SQL Server BCP and Phantom CRLFs

Microsoft SQL Server comes with bcp.exe, a command-line tool that uses the Bulk Copy Program API. It allows to export data from tables and queries, import data from files into tables and it generates format files.

Bcp comes with a lot of command line parameters. Find them all here. The tool is very convenient if you want a swift and simple way to import or – in my case – export data from a database and dump the information in a XML file. In my case, an XML file is desired, containing some filtered data, renewed every few hours. We’ll take the following approach:

  1. Prepare the T-SQL query and define the output (XML schema)
  2. Create a batch file calling bcp.exe with the appropriate parameters
  3. Schedule the batch file on a dedicated server

T-SQL Query

Using SQL Server Management Studio or other tool, key is to prepare your SQL query in advance. Make sure the output of the query reflects exactly the data you expect. For simplicty reasons, we’ll continue with a simple SELECT Name, Address, City FROM Customers in this blogpost. We want the XML result to be structured as follows:

<Database>
 <Customer>
  <Name>The President</Name>
  <Address>1600 Pennsylvania Avenue NW</Address>
  <City>Washington, DC 20500</City>
 </Customer>
 ...
</Database>

As the final output should be an XML file, we need to tweak the T-SQL query with XML statements and append FOR XML PATH('Customer'), ROOT('Database'), ELEMENTS to format the data accordingly.

Bcp

Now we’ll construct the command line and save it in a batch file bcp.bat. In order to create the XML file, the parameters queryout, -x (XML output) and -w (wide character also known as ntext) are important.

bcp.exe "SELECT Name, Address, City FROM Customers FOR XML PATH('Customer'), ROOT('Database'), ELEMENTS" queryout C:\BCP.XML -x -w -S SQLSERVER\INSTANCE

Give it a try, execute the batch file. Now, if the query ran successfully, open the XML file with Internet Explorer and you’ll get… an error!

Open the XML file with a text editor such as Notepad++ and you’ll notice that bcp.exe inserts CRLF characters at specific intervals. Before you start implementing a state-of-the-art solution to find and replace those, the trick is to simply add a -r parameter and you’re set, no more phanthom CRLFs. In other words, the following final command line does the trick:

bcp.exe "SELECT Name, Address, City FROM Customers FOR XML PATH('Customer'), ROOT('Database'), ELEMENTS" queryout C:\BCP.XML -x -w -r -S SQLSERVER\INSTANCE

Scheduler

In order to refresh the data on a regulare basis, simply use the Windows Task Scheduler, point to the batch file and either run the task as a domain user with priviliges to read data from the table (don’t forget the -T switch for trusted connections) or extend the command line to use a SQL Server account that has the same priviliges:

bcp.exe "SELECT Name, Address, City FROM Customers FOR XML PATH('Customer'), ROOT('Database'), ELEMENTS" queryout C:\BCP.XML -x -w -r -S SQLSERVER\INSTANCE -U SQL_USER -P SQL_PASSWORD

Advertisements

One Response to SQL Server BCP and Phantom CRLFs

  1. Valentina says:

    Than to import data from file to Table?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: