Executive Summary:
The T-SQL user-defined function (UDF) named dbo.occurrences performs a simple arithmetic calculation to determine how many times a substring appears in a source string. Although this UDF is written for Microsoft SQL Server 2005, SP1, you can make it work on Microsoft SQL Server 2000 by changing the data type of two parameters.
|
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:\myfiles\result.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
End of Article
