Categories
Computers and Internet SQL Server

Installing Balanced Data Distributor on SQL Server 2008 R2 SP1

Edit: This post refers to an older version of the BDD installer. As per JasonH’s comment below, Microsoft has released a new installation package which should hopefully fix the installation bug. It can be found here: http://www.microsoft.com/en-us/download/details.aspx?id=4123

Original post:

Microsoft’s Balanced Data Distributor does not install on top of SQL Server 2008 R2 SP1. It installs fine without SP1 but otherwise comes up with the error:

“The installation is not successful. Check the following prerequisites: 1. Either Integration Services or BIDS has to be installed. 2. The version of these components has to be either SQL Server 2008 SP2 (or future SPs) or SQL Server 2008 R2 (or future SPs)”

In my case all the pre-requisites were met. As per this thread I examined the registry keys it was checking for the version numbers using Process Monitor. I then modified the keys to pretend I was running SQL Server 2008 R2 RTM, ran the BDD installer again (successfully) and modified the keys back to their original values.

Warning: This is not best practice advice! If you do the same as I did and your production system is rendered unusable, this will be entirely your fault. I did this on a throwaway development environment to save time uninstalling SP1 and reinstalling it.

The keys I altered were all in the following path:

  • HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\

The specific keys and the values I set them to were:

  • DTS\Setup\SP = 0
  • DTS\Setup\Version = 10.50.1600.1
  • BIDS\Setup\SP = 0
  • BIDS\Setup\Version = 10.50.1600.1

When setting up a production system, please ensure you apply the BDD installation before SP1. Don’t use this technique, which will probably render your environment unsupportable!

Categories
SQL Server

Reporting Services Sysprep Resolution

Reporting Services 2005 was installed on a virtual machine prior to a sysprep. I received errors from http://localhost/Reports and http://localhost/ReportServer that ultimately indicated that it couldn’t connect to the reporting database.

The resolution to this is to run a tool called "rsconfig". In my case, which I suspect is pretty common, the required command was:

rsconfig -c -s localhost -d ReportServer -a Windows

This led to the next issue, which was that Reporting Services was no longer able to "decrypt the symmetric key used to access sensitive or encrypted data in a report server database". I believe this is due to the fact that the public/private key of the virtual machine was changed after sysprep was run. Better practice (I’ll refrain from using ‘best practice’ here, as I’m sure there is a better process) would be to backup the symmetric key using the Reporting Services Configuration Manager before running sysprep, and then restoring it afterwards.

However, I had not backed up the key. So the solution was to use the configuration manager’s "Delete encrypted content" feature on the "Encryption Keys" page, then "Change" the key. This was fine for me to do as I had no schedules or encrypted connection strings in my Reporting Services database.

 

Categories
SQL Server

Excel Connection Manager and Excel Source in Windows x64

There is no Microsoft.Jet.OLEDB.4.0 provider for 64 bit Windows editions. If you need to use an Excel Source task or Excel Connection Manager in Integration Services on a 64 bit machine (I’m using Vista Ultimate x64), you will need to set your packages, or your project, not to use the 64 bit runtime. This is done by setting the Run64BitRuntime property to false on the project properties’ Debugging tab.

This article on MSDN provides more information about Integration Services running 64 bit editions of SQL Server and Windows.

Also note that if your source file is Excle 2007, the Excel Connection Manager cannot be used and the OLE DB Connection Manager must be used instead. See the note in the linked MSDN article about the Excel Connection Manager.

Technorati tags:

Categories
SQL Server

SSIS Lookup Transformation for SQL Server datetime type and DT_DATE

How do you perform a Lookup transformation using an input column of type DT_DATE (e.g. from a spreadsheet input) and a SQL Server table column that has the datetime type? Dragging the former onto the latter results in the error message "One or more columns do not have supported data types, or their data types do not match".

The answer is to create a Data Conversion transformation earlier in the flow. In this task, convert your input column to the type "database timestamp [DT_DBTIMESTAMP]". Make a note of the output alias you have entered. Now go back to the Lookup transformation and drag the new column (i.e. <output alias> of the Data Conversion transformation) onto the datetime column in the lookup table.

 

Categories
SQL Server

Bugs/Omissions in Visual Studio Team Edition for Database Professionals

 

I’m using Visual Studio Team Edition for Database Professionals and have spotted a couple of niggles with it.

1) If you rename a foreign key field, the schema compare fails to recognize that it needs to update the foreign key constraint. Instead it creates a new one and leaves the old one in place and unchanged, causing an error.

2) Extended properties are behaving strangely. It seems to be dumping them all into a file called “ScriptsIgnoredOnImport.sql”, appending it on to any entry it left in that file last time it ran. As far as I can tell you need to manually copy the extended property creation lines out of this file and into the individual table SQL scripts in the project. Irritating.

 

Categories
SQL Server

Calculated Measures DisplayFolder

In Business Intelligence Studio (AKA Visual Studio 2005), when editing a cube, there is a tab for creating calculations. This tab contains a handy ‘script organizer’ pane showing a list of the names of all your calculations, names sets, etc. Unfortunately if you select an item within the script organizer, the standard properties window does not show "Display Folder" and "Related Measure Group" properties. To get to these properties, there is a "calculation properties" icon near the top of the calculations tab.

 

Categories
SQL Server

SCOPE_IDENTITY()

A technical post for a change. We recently noticed a difference in SQL Server 2005 over 2000 around the use of the SCOPE_IDENTITY function. I’ve been used to "SELECT SCOPE_IDENTITY()" returning INT as its data type, as that was the largest data type that was allowed as an IDENTITY column. However, it now seems to be returning DECIMAL.

A quick investigation shows that DECIMAL can indeed be used as an IDENTITY column. There is a restriction that the scale must be zero, i.e. DECIMAL(n,0), so that the field only contains whole numbers.

Exciting? Maybe not. Interesting? Evidently enough for me to blog it.

Technorati tags: ,