ODBC 32 vs. 64 bit

Scenario: I needed to connect 32 bit software running on a 64 bit Windows 7 OS to an SQL server via ODBC. Using a User DSN, everything was fine - setting up a System DSN instead, the application could no longer find it or and connect to the database. I preferred setting up a System DSN, so that any user logged on to the computer would be able to run the software without further hassle in setting up the needed ODBC connection.
Cause: An ODBC System DSN in 32 and 64 bit versions are two separate matters, hence a 32-bit application will not see a 64 bit ODBC System DSN and vice versa. User DSNs are however visible on both sides. Confusing.
Gotcha: Both the 64 and 32 bit version of the ODBC tool's is named odbcad32.exe. Hence, the Win7 standard method of writing this in the search field at the bottom of the start menu would always give you the 64 bit version. You have to run %windir%\syswow64\odbcad32.exe (for instance via the run dialog, keyboard shortcut winkey-r) to start the 32 bit ODBC application.
Reference: Microsoft's Knowledge Base article 942976

Cannot logon to newly installed SQL Server

Problem: After a fresh install of SQL Server 2008 R2 on a Windows 7 computer, where Windows user group BUILTIN\Administrators were given administrative rights, I was not able to log on to the database from Microsoft SQL Server Management Studio while logged on to Windows as a user who was a member of the Administrators group and thus should have been granted access. The error received was: Login failed for user 'COMPUTERNAME\user'. (Microsoft SQL Server, Error: 18456).

Solution: Turn off User Account Control, or if you prefer to leave it on, make sure you start SQL Server Management Studio as an administrator (right-click the icon and select Run as administrator).

Accesing an .mdb database file fails after SQL Server upgrade

I have a feeling this is a pretty classic problem, and there's probably a variety of related problems out there - anyways, this caused me some headache today:

Scenario: I am developing an application that uses a simple .mdf database file (SQL Server Compact) to store and retreive data. The app runs well, even when taking it to other computers where SQL Server Express (SSE) is installed as a dependency application for my app. Today I installed a new version of SSE (2008 R2) on my own computer that I use to develop. Because of some clutter of old installations (2005 and 2008), I first removed the existing SSE completely, then installed the new one.

Problem: When trying to run my app after having installed the new SSE, it crashed with the following message:

Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance

At first I did a compare of the parameters used to start the SQL Server service and the SQL VSS Writer with the same services on other computers where it worked. They were identical!

Solution: This posting on social.msdn.microsoft.com gave me the solution. Since I am running Vista, I renamed the following directory (instead of deleting it, to make sure I had a safe return in case it made things worse):

C:\Users\[username]\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

I restarted my application, and voilla! I was back on track!

Reason: According to Scott J. Baldwin on the above mentioned posting, referring to Reen's suggestion B, "this error is often caused because the parent instance (for whatever reason) can't copy the system databases to the users local isolated storage folders. Sometimes it is because of a previous install of SQL Express has left files in that directory which is why Reen's suggestion sometimes works" - Thanks guys!

Creating an installer for your software that checks for and installs .NET and SQL dependencies

I developed a small software package for a client utilizing a SQL Server 2008 Express database and .NET 3.5 technology. The client wanted a simple installation package to distribute the software to several computers. Hence, the package should check for dependencies, like whether or not the .NET 3.5 framework and SQL Server 2008 Express was installed.

My eyes quickly fell upon the excellent Inno Setup freeware package. Together with this adaption of Inno found on The Code Project website, to include and/or download the needed dependency packages, it turned out to be the perfect tool for the job.

I had to develop a new script to be able to include the SQL Server 2008 R2 Express, SQL Compact Edition 3.5 for Desktops and MS Installer 4.5 in my package, but it was a breeze with all the good work already done. Check out my posting over at The Code Project to see these adaptions.