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

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.

KB982861 could not be installed

Internet Explorer 9 Language Packs are provisioned by different x86 and x64 Windows Update files, named as IE9-Windows6.1-LanguagePack-xAA-LLL.msu, where AA = architecture (86 or 64) and LLL = language code (e.g. nld for Dutch).

When trying to install these language packs via wusa.exe, Windows Installer might throw the follow error:

Windows update "Update for Windows (KB982861)" could not be installed because of error 2147944003 "Fatal error during installation." (Command line: ""C:\Windows\system32\wusa.exe" "X:\IE9-Windows6.1-LanguagePack-x86-nld.msu" ")

Search no more. Although there doesn’t seem to be any resource (for now) that states so formally, you cannot deploy IE9 LPs unless the same Windows 7 Language Pack has been installed previously.

In other words, after installing the Windows 7 Dutch Language Pack, IE9-Windows6.1-LanguagePack-x86-nld.msu for Internet Explorer 9 installs succesfully too.

GoogleCL on Synology DiskStation

Some time ago I decided I would not upgrade my SOHO server (the dearly beloved Zeus, a Windows 2003 box acting as a file/print/mail/etc server) but to move these services to the cloud. Despite my mainly Microsoft oriented professional background, I choose Google Apps rather than Microsoft Office 365. I buried the server, reluctantly concluded not to use Active Directory any more (nor any other directory service)  in this heterogeneous environment of Windows, Linux and Mac clients (and plenty of other devices). As far as storage is concerned, I bought a Synology DiskStation DS212+ NAS to safeguard my personal and business files. In short: less is more.

I’m using Google Docs intensively now. Despite my beliefs and unconditional trust in the cloud (and Google in particular), the paranoid control freak in me wanted to have a local “backup” of what I’m storing in the cloud. So here’s a little post on how to copy files from Google Docs to the DS212+.

Prerequisites

Enable Secure Shell on the NAS first if you haven’t done so yet:

By doing so we’ll be able to connect by ssh from a Linux or OSX terminal session (or PuTTY if you’re running Windows). Connect to the NAS with root (which has the same password as admin).

In the next following three steps, we’ll 1) bootstrap the Synology NAS to install ipkg (the Itsy Package Management System), 2) install Python, the Google Data APIs Python Client Library (gdata-python-client) and the Command Line Tools (GoogleCL) and finally 3) Test GoogleCL.

Bootstrap

In order to install ipkg (via a bootstrap script), you need to figure out the CPU of your Synology NAS beforehand because these scripts are specific to the processor. In my case, the DS212+ CPU seems to be a Marvell Kirkwood mv6282.

On this page, you’ll find URLs to download each specific script (xsh) and straightforward instructions on how to install it, follow these carefully. In short: download the xsh file, execute it and reboot (there is a reboot command available in Synology’s BusyBox).

After rebooting, ipkg should be functional. First thing to do after rebooting is to update the package repository by executing ipkg update. There is no need to upgrade (as stated in this article) as we don’t have any packages installed yet.

Python

In order to install Python, we’ll check which packages are available first:

ipkg list python*

Install the Python 2.7 package (ipkg will automatically download and install dependencies):

ipkg install python27

Next, test if Python is working properly:


If Python was installed successfully, download, extract and install Google’s gdata-python-client first, then GoogleCL. Builds 2.0.15 and 16 of gdata-python-client seem to be broken (at least they didn’t work on my box – AttributeError: 'module' object has no attribute 'DOCLIST_FEED_URI' error upon testing GoogleCL), so I advice to download build 2.0.14 available here:

wget http://gdata-python-client.googlecode.com/files/gdata-2.0.14.tar.gz

Decompress the tarball:

tar xvf gdata-2.0.14.tar.gz

And finally, install the goodies:

cd gdata-2.0.14/
python2.7 setup.py install --record=files.txt

Check files.txt if you need to troubleshoot and eventually delete the source files downloaded and extracted here if the installation succeeded.

Repeat the same procedure for GoogleCL, latest version, available here.

Test

Time to test GoogleCL! google should be installed in /opt/local/bin. Try to execute google and invoke help:


Done for now, in a next post we’ll create a (cron)job to backup files from Google Docs to the Synology DiskStation automatically on a daily or weekly basis.

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.

Automatically Refreshing a Background Image

Example

Take a look at http://www.ktown.be as an example. This simple web page has a background image (taken from a webcam located in the city of Kortrijk), stretched to fill the entire page. The background is automatically refreshed every 15 seconds – smoothly, without flickering.

Stretching

Stretching the image to cover the entire background is achieved through simple HTML and CSS. The background image imgBackground is contained in a div divBackground, resized to fit the entire page.

body {
margin: 0;
padding: 0;
height: 100%;
width: 100%;
}


#divBackground {
background-image: url(http://193.190.76.133:8080/record/current.jpg)
position: absolute;
}


img#imgBackground {
position: fixed;
top: 0;
left: 0;
width: 100%;
height: 100%;
}

The only drawback in the width: 100% and height: 100% combination, is that the aspect ratio of the original image is not maintained, which is no big deal in this case.

Refreshing

Automatically reloading the image is plain simple too, however keep in mind that:

  1. We don’t want flickering to occur, neither do we want to notice the reloading process (block by block)
  2. In most cases, the browser will reuse the image stored in its cache, in other words we have to use a trick to prevent this behaviour

In order to refresh the background without having the browser reloading the image from its cache, concatenate the request uri with the output of a call to Date.getTime(), which returns the number of milliseconds passed since January 1, 1970. Alternatively, a random number could be used. By doing so, the browser is fooled into thinking that it is a different image being requested that the one stored in cache.

<head>
...
<script type="text/javascript">
function reloadBackground() {
url = "http://193.190.76.133:8080/record/current.jpg?" + new Date().getTime();
img = document.getElementById("imgBackground")
img.src = url;
}
</head>

Next, the above function is to be executed by a timer, initiated in the body onLoad event with a call to Window.setInterval(). Pass the name of the JavaScript function reloadBackground() as the first and the number of milliseconds as the second argument – e.g. 15000 for 15 seconds.

onLoad="setInterval('reloadBackground()',15000)">

That’s it. Using a combination of some simple HTML, CSS and JavaScript basics, the background image is automagically and smoothly refreshed.

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.

%d bloggers like this: