SQL Express 2008 x64 Integration with Visual Studio 2008 SP1

This problem makes me nuts. I spent several hours try to resolve this problem. The following article is the best one which teach solve this silly problem step by step.

 

Ref: http://blogs.visoftinc.com/archive/2009/02/06/SQL-Express-2008-x64-Integration-with-Visual-Studio-2008-SP1.aspx

 

Yesterday I was trying to add a database to the App_Data folder of a simple web application I was creating.  During one of my computer re-installs, I didn't install SQL Express since I normally use a full blown SQL instance, so I really had no need for it.  Now that I did need it, I wanted to run SQL Express 2008.  I am running Windows Vista x64, so I proceeded to download SQL Express 2008 x64.  I installed it and loaded up SQL Server Management Studio to make sure the instance was working (I just used the default of SQLEXPRESS for the instance name).  Everything was fine.  I then fired up Visual Studio, right-clicked on the App_Data folder of the web app, and... error (Figure 1).

Figure 1 - Connections to SQL require SQL Express

The Fix - Option 1 - Use SQL Express 2008 x86

One quick fix for this problem is to install the 32-bit version of SQL 2008 Express instead of the x64 version.  I figured this was sort of silly, I'm running Windows x64 and I really hate installing 32-bit software if I don't have to.  But if you're looking for a simple solution without an extra download, this is it.

The Fix - Option 2 - Using SQL Express 2008 x64

Refusing to take the easy way out, I then started down the path of fixing this "the right way".  The first article I came across was John Roland's post on this error.  Not helpful in my case, but useful if your instance is named something other than the default for SQLEXPRESS. 

Next, I found someone else with my problem on the ASP.NET Forums.  Thankfully, it had a link to the fix (KB 957944).  However this isn't a something you can just directly download from the KB article since it isn't available to the general public just yet. After a quick Google search, I found a simple way to request the hotfix from Microsoft.  From there, I discovered a pretty cool site called Hotfixr, which automatically sends the correct request to Microsoft.  If you are looking for the specific one for this problem, you can use this direct link.

After you make a request, Microsoft will e-mail you a link to the fix as well as a password to unzip the fix.  After downloading it, I installed the fix as show in Figure 2 below.  Note this install took quite a while.  Make sure you have all your instances for VS closed.

Figure 2 - Installing the KB 957944 Hotfix

Configuring SQL

I then fired up Visual Studio 2008, attempted to add a database again, but I received another error.  This was a pretty simple one though, it just stated that Named Pipes wasn't enabled for the server.  D'oh forgot to do that.  Easy fix...  Go to the SQL Server Configuration Manger, click on the Protocols for the specific instance and enable Named Pipes (Figure 3).  

Figure 3 - Enable Named Pipes

After all of that, a database can successfully be added (Figure 4). 

Figure 4 - Fixed... A New Database in App_Data