OOPS, I got an error. Ensuring The Correct Bitness Provider is Installed

The field of Business Intelligence involves consolidating and aggregating data from a number of different and often highly disparate data sources. The cool part is that working in the field of BI means that we get to play with a bunch of really neet toys like SSRS, SSIS, and SSAS. Since those neet toys all use data sources and can be used as data sources, we also get to have a bit of fun with things like connection strings as well as an assortment OLE DB providers when designing and deploying projects. (OK. I admit that I'm a major data geek).

The development process typically presents a certain set of challenges, given that BI projects must be molded to meet often highly unique business requirements. So you've worked for several weeks building an optimal solution and it's finally time to deploy the solution only to encounter an error message something like the following:

The provider 'XXXOLEDB.1" is not registered.

The following system error occurred: Class not registered

A connection could not be made to the data source with the DataSourceID of '4a023caf-72d5-4021-af2d-49568052e42c', Name of 'Custom 3'.

The error message is reasonably self-explanatory, and is raised because the provider used for the data connection doesn't exist on the system. But wait, the project was developed on the same machine that it was deployed to so obviously the OLE DB provider exists and is installed on the system since it could be visualized in the Visual Studio development environment.

Yes, the OLE DB provider does exist and it can be visualized from the Visual Studio IDE, so you can rest assured that you weren't dreaming (or having a nightmare) for several weeks. What really happened is that the Visual Studio IDE is a 32-bit application which will result in calls to the 32-bit versions of oledb32.dll and rundll32.dll which allows the Visual Studio shell to only see the 32-bit providers and not the 64-bit providers. In the vast majority of instances, the server processes are 64-bit applications which will result in calls to the 64-bit versions of oledb32.dll and rundll32.dll which will allow the server processes to see only the 64-bit OLE DB providers and not the 32-bit providers.

So now the question arises "How can I make certain that the correct 64-bit provider is installed on my server?" Fortunately, Faruk Celik created a blog posting that describes how to use a UDL file on a 64 bit machine.

You can test it out by creating a new text file on the desktop named "test.udl". Once that's created, execute the following command from an elevated command prompt to see the 32-bit providers that exist on the machine (note that you'll need to substitute your account for <user_alias>):

C:\Windows\syswow64\rundll32.exe "C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll",OpenDSLFile C:\users\<user_alias>\desktop\test.udl

You should see a Universal Data Link property sheet that lists all of the 32-bit providers that are currently registered on the system.

To open the same file and view the 64-bit providers that are installed, run the following command from an elevated command prompt:

C:\Windows\system32\rundll32.exe "C:\Program Files\Common Files\System\Ole DB\oledb32.dll", OpenDSLFile C:\users\<user_alias>\desktop\test.udl