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:

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.

SQL Server 2008 R2 – Version string portion was too short or too long

An enterprise-wide rollout of Microsoft SQL Server 2008 R2 Developer Edition can result in serious headaches. On Windows XP SP3 clients, the installer may abort with a System.ArgumentException error: Version string portion was too short or too long. The logfiles residing in %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log reveal that the error occurs during the RunDiscoveryAction phase:

Running Action: RunDiscoveryAction
Running discovery on local machine
Error: Action "Microsoft.SqlServer.Configuration.SetupExtension.RunDiscoveryAction" threw an exception during execution.
Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: Version string portion was too short or too long. ---> System.ArgumentException: Version string portion was too short or too long.
at System.Version..ctor(String version)
at Microsoft.SqlServer.Discovery.Sql80DetectionInterface.GetFeatureProperties(String instanceName, List`1& outVal)
at Microsoft.SqlServer.Discovery.Sql80Discovery.EnumerateFeatureProperties(String instanceName, List`1& outVal)

Diagnosis indicates that most of the failures occur on clients where previously SQL Server 2000 (aka version 8) Analysis Services (SSAS) had been installed (or still is installed), confirmed by Sql80DetectionInterface in the log file.

Some refer to KB973301 and suggest to create or modify some registry keys related to SQL Server 2000, but that might not remediate the problem.

Even uninstalling SSAS 2000 may not always help as often garbage is left which is picked up by the SQL Server 2008 R2 installer, resulting in the infamous error.

So here’s a working scenario to forcefully remove SQL Server 2000 Analysis Services by:

1) Stopping and removing the MSSQLServerOLAPService service
sc stop MSSQLServerOLAPService
sc delete MSSQLServerOLAPService

2) Deleting the local MsOLAPRepository$ share
net share MsOLAPRepository$ /d

3) Removing the SQL Server 2000 installation directory
rd /s /q "%programfiles%\Microsoft SQL Server\80"

After which SQL Server 2008 R2 installs successfully.

Deploying SQL Server 2008 R2 Silently

The installation sources of SQL Server aren’t composed of one or two simple MSIs nor is it a software you want to repackage. The most obvious way to deploy SQL Server 2008 R2 is to a use the command line (setup.exe) in combination with a configuration file. In this example we’re deploying SQL Server 2008 R2 Developer Edition to desktops (none of the engines; only the tools). Steps to follow:

1. Generate the configuration file

Creating the configuration file is a straightforward process. Simply walk through the setup wizard, selecting required features and specifying options/settings as you would normally do during a manual installation. When the Ready to Install window appears, note the full path where ConfigurationFile.ini resides, copy the file and cancel the setup wizard.

SQL Server 2008 R2 Configuration File

2. Modify the configuration file

Open the INI file with your favorite text editor. You might want to modify/add:

  • QUIET="True" for a completely silent installation
  • IACCEPTSQLSERVERLICENSETERMS="True" (required)
  • PID="productkey" (unless working with volume licensing or if deploying an evaluation version)
  • Remove (comment) UIMODE="Normal" (not allowed with /quiet)
  • Remove (comment) INSTALLSHAREDDIR and INSTALLSHAREDDIR (so the default %PROGRAMFILES% paths will be used which might not refer to the drive/paths specified in the wizard)

Note that each parameter can be specified as an argument for setup.exe too. Arguments specified at the command line override those in the INI file.

3. Installation

Finally, execute setup.exe with the full path to the configuration file:

setup.exe /ConfigurationFile=\ConfigurationFile.ini [/hideconsole]

If completed successfully the installer will return 0 or 3010 (reboot required). During installation, detailed logs are created in %program files%\microsoft sql server\100\setup bootstrap\log.

Tip: prior to launching the installation, it’s not a bad idea to install some potentially missing prerequisites in advance, such as the .NET Framework 3.5 SP1 and Windows Installer 4.5 (which requires a reboot), especially if you already have separate packages for these products.

Resources

How to: Install SQL Server 2008 R2 from the Command Prompt
http://msdn.microsoft.com/en-us/library/ms144259.aspx

How to: Install SQL Server 2008 R2 Using a Configuration File
http://msdn.microsoft.com/en-us/library/dd239405.aspx

%d bloggers like this: