osql query to text file - thanks to Brain Knight

Piping a Query to a Text File
By Brian Knight, 2001/05/21

The case where one has to send the results of a query to a text file comes up quite often. 

General this case 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.

\mssql7\binn\osql -Usa -Ppassword -Ic:\inputquery.txt -Oc:\outputdir\output.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
 -Usa
 -Pmisty
 -dNorthwind
 -Q"select * from products"
 -oc:\output.txt'


The -Q parameter in the above syntax runs the query then exits ISQL. 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
 -dNorthwind
 -Q"select * from products"
 -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.


By Brian Knight, 2001/05/21


usage: osql              [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w columnwidth]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-L list servers]      [-c cmdend]            [-D ODBC DSN name]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-n remove numbering]  [-m errorlevel]
  [-r msgs to stderr]    [-V severitylevel]
  [-i inputfile]         [-o outputfile]
  [-p print statistics]  [-b On error batch abort]
  [-O use Old ISQL behavior disables the following]
       batch processing
      Auto console width scaling
      Wide messages
      default errorlevel is -1 vs 1
  [-? show syntax summary]