Friday, February 15, 2008

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
-Q"select * from products"

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
-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]

No comments: