Outputing results to a file (equiv Oracle 'spool')

[Welshbird]

Morning Chaps,

Having come back to MS-SQL after spedning time with Oracle I'm finding things that I don't know how to do with SQL2000 and SQL2005.

I produce weekly reports, and my standard method now is to send the results of the query to a tilde (~) delimited file.

However, sometimes the reports should show detail and a summary, and in Oracle I would have spooled these results to one file.

Is there an equivelent way to do this in MS-SQL?

Thanks,

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Mich]

The only way I'm aware of scripting this is by enabling and executing xp_cmdshell with bcp. Most consider this a serious security risk.

If you're running a query from within SSMS you can select the 'Results to File' option in the toolbar.

Sounds like you might want to look into SSIS/DTS for this. It might simplify your life.

-If it ain't broke, break it and make it better.

 

[Welshbird]

I use the 'results to file' now. I guess what I really wanted to do was to run this, then run a totally different query and have it appended to the bottom of the file (which spool in Oracle would have done happily).

I'll keep trying things, but it seems like maybe it isn't possible.

(I guess I could write everything to a temp table and then to select * into file from that, but this sin't really what I want to do).

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Mich]

If you run two queries within the same window at the same time, then send those results to file, you'll have result set 1 followed by result set 2.

I'm not aware of a way to append an existing file unless you use a SSIS package.

-If it ain't broke, break it and make it better.