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 []

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 []

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. 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 []