How do I output records from a SQL Server ISQL script to a text file?

[Risk]

I am not very familiar with SQL Server SQL but what I am trying to do is the equivalent of the Spool command in Oracle SQL. I wish to write the results of a SQL query in SQL Server 7 to a text file. Can anyone help me this?

Thanks

 

[outofservice]

One way of doing this is to execute the query statement.
After execution has completed, go to 'File' and click
'save as' option. This will promt you to save the query results to a report file. Hope this helps.

Lauryn Bradley
SQL Server DBA

 

[Risk]

Thanks Lauryn

But is there anyway of doing this automatically?

 

[SteveMac32]

-- EXPORT Query results to a .txt file

-- define and run your SQL query and make sure the file you want to export to exists.
DECLARE @matter_rsn int,
@matter_Desc varchar(100),
@matter_search varchar(50),
@cmd varchar(300)

SET @matter_rsn = 101669

SELECT @matter_Desc = (SELECT matter_Desc from matter
WHERE matter_rsn = @matter_rsn)
SELECT @matter_search = (SELECT matter_search from matter
WHERE matter_rsn = @matter_rsn)


-- Appends to existing record (>>).
Set @cmd = 'Echo ' + @matter_Desc + ', ' + @matter_search + '>>C:\Test\testing.txt'
Exec master..xp_cmdshell @cmd


/* Overwrites existing record (>)
Set @cmd = 'Echo ' + @matter_Desc + ', ' + @matter_search + '>C:\Test\testing.txt'
Exec master..xp_cmdshell @cmd
*/


Hope i have made this clear ?

 

[moonshadow]

Steve,
you have to be joking, surely ? Just use the DTS export wizard to create a DTS package that exports the data straight to a text file. You can then either call this package programmatically, or schedule it with SQLAgent.

 

[tlbroadbent]

If you want to use ISQL (or OSQL in SQL 7 and higher) include the -o option and a file name. The result of the query will be directed to the output file.

osql -E -S ServerName -d db_name
-Q 'Select * From MyTable'
-o f:\data\output\mytable.txt'

See SQL BOL for more info on the ISQL/OSQL options. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.