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

Parsing PowerShell Output from VBScript

You might have a case of a mixed VBScript/PowerShell implementation – for instance a customized MDT script calling a PowerShell cmdlet. No problem, but the tricky part lies in parsing the output i.e. the text PowerShell sends to the console; not the exit code.

Bottom line is: if you don’t close the standard input before reading the standard output, your VBScript will stall and wait forever.

Here’s a VBScript code snippet which calls PowerShell to query all running services and then echoes the result:

Option Explicit

Dim ps: ps = "powershell.exe -command Get-Service | Where-Object {$_.Status -eq 'Running'} | Foreach-Object {$_.Name}"
Dim sh: Set sh = CreateObject("WScript.Shell")
Dim exec: Set exec = sh.Exec(ps)

'-- Close standard input before reading standard output!
exec.StdIn.Close()

WScript.Echo exec.StdOut.ReadAll()

So don’t forget to include StdIn.Close() or the script will simply not terminate.

Checking AD Group Membership from a Batch File

Batch files are not dead. They are not extinct by VBScript or PowerShell. Not yet. Perhaps never. Get over it.

Here’s how to check if a user is member of a given Active Directory group and act upon the result. The principle is straightforward:

1. Parse the output of net user to retrieve the user’s AD groups
2. Use for and find to count the occurrence of a given group
3. A counter > 0 implies membership

Example:

@echo off
cls
set i=0
set group=SomeAdGroup
set user=%username%
echo Checking if %user% is member of %group%...
for /f %%f in ('"net user %user% /domain | findstr /i %group%"') do set /a i=%i%+1
if %i% gtr 0 (goto :member)
:nomember
echo %user% is not member of %group%
goto :end
:member
echo %user% is member of %group%
:end

Caveats! The above example is not bulletproof:

1. False positives occur when there are groups which contain part of the name of another group – e.g. checking membership of an AD group “ITDep” will return true even if the user is not a member of that specific group but (s)he is member of a group called “ITDepartement”.
2. The net user command has a problem with lengthy group names and truncates values in its output.

%d bloggers like this: