September 18, 2003 at 5:28 am
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?
September 18, 2003 at 7:07 am
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.
September 18, 2003 at 10:27 am
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
September 18, 2003 at 10:36 am
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.
September 19, 2003 at 1:58 am
Thank you David for both your expertise and the speed of your responses.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply