Beware 32bit and 64bit ODBC Data Source Administrators

While creating an SSIS Package in SQL Server 2008 R2 I ran into a problem with an “architecture mismatch between the driver and the application” when trying to use a DSN as a new connection.

It turns out this is the result of the differences between 32-bit and 64-bit drivers. When creating data source names using the ODBC Data Source Administrator (Control Panel > Administrative Tools > Data Sources (ODBC)). Apparently when you run this tool on a Windows 2008 R2 server (64 bit) you are running the 64 bit version of this tool which only recognizes 64 bit drivers. SSIS is expecting 32 bit DSNs using 32 bit drivers (I think – this is still sinking in).

The fix to this is to create 32 bit DSNs. To do this you need to run the ODBC Data Source Administrator located here:

C:\Windows\SysWOW64\odbcad32.exe

One caveat is that you cannot create DSNs with the same name – e.g. “MyReportDB” – in both the 32 bit and 64 bit Data Source Administrator applications. I named mine “MyReportDB_32” and “MyReportDB_64”. I also created shortcuts to both Data Source Administrator applications changing their names to reflect the 32 or 64 bit version.

The 64 bit version of the ODBC Data Source Administrator is located here:

C:\Windows\system32\odbcad32.exe

The following images show the difference of the Drivers tab in the Data Source Administrator applications. This is the 64 bit version of the tool:

And this is the 32-bit version – notice there are alot more drivers:

Hopefully this will save some time for others!

The complete error I was encountering was:

Test connection failed because of an error in initializing provider. ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Drive and Application

 

Many thanks go John DaCosta for his helpful explanation on his blog!

Advertisement

Transferring logins in SQL Server

I am in the process of upgrading our Prophet21 databases from SQL Server 2000 to SQL Server 2008 R2. After restoring the backup from the old server you are left with a database that has user logins assigned to it that do not exist in the new SQL Server. Furthermore, if you try to just re-create them in SSMS you’ll still have a problem because the “Security Identifiers” or SIDs will be different. You must transfer the logins from the old SQL Server to the new SQL Server.

First, to check for orphaned users in the restored database on the new SQL Server using the stored procedure sp_change_users_login:

This will list all the users in the database that do not exist in SQL Server. To correct this you need to create these users and their SIDs by transferring them from the old SQL Server. There are a few ways to do this but, I found the simplest is to use a modified version of the script Microsoft provides in kb Q918992 that I downloaded from this thread on SQLTeam.com. I ran this script on my old SQL Server.

After running this script I then ran the command (still on the old SQL Server) EXEC sp_help_revlogin which creates, as output, a series of script commands that will create new users when run on the new SQL Server. After running the script output by the stored procedure on the new SQL Server I re-ran the command to check for orphaned users and the result was none being found:

You can download the script I used or get it from the forums. I tweaked it a bit adding a line to check for the existence of the stored procedure it creates since I had run it once already before figuring out what was happening.

Another option for transferring users is to use the transfer logins task within SSIS. Andy Hayes explains how to do this on his blog but, I ran into problems — it appears to only work if the database name is the same on both the old SQL Server and the new SQL Server. I had changed the name of my database during the restore. This may still work but, my knowledge of SSIS is still fairly limited.

Yet another option presented by Shaun Stuart was to use SQL Server 2005 like a swing server — transferring users from 2000 to 2005 and them from 2005 to 2008. Apparently, even when able to use transfer logins task within SSIS going from 2000 to 2008 had problems that were overcome with his technique. You can read about this on his blog.

Using Microsoft Access for ETL

Challenge: Taking information out of MS SQL Server database and putting it into MySQL database – de-normalized for reporting (without spending a fortune).

Tentative Solution: Use Microsoft Access to bridge the divide with linked tables, pass-through queries, and append queries.

1. Create a simple “make-table” query in MS Access to query SQL Server and make a local table. This query will later be changed to an “update-table” query.
   Hint: add a constraint (or add a “top x”) to limit your data – you only need enough rows to build the structure and see it.
2. Use MySQL Migration Toolkit (or MySQL Workbench) using the Access database as source to build the scripts (create and insert) that would recreate the table in MySQL. Save output as scripts – if you allow the Migration Toolkit to build the table directly then you will need to edit the table. By saving to scripts you can add your edits to the script.
3. Edit the MySQL script (combining the create and insert scripts) and adding additional fields, keys, and indexes if needed.
4. Run the edited MySQL script against your MySQL database.
5. In Access add the new MySQL table using Link Table Manager.

   

   Linked Tables in Microsoft Access

6. Create a a pass-through query to “truncate” the linked MySQL table. This will empty the table when run.
   Note: this is only needed if you are rebuilding the data each time. Otherwise, if just appending yesterday’s data all you need is an append query.
7. Edit the query created in step 1 to be an “append” query and select the linked MySQL table. Remove additional constraints or limits added when building the MySQL table.

   

   Pass-through and append queries in Microsoft Access

8. Create a macro to update the MySQL table by first truncating it then updating it using the two queries.