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
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

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

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]


#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?


#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.



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.

Next : How can Truncate Temporary Table
Previous : Stored Procedures CRUD operations
Return to Discussion Forum
Post New Message
Category: SQL Server

Related Messages

Subscribe to Subscribers
Awards & Gifts
Talk to Webmaster Tony John

Online Members

Ayush Dhar
Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India