How do you export to a file in a Stored Proc?

Question

I tried BCP, but doesn't work. Sql keeps giving me some Help text..

Anyway, it doesn't matter, as I don't want to paste the contents of the stored proc AGAIN into a string so this can export.

My Question: I have an existing stored proc. I would like it to automatically kick its result out into a text file.

Any clues?

Answer 1

this is very easy

CREATE PROCEDURE sp_AppendToFile(@FileName varchar(255), @Text1 varchar(255)) AS DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject' :Open a file execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1 IF @OLEResult <> 0 PRINT 'OpenTextFile'

--Write Text1 execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1 IF @OLEResult <> 0 PRINT 'WriteLine'

EXECUTE @OLEResult = sp_OADestroy @FileID EXECUTE @OLEResult = sp_OADestroy @FS

from: http://www.motobit.com/tips/detpg%5FSQLWrFile/

Answer 2

Assuming you have stored procedure MyProc in database MyDB on server MyServer, and you want results in tab delimited output file MyResults:

bcp "exec MyDB.dbo.MyProc" queryout MyResults -T -c -t\t -S MyServer

should work. bcp offers a lot of other options for output as well.