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: 30 Date: 04/Jun/2010 Rating: Points: 2
Did you check if that procedure runs with out errors in query analyser? Regards, Satish My Blog

#513318 Author: kumar Member Level: Bronze Member Rank: 19133 Date: 04/Jun/2010 Rating: Points: 2
The Proceudure runs without any problems.

#513329 Author: Satish Kumar J Member Level: Gold Member Rank: 30 Date: 04/Jun/2010 Rating: Points: 2
try to keep 'C:\Backup\log1.txt' statement is stored procedure and check, not sure why it is happening. HTH Regards, Satish My Blog

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:

Related Messages