Forums » .NET » SQL Server »

Queryout with Column Names


Posted Date: 04 Jun 2010      Posted By:: kumar     Member Level: Bronze    Member Rank: 19133     Points: 1   Responses: 3



CREATE PROCEDURE dbo.proc_generate_txt
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
) AS

Declare @Headers varchar(1000),@sql varchar(8000), @data_file varchar(100),
@x varchar(300)

--Generate column names as a recordset

Select @Headers = IsNull(@Headers + CHAR(9) ,'') + Column_Name
From INFORMATION_SCHEMA.COLUMNS
Where Table_Name = @table_name ORDER BY ORDINAL_POSITION ASC

print @Headers

set @sql = 'bcp "select ''' + @Headers + '''" queryout "'+@file_name+'" -c -t'
print @sql
exec master..xp_cmdshell @sql
set @sql = 'exec master..xp_cmdshell ' + @sql
print @sql
--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.csv'

print @data_file
set @sql = 'bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -t"\t"'
print @sql
exec master..xp_cmdshell @sql

--Copy dummy file to passed txt file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
print @sql

exec(@sql)
--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
print @sql
exec(@sql)
GO
----

I have run the below query :
----------------------------
exec proc_generate_txt 'AdventureWorksDW', 'DatabaseLog', 'C:\Backup\log1.txt'

All What I get is :
-------------------
User name not provided, either use -U to provide the user name or use -T for Trusted Connection
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL




Responses

#513303    Author: Satish Kumar J      Member Level: Gold      Member Rank: 28     Date: 04/Jun/2010   Rating: 2 out of 52 out of 5     Points: 2

Did you check if that procedure runs with out errors in query analyser?

Regards,
Satish


 
#513318    Author: kumar      Member Level: Bronze      Member Rank: 19133     Date: 04/Jun/2010   Rating: 2 out of 52 out of 5     Points: 2

The Proceudure runs without any problems.

 
#513329    Author: Satish Kumar J      Member Level: Gold      Member Rank: 28     Date: 04/Jun/2010   Rating: 2 out of 52 out of 5     Points: 2

try to keep 'C:\Backup\log1.txt' statement is stored procedure and check, not sure why it is happening.

HTH

Regards,
Satish





 
Post Reply

 This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.



Return to Discussion Forum
Start new thread

Subscribe to Subscribers
Active Members
TodayLast 7 Daysmore...

Awards & Gifts
Talk to Webmaster Tony John

Online Members

Priya jain
More...
Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India