Solved

How do I spool the output of a query to a file in SQL Server

Posted on 2011-02-24
2
3,146 Views
Last Modified: 2013-11-05
Is there an equivalent to Oracle’s “spool” function on SQL Server? I need the output of a query spool to a file. The query will be run as a cron job as a schedule task, any suggestions?    
0
Comment
Question by:dbawant2b
2 Comments
 
LVL 4

Accepted Solution

by:
MarioAlcaide earned 500 total points
Comment Utility
We have many options.  Here are just a few that I think can help solve your problem...

SQL Server 2000:
OSQL/ISQL - SQL Server 2000 command line tools.
SQL DMO - Management API
T-SQL

SQL Server 2005:
SQLCMD - Command line tool
SQL SMO - Management API (really easy to script in .NET)
Generate Script Wizard (You choose the objects in your database that you want to script and the settings.  It generates the scripts for you.) (In SQL Server 2005 SP2, you can generate DROP statements for objects and script each object to its own file.)


If you want to save the results from this T-SQL script to a file, you can:

1) Have Management Studio save it to a file:
Query | Results To | Results to File or Shortcut Key (CTRL + SHIFT + F)

2) Use SQLCMD to output the results to a file:
a) Save the T-SQL query above to a .sql file
b) Use the -e option to echo your input file into your output file
SQLCMD -i Input.sql -o C:\Results.txt -e

0
 

Author Closing Comment

by:dbawant2b
Comment Utility
Thanks for your help.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now