In a previous article
I showed you how to use BULK INSERT to import data stored in a text file into a
table in SQL Server. BULK INSERT makes an "under-the-hood" call to bcp (Bulk
Copy Program) to import the data. bcp is a command-line utility that can be used
to import/export data in either character or native SQL Server format. Most
developers use DTS (Data Transformation Services) to import/export data, but
there are times when bcp is a more convenient approach.
In this article I want to show you how to programmatically use bcp and xp_cmdshell
to create a text file based on data stored in SQL Server. The approach shown here
is used on a project I am currently working on to create text files during a nightly
batch process. The batch process uses a stored procedure to accumulate daily
transactions for EDI processing. More specifically, it retrieves and formats the
daily transactions and then exports them to a text file which is ftp'd to the
target vendors. The procedure creates several text files, which is why the approach
shown in this article is more efficient than the one discussed in
OSQL: Storing result of a
Stored Procedure in a file
bcp in Action
Let's start off with a simple bcp call so you can see how it works. If you want
to see the full syntax for bcp take a look the topic: bcp Utility, in Books
Online (BOL). The following command exports the data stored in the authors table
in the pubs database to authors.txt.
bcp "SELECT * FROM pubs..authors" queryout authors.txt -U garth -P pw -c
To execute the statement, go to the Command Prompt and type it in as shown replacing
"garth" and "pw" with a valid login/password. All we are doing is passing arguments
to the bcp executable. The first argument is the SELECT statement that specifies
the source data. The database name is referenced in the SELECT because by default
bcp will try to run the statement in the default database associated with the
"garth" login. If the default database associated with the login does not contain
the target object(s), an error message is generated.
The second argument (queryout) tells bcp the direction of data flow. In
this case it indicates the data is flowing out of SQL Server using a query to
specify the source data.
The third argument (authors.txt) specifies the file that will store the
data. The file will be created in the directory in which the command is executed
if no other is specified.
The fourth and fifth arguments (-U and -P) specify the login and
password. The arguments are case-sensitive, so make sure they are capitalized.
You should know that the -T argument can be used in place of -U and -P when
using a trusted connection.
The last argument (-c) specifies the format of the data coming out of SQL
Server. When -c is used the data is stored in character format using the tab
character as the column separator and the newline character as the row
terminator. After you execute the statement open authors.txt in Notepad and take
a look at the format.
Using Dynamic SQL to Create a Valid bcp Call
The nightly batch process I referenced earlier creates date-specific files, so
it is important to have the date the file is created in its name. Dynamic SQL is
used to accomplish this. A file's name is created using the technique shown here.
DECLARE @FileName varchar(50)
SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
PRINT @FileName
--Results--
c:\authors_06-30-01.txt
The GETDATE() function is used to determines today's date, CONVERT changes the
date into a more friendly format (e.g., 06/30/01) and REPLACE replaces the "/"
characters with a "-".
Once the file name is created, it is concatenated with the bcp command to create
a valid call. The following shows how this is done.
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand = 'bcp "SELECT * FROM pubs..authors ORDER BY au_lname" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U garth -P pw -c'
PRINT @bcpCommand
-- Results --
bcp "SELECT * FROM pubs..authors ORDER BY au_lname"
queryout "c:\authors_06-30-01.txt" -U garth -P pw -c
Note: Results wrapped for readability.
Executing the Call with xp_cmdshell
Now that a valid bcp call has been constructed, we must execute it to create the
data file. You have already seen how to do this interactively via the Command
Prompt, but what we need is way to execute it within a stored procedure. This is
accomplished with the extended stored procedure xp_cmdshell. Extended stored
procedures are programs written in langauges like C++ that are then attached
to an instance of SQL Server. Once attached, they can be referenced just like a
regular stored procedure.
xp_cmdshell allows you to execute an operating system command via a SQL
Statement. Execute the following in Query Analyzer to see how xp_cmdshell works.
EXEC master..xp_cmdshell 'dir'
-- Results (partial) --
output
------------------------------
Volume in drive C has no label.
Volume Serial Number is 2876-AD19
NULL
Directory of C:\WINNT\system32
NULL
06/27/01 08:38p <DIR> .
06/27/01 08:38p <DIR> ..
...
The operating system command "dir" is executed and the results are shown in the
Results pane of Query Analyzer. Note that I referenced the master database in
the EXEC statement. To execute an extended stored procedure that starts with
"xp_", either the master database must be active or referenced by EXEC.
There are a few extended stored procedures that start with the "sp_" prefix.
Those procedures, like all system stored procedures, can be executed without
specifying the database because of the default behavior of SQL Server. When you
execute a stored procedure that starts with "sp_", SQL Server looks for the
procedure in the master database first. If the procedure is not found in master,
it looks in the active database. If you have a stored procedure that you want to
access from all your databases, create it in master and use a name that includes
the "sp_" prefix.
To create the data file we simply execute the command stored in @bcpCommand with
xp_cmdshell. The following shows all the statements needed to create the file.
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand = 'bcp "SELECT * FROM pubs..authors ORDER BY au_lname" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U garth -P pw -c'
EXEC master..xp_cmdshell @bcpCommand
-- Results --
output
---------------------
NULL
Starting copy...
NULL
23 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 61 Avg 2 (377.05 rows per sec.)
NULL
In the nightly batch procedure this code is referenced multiple times to create
the various text files. For each reference, the SELECT is modified per the target
tables and a new file name is generated.
