Take Advantage of Osql and Bcp Without Having to Remember All Their Command-Line Options

Querying data is one thing; getting that data into a text file programmatically is another. SQL Server ships with two excellent command-line utilities—bcp and osql—that you can use to query a database and save the results to a file. However, if you aren’t familiar with these two utilities, you might find their many command-line options daunting.

I wrote a stored procedure, sp_Query2File, that can help you quickly get your data into the file of your choice, without having to remember lots of command-line options. This stored procedure consolidates the basic functions of osql and bcp. Switching between the osql and bcp utilities’ output is simply a matter of changing an input parameter’s value.

The sp_Query2File stored procedure has three mandatory input parameters (@Query, @FileName, and @Method) and two optional input parameters (@ Headers and @ShowResults). The @Query parameter specifies the SQL query to be executed (e.g., 'SELECT * FROM master.dbo.sysobjects'). The @FileName parameter specifies the pathname of the output file (e.g., 'D:myfilesresult.txt').

The @Method parameter specifies the utility you want to invoke to run the query and create the file. The two choices are 'OSQL' (which is the default) and 'BCP'. If you want a nicely formatted, column-based result set with headers, specify 'OSQL'. If you want a space-saving tabdelimited file without headers, specify 'BCP'. Note that to use the bcp utility, the SQL query must be fully qualified (i.e., master.dbo.sysobjects).

The @Headers parameter applies only if you specify 'OSQL' as the @Method parameter. You use the @Headers parameter to indicate how many data lines to print before reprinting the column headings. If you don’t specify a value, the default value of 999999 is used. Passing in a number less than 1 tells osql.exe to not print any column headings.

You include the @ShowResults parameter if you want to display the contents of the output file after the query results are written to it. This option is useful when you’re using sp_Query2File on a remote server and would like to view the contents of the output file without having to actually open a connection and browse to the file.

Figure 1 includes sample calls to sp_Query2File. The figure also includes the equivalent osql and bcp commands. As Figure 1 shows, sp_Query2File can greatly reduce code complexity because you no longer have to worry about formulating different commands for each type of output. In addition, sp_Query2File can balance the need for readable yet analyzable result sets. For example, one of my clients uses it to create scheduled jobs that run report queries. She saves the results in both a column-based format for easy readability and in tab-delimited format for further analysis through Microsoft Excel.

The sp_Query2File stored procedure works on SQL Server 2005 and SQL Server 2000. (Although sqlcmd is the replacement for osql and isql in SQL Server 2005, SQL Server 2005 ships with both osql and sqlcmd. Osql doesn’t support some SQL Server 2005’s enhancements, such as CLR user-defined data types, but it works fine for this usage.) You can download sp_Query2File.

—Bill McEvoy, Master Chef/DBA, Cooking with SQL