Save output messages to text file?

_{Friday, July 11, 2008 11:05 AM}

Hi all I was wondering whether I was able to save the messages outputted from an SQL command to a text file? Kind of like the SPOOL ON function in Oracle? But the SPOOL I believe stores the code written but I need the output message? Anybody know how to do this?

For instance if I typed UPDATE Table1 SET Column1 = 100 then in my text file it would have 7 rows affected.

Thanks for comments + suggestions, Onam.

_{Friday, July 11, 2008 6:13 PM}

Onam,

There is no such functionality as spool of oracle.

However you can redirect the results of the query along with the messages to a file instead if you want.

Hit ctrl-F before running the query and this redirect the results to a file.

SQL Server directs the results to 1) Grid (ctrl-D), 2) Text (ctrl-T) and 3) File (ctrl-F)

_{Monday, July 14, 2008 8:16 AM}

Is it possible to direct the results to file via an SQL command? There is an option on the toolbar saying Direct to File but is it possible to do this via code specifying a file path?

*EDIT*

For some reason I am missing several system tables in the Master database? I need the dbo.sysxlogins table... anybody know how I could restore this table?

_{Monday, July 14, 2008 11:41 AM}

You can direct output to file programmatically several ways:

1. bcp

2. osql

3. SQLCMD

4. SSIS

This is for bcp:

Code Snippet

DECLARE @FileName sysname,

@SQLCommand varchar(1024)

SET @FileName = REPLACE('f:\data\export\Department'+

CONVERT(char(10),GETDATE(),111)+'.txt','/','-')

SET @SQLCommand = 'bcp "SELECT * FROM AdventureWorks.HumanResources.Department ORDER BY [name]" queryout "'

SET @SQLCommand = @SQLCommand + @FileName + '" -S "YOURSERVER" -U "YourLogin" -P "YourPassW" -c'

PRINT @SQLCommand

EXEC master..xp_cmdshell @SQLCommand

You can build an SSIS package with the SSIS import/export wizard. You can execute it at command line with DTEXEC.

Let us know if works.

_{Monday, July 14, 2008 2:02 PM}

If I simply used this and not included the EXEC master..xp_cmdshell @SQLCommand can I still output to a text file? Is it possible to output to text file without using cmdshell?

_{Monday, July 14, 2008 3:34 PM | 1 vote}

There is another way  :-)

create a job, add a step, put your code into the step, click on edit job step, in the advanced tab there is an output file, navigate to where you want this file to be

save the job and execute it or schedule for later execution, you can append to this file or overwrite it

Denis The SQL Menace

http://www.lessthandot.com/

http://sqlservercode.blogspot.com

http://sqlblog.com/blogs/denis_gobo/default.aspx