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

SQL Server 2008 R2 – Upgrading to Service Pack 1 Silently

KB2528583 represents SQL Server 2008 R2 Service Pack 1 which went live early November 2011. It consolidates Cumulative Update 1 to 6 and fixes circa 100 issues in total. Today the first post SP1 is CU7.

There actually is a possibility to slipstream SP1 into the original setup, as described here, but in my personal experience, the process results in versioning woes after deployment. In other words, I would not recommend it; instead I would take the classic road and simply apply SP1 immediately afterwards.

Note that before applying SP1, SQL Server Management Studio’s Help > About information reveals version 10.50.1600.1. We’ll check if the version reflects SP1 properly in the end (which should be 10.50.2500.0 according to the KB article).

After downloading the installer (x86 or x64 version depending on the target platform), extract SQLServer2008R2SP1-KB2528583-x86-ENU.exe into a folder:

Setup.exe is what we need. Now, what setup /? does not reveal, is a hidden switch to bypass the otherwise manual action of accepting the license agreement. What we need is the IAcceptSQLServerLicenseTerms parameter.

In other words, the following command line will upgrade SQL Server 2008 R2 to Service Pack 1 silently:

setup.exe /quiet /IAcceptSQLServerLicenseTerms

You can parse the exit code of the installer (should be 0 for success or 3010 for a reboot) and verify successful installation or troubleshoot errors by analyzing the detailed logfiles which – as usual – reside in C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\yyyymmdd_hhmmss. If the summary log file reads the following, you are all clear:

Overall summary:
Final result: Passed
Exit code (Decimal): 0
Exit message: Passed
Start time: 2012-06-18 09:23:19
End time: 2012-06-18 09:29:17
Requested action: Patch

Double check the version of SQL Server Management Studio, it should read 10.50.2500.0 after applying SP1:

3 OS X Tips for Windows Users

If you’re rather new to OS X, coming from or still living in a world dominated by Windows just like mine, you’ve probably run into the following trivial questions:

  1. How to cut and paste (move) files in Finder?
  2. How to lock the screen?
  3. How to restore windows using Command+Tab?

Here are the short answers.

Cut and Paste Files in Finder

In Windows, the keyboard shortcut Control+X (cut) followed by Control+V (paste) behaves exactly the same in Explorer as it does in other applications such as text editors or graphics programs. It moves files and even entire directories, recursively.

Not on your Mac. Cut and paste doesn’t work in Finder. I’ve been struggling with this for some time, but the solution is quite simple: don’t use cut and paste but use copy and paste special instead: Command+C followed by Command+Option+V.

Lock the Screen

I never leave a machine unlocked. Then I discovered that a Windows+L equivalent isn’t obvious in OS X. There are many tricks out there to mimic the same behaviour but unfortunately most of these are based upon either invoking the screen saver or logging off. Hey, I simply want to lock the screen, requiring a password to unlock when I get back.

I ran into the following solution which basically consists of creating a service using Automator and assigning a keyboard shortcut to swiftly invoke the service. The service itself executes the following command line:

/System/Library/CoreServices/Menu\ Extras/User.menu/Contents/Resources/CGSession -suspend

Exactly what I need. After creating the service with Automator – I named it “Lock Screen” – simply assign a keyboard shortcut to the service via System Preferences > Keyboard > Keyboard Shortcuts. I chose Control+Option+Command+L to make sure there is no conflict between existing, prioritized shortcuts in applications.

Task Switcher

In Windows, Alt+Tab brings a window to the foreground, whether its previous state was normal, minimized or maximized, it doesn’t matter. Not so on your Mac. If you minimize an application in OS X, Command+Tab won’t bring it back. The selected application is reactivated but a previously minimized window doesn’t receive focus; it keeps sitting in the dock. Try for yourself: open two applications, minimize the first, switch to the second and then try to return to the first via Command+Tab.

This behaviour seems normal, by design, as in OS X the Task Switcher iterates between applications and not really between windows.

I did find a workaround though: if you minimize a window, don’t use Command+M (the keyboard shortcut to minimize windows) but hide the window instead, using Command+H. Try and see the difference when you reactivate the hidden application via Command+Tab.

SQL Server 2008 R2 – The performance counter registry hive is corrupted.

Sequel number four (yes, 4!) of the SQL Server 2008 R2 installation woes saga.

Previous posts include:

  1. Version string portion was too short or too long
  2. Error code: 1605
  3. Version’s parameters must be greater than or equal to zero

This post discusses the following error which appears in Summary_%computername%_%date%_%time%.txt, located in C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log:

The performance counter registry hive is corrupted. To continue, you must repair the performance counter registry hive. For more information, see http://support.microsoft.com/kb/300956.

Performance counters

In normal circumstances, the value of Last Counter (e.g. 13400), located in HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib, should match the Counter counterpart, located just beneath the previous key, in HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib09. The exact same value should be there in the end of the large string.

Investigation of the registry’s keys and values on the erroneous target machine reveals a mismatch between the above values. In other words, we are dealing with performance counter corruption, also confirmed by the fact that trying to add a new counter in perfmon.msc (by clicking the + button), results in a dialog window popping up showing no performance counters to choose from.

Remediation

In order to fix the performance counter corruption, the performance counters have to be rebuilt. Simply follow the procedure as outlined in KB300956 step by step. If several Windows XP SP3 clients demonstrate the exact same problem, a batch file can come in handy to fix the issue swiftly. Here’s a procedure to create such file.

1) Expand perfc009.da_ and perfh009.da_ in the i386 folder of Windows XP’s original sources, and put both files in a separate directory, e.g. PerfMonFix.

2) In the PerfMonFix directory, create a new file PerfMonFix.bat with the following contents:

xcopy %~dp0*.dat %systemroot%\system32 /y
cd /d %systemroot%\system32
for /f "tokens=1 delims=:" %%i in ('findstr drivername *.ini') do lodctr /r:%%i

On the target machine, execute PerfMonFix.bat and launch the SQL Server 2008 R2 installer after verification of the performance counters. The error should be gone.

SQL Server 2008 R2 – Version’s parameters must be greater than or equal to zero

A third (!) and hopefully final blog post on SQL Server 2008 R2 deployment woes. Previous posts include SQL Server 2008 R2 – Version string portion was too short or too long and SQL Server 2008 R2 – Error code: 1605.

This time the installation problem comes with the following beauty:

Exception type: System.ArgumentOutOfRangeException
Message: Version's parameters must be greater than or equal to zero.
Parameter name: build

In this particular case, it appears the SQL Server 2008 R2 installer detects some SQL Server 2000 residues on the client and fails to determine its version.

SQL Server 2000

A quick investigation of HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server reveals that the 80\Tools key is still present on the machine, even though SQL Server 2000 has been uninstalled. Not surprisingly, the ClientSetup key, which denotes the version, is missing.

Two options:

  1. Install SQL Server 2000 before 2008 R2 – which is not an elegant solution.
  2. Give the SQL Server 2008 R2 installer a helping hand by recreating the missing key and value pairs

Solution

Dump the following info into a fix.reg file or recreate the following in the registry manually and the SQL Server 2008 R2 installer should continue successfully:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup\CurrentVersion]
"Language"=dword:00000409
"CurrentVersion"="8.00.194"
"CSDVersionNumber"=dword:00000300
"CSDVersion"="8.00.760"

SQL Server 2008 R2 – Error code: 1605

Yet another post on SQL Server 2008 R2 deployment woes.

An unattended installation of SQL Server 2008 R2 – developer/management tools on Windows XP SP3 clients in this particular case – might fail with the following error:

MsiGetProductInfo failed to retrieve ProductVersion for package with Product Code = '{0A869A65-8C94-4F7C-A5C7-972D3C8CED9E}'. Error code: 1605.

The error appears in Summary_%computername%_%date%_%time%_ComponentUpdate.txt and other log files as described here: View and Read SQL Server Setup Log Files.

Error 1605

This record of Windows Installer error codes on MSDN lists 1605 incorrectly as an indicator for not enough free space available on the target drive; DesktopEngineer.com however, states that 1605 designates a missing product, required to complete a custom action successfully:

1605 ERROR_UNKNOWN_PRODUCT This action is only valid for products that are currently installed.

Windows Installer GUIDs

In order to figure out what missing product might be the culprit, we’ll check a working installation for the GUID specified in the error message.

Windows Installer GUIDs have a different formatting than how they appear in the MSI tables. In this specific case, the product code {0A869A65-8C94-4F7C-A5C7-972D3C8CED9E} needs to be transformed into its equivalent which is stored in the registry.

Some swapping and reversing needs to be done:

  1. Reverse the first 8 digits
  2. Reverse the next 4 digits
  3. Reverse the next 4 digits
  4. Reverse the next 2 digits (8 times)
  5. Drop all hyphens

Applying the above transformation to {0A869A65-8C94-4F7C-A5C7-972D3C8CED9E} results in 56A968A049C8C7F45A7C79D2C3C8DEE9. Search for the value in the registry and soon MSXML appears to be the wrongdoer.

MSXML6

Now back to the client where the installation fails. Repeat the same search and you’ll probably notice that XML 6.0 Parser actually is present on the erroneous machine too, confirmed by the XML Parser entry found in appwiz.cpl.

Reinstallation of MSXML6 is the first next thing that comes to an IT guy’s mind. Unfortunately, the uninstall might fail with an exotic error. If this is the case, as a last resort other than swiping the client completely, the Windows Installer Clean Up Utility (a Microsoft tool which has been retired – in other words use at own risk) can forcefully remove installed products.

However, even after deleting MSXML6 entirely using msicuu.exe and reinstalling Microsoft XML Parser available here, the setup of SQL Server 2008 R2 still fails with the same error.

Solution

Trial and error to the rescue. So by means of elimination, the following simple solution appears to be fairly effective.

Delete the registry key’s parent containing 56A968A049C8C7F45A7C79D2C3C8DEE9 in HKLM\Software\Classes\Installer\UpgradeCodes. In this particular case, deleting the 7AB711B11CB5E91428E0D7F4F314C2B7 key first, results in a successful installation of SQL Server 2008 R2 afterwards:

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Installer\UpgradeCodes\7AB711B11CB5E91428E0D7F4F314C2B7]
"56A968A049C8C7F45A7C79D2C3C8DEE9"=""

Microsoft often moves in mysterious ways.

No Compatible Drives in CDBurnerXP

I’ve been using CDBurnerXP since day one. It’s a no-nonsense CD/DVD/ISO burning tool for Windows and it’s freeware. It supports Blu-Ray and HD-DVDs.

On my HP 620 laptop with Windows 7 Professional 64-bit, version 4.3.8.2568 of CDBurnerXP doesn’t recognize the internal burner, an HP DVDRAM GT30L according to devmgmt.msc, although it is listed as compatible here. The target device for the burning process drop-down list doesn’t contain the burner but reads No compatible drives instead.

Even though I’m quite sure that the problem did not exist with previous versions of CDBurnerXP, the issue is driver related – as is often the case.

In my case, the solution was to download and install Intel’s Matrix Storage Manager driver (which is available on HP’s support site). Reboot and the DVDRAM GT30L burner appears in CDBurnerXP.

Command Line Goodies – Part II

As a sequel to Command Line Goodies – Part I, here’s another trio of helpful tools I quite often use.

Nslookup

Use Nslookup to query DNS servers. The following example will retrieve all DNS records from the primary DNS server as configured in TCP/IP settings (or obtained by the DHCP lease):

nslookup -"set q=any" google.com

Robocopy

An all time classic, Robocopy won’t disappear from batch files pretty soon. Did you know it’s also possible to move – rather than copy – files? For instance, this line moves *.log files older than 100 days from e:\log to an archive directory e:\archive:

robocopy e:\log e:\archive *.log /minage:100 /mov

Grep

Grep is a powerful command-line text-search utility originally written for Unix. Fortunately, there are various Win32 ports available such as Grep for Windows or this older port by Tim Charron.

Just one simple example of what grep can do. The following line takes the two leading lines from all *.log files in e:\log containing a case-insensitive “START LOGON” and dumps the output in *.tmp files:

for %f in (e:\*.log) do grep -i -B 2 -e "START LOGON" "%f" > "%f.tmp"

Check out 15 Practical Grep Command Examples In Linux / UNIX for more examples.

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: