write table content into a text file

[luigiida]

Hi,
I have a stored procedure that does some operations on a table and I would like to save the content of the result table into a file (using some function inside the stored procedure or from the "command prompt"... not with enterprise manager)
How can I do it?
Is it possible to keep also the fieds, for example, tab separated?
Thanks a lot,
Luigi

 

[hmckillop]

You can use BCP from the command line, which is probably the quickest for output
i.e.
bcp "SELECT * FROM mydb..mytable" queryout c:\BCPOut.txt -P

will output your table into a file called BCPOut.txt on the c drive.

 

[luigiida]

Thanks,
I tried to useit form the command line but it gives me alot of strange errors regarding the stored procedure.
Is there another way to use it from inside the procedure?
( I tried doing exec xp-cmdshell 'bcp.... but it doesn't find xp-cmdshell)

Thanks,
Luigi

 

[mukund]

Create the following Stored Procedure in the master db
/* Procedure starts */
Create PROCEDURE sp_QueryToFile
( @db sysname,
@query VARCHAR(1000),
@file VARCHAR(255)
)

AS

SET NOCOUNT ON
SET @Query = 'SET NOCOUNT ON ' + @Query

EXECUTE ('master..xp_cmdshell ''osql -w8000 -r -s" ", -h-1 -o' + @file + ' -d'
+ @db + ' -Q"' + @query + '" -E''')
/* Procedure Ends */

Usage of this Stored Proc is as follows
EXEC sp_querytofile @dbName, @query, @file

In any other stored proc, assign appropriate values to the three variables and while passing the filename make sure that you assign value along with the filepath for e.g.
'c:\test.txt'

After execution the text file will be created on the SQL Server and the Results of the Query that is being passed will be stored in the text file.


-Mukund.