|
The case where you have to send the results of a query to a text file comes up quite often in my job. Generally, this case can take the form of a maintenance script that you run through Agent nightly and would like to see the results in the morning. In the above example, you can create a CMDEXEC job to run the below command. This will output the results of the query inside c:inputquery.txt to a separate file.
mssql7binnosql -Uloginname -Ppassword -ic:inputquery.txt -oc:outputdiroutput.txt
You can also use -E switch for trusted connection if you don't want to hard code a password into your job. From an Query Analyzer window or inside the stored procedure you can run the above statement with an extended stored procedure called XP_CMDSHELL:
master..xp_cmdshell 'osql -Uloginname -Ppassword -Q"select * from products" -dNorthwind -oc:output.txt'
The -Q parameter in the above syntax runs the query then exits osql. You may also want to add the additional NO_OUTPUT parameter at the end of query as shown below to supress any output to the client:
master..xp_cmdshell 'osql -Usa -Pmisty -q"select * from products" -dNorthwind -oc:output.txt', no_output
There are other methods of piping a query to a text file by using OLE Automation but they are much more complex than this method.
Brian Knight
SQLServerCentral.com Co-Founder | 
trackback []
Articulate []
