Exporting Data Programatically with bcp and xp_cmdshell

By Garth Wells on 1 July 2001 | Tags: bcp/Bulk Insert

This article demonstrates how to programmatically control bcp to generate text files. It uses dynamic SQL and xp_cmdshell to execute a call to bcp from within Query Analyzer.

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.