Exporting to a text file from a stored procedure

Question

Exporting to a text file from a stored procedure

barry555

SSC Eights!

Points: 944
More actions
September 18, 2003 at 5:28 am

#82257

I want to output the results of a query to a text file. I know I can do this using DTS, but is there a way to do it from a stored procedure?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65142 More actions · Answer 1

Depending on your definition of 'query' you could use bcp

exec master..xp_cmdshell 'bcp "select ... from datbase..table" 

queryout outputfilename 

-c -Sserver -Uuser -Ppassword'

or

exec master..xp_cmdshell 'bcp "exec datebase..procname" 

queryout outputfilename 

-c -Sserver -Uuser -Ppassword'

to use the output of another procedure

Edited by - davidburrows on 09/18/2003 07:07:28 AM

Far away is close at hand in the images of elsewhere.
Anon.

barry555 SSC Eights! Points: 944 More actions · Answer 2

Thanks David, that was just the job!

However, after I get the sp working, I tried to be clever (always dangerous) and wanted to put my rather long query into a variable to make the sp easier to read/maintain as follows:

DECLARE @cmd varchar(1000)

, @sql varchar(1000)

SET @sql = 'SELECT Field1

, Field2

FROM Database.dbo.Table'

SET @cmd = 'bcp "' + @sql + '" queryout test.csv -c -t, -T'

EXEC master..xp_cmdshell @cmd, no_output

I wasted ages trying to figure out why this was failing. It turned out to be my splitting the sql statement into more than 1 line! The following code worked:

SET @sql = 'SELECT Field1, Field2 FROM Database.dbo.Table'

Am I missing some wider issue with concatenating strings? Or is it to do with the xp_cmdshell sp?

Thanks,

Barry

David Burrows SSC Guru Points: 65142 More actions · Answer 3

Barry,

It's to do with concatenation. When you put text between quotes on separate lines sql will include CR/LF in the text. Sometimes it is useful sometimes not.

SET @sql = 'SELECT Field1' +

', Field2 ' +

'FROM Database.dbo.Table'

and

SET @sql = 'SELECT Field1, Field2 FROM Database.dbo.Table'

will give you the same text in the variable.

Far away is close at hand in the images of elsewhere.
Anon.

barry555 SSC Eights! Points: 944 More actions · Answer 4

Thank you David for both your expertise and the speed of your responses.