How to export the output of a stored procedure or query to a text file using SQLCMD command in SQL Server 2005 and later versions

How to pump the output of a stored procedure or query to a text file using SQLCMD command in SQL Server 2005 and later versions

(a) Running SQLCMD command in Command prompt and create a file from the output

C:\> sqlcmd -Sservername -dmaster -Usa -Psa -q"se
lect *from sys.objects" -oC:\testoutput.txt

(b) Push the output of a query to a file using XP_CMDShell and SQLCMD command

Now what happens If we want to do this from Query Analyser. Simple , run the same command using XP_CMDShell.

(a) Open a Query Analyser in Management Studio
(b) Run the following query
Exec XP_CMDShell 'sqlcmd -Sservername -dmaster -Usa -Psa -q"select *from sys.objects" -oC:\testoutput.txt'

(c) Third and simple method
We all know the two methods mentioned above. But there is another method which is very simple and flexible. SQL Server Management Studio Query Editor has support for SQLCMD scripts authoring and execution. We can switch ON or Off this feature.

(a) Open Management Studio
(b) Tools Menu -->> Select Options  Query Execution  General  check the check box at the bottom of the page on the right side of the dialog “By default, open new queries in SQLCMD mode.”
(c) Press OK and save the settings
(d) Now , open a query analyzer and type the following
:out c:\queryresults.txt
:error c:\queryerror.txt
GO
Select *from sys.objects
go
Select *from sys.objectsaaa



Note : In the above mentioned command the output of the first query will be pushed to C:\queryresults.txt and the error due to the second select statement will be pushed to c:\queryerror.txt

Disclaimer :

All the SQLCMD commands are not supported from Query Analyser. Following are the SQLCMD commands not supported in Query Analyser

(a):serverlist Lists local and SQL Servers on the network.

(b) :reset Discards the statement cache.

(c) :perftrace Redirects timing output to a file, stderr, or stdout. This is not supported because Query Editor doesn't have this concept.

(d) :listvar Lists the set SQLCMD scripting variables.

(e) :ed Edits the current or last executed statement cache.

(f) :help Shows the list of supported commands.

if you use any command in Query Analyser, Query Editor issues a non-fatal warning into the Messages tab during execution and continues on.


Summary :

All the three methods are equally useful. Many folks may not have observed the third method that is why i thought to blog this..