Based on Michael Otey’s “Overlooked Command-Line Utilities” at Sqlmag.com and “Brian Knight” at SWYNK.COM “Piping Queries to a Text File”
The article discusses a set of MS SQL Server command-line utilities and their applications.
Seven utilities
Michael writes that MS SQL Server has powerful graphical management tools, but also has a lot of lesser-known command line utilities that you can use in Windows NT or VBScript shell scripts to manage your SQL server. Here are its tips for the seven most commonly used MS SQL Server utilities that come with MS SQL Server 2000 and 7.0:
- The console commands Net Start and Net Stop MSSQLServer allow you to start and stop MS SQL Server services from the command line. You can add these commands to your own scripts or batch files to automatically restart the database server.
- Bulk Copy Program (BCP) – designed to download/import data to MS SQL Server or to export it. Although the author does not use bcp but rather prefers Data Transformation Services (DTS) from MS SQL Server 7.0, bcp (in his opinion) remains one of the most useful data transfer options. The syntax of this utility is described in detail in the BOL.
- The ODBCCMPT command sets the level of ODBC compatibility for the user application. This utility is especially useful when you are using the 3.x ODBC driver version, but you need to ensure that you connect to MS SQL Server 2000 or 7.0 from the user application via the ODBC driver version 2.x. To support compatibility with MS SQL Server 6.x, you can use odbccmpt with the /v:6 switch. The command syntax is odbccmpt file_name /v:version_number
- The ITWIZ utility allows you to run Index Tuning Wizard MS SQL Server from the command line.
- ISQL and OSQL utilities allow you to execute separate SQL instructions, as well as SQL scripts saved in files – scripts. However, isql uses a truncated DB-Library to connect to MS SQL Server. You should always remember that osql works through ODBC. Below is the osql syntax that specifies the query execution and the result is output to the file:
osql /S Server_name /U user /P password /Q query /o output_file
- The DTSWIZ utility launches the DTS Import/Export Wizard from an NT script or VBScript file. The available string parameters allow you not to display the wizard dialogues by specifying the necessary information from the command line.
- The DTSRUN command line utility launches DTS packet maintenance. Using dtsrun in MS SQL Server scheduled tasks gives you an excellent opportunity to process DTS packets regularly. You can store DTS packages in an msdb database, structured COM file or Microsoft repository. To process DTS packages saved in msdb, use the following syntax:
dtsrun /S Server_name /U user /P password /N package_name
Saving query results in a text file
Brian writes that it is often necessary to save the query execution results in a text file. This is especially useful when the execution time of the request is very long or the available resources do not allow you to run the request during working hours, and you decide to start the execution of the request at night and see the results in the morning. You can create a task containing CMDEXEC, which will execute the necessary commands and output the results of the request c:\inputquery.txt in a separate file.
\mssql7\binn\osql -Usa -Password -ic:\inputquery.txt -oc:\outputdir\output.txt
If you don’t want to write the password to your task openly, you can use the “E” key to organize a trusted connection.
You can also use the extended stored XP_CMDSHELL procedure:
master..xp_cmdshell ‘osql -Usa -Pmisty -Q “select * from products” -dNorthwind -oc:\output.txt’ ‘
The “Q” parameter specifies the query string.
At the end of the request, you can add an additional parameter NO_OUTPUT to block 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 conveying query execution results to a text file (for example, using OLE Automation), but they are more time-consuming than this method.
