FAQ: Error creating a Service-based database in Visual Studio 2008 SP1

  • Article

A few of you have run into problems when trying to add a new Service-based database to your project in Visual Studio 2008 SP1. This problem has been reported to Microsoft Connect and the Visual Studio team is working on a fix, but I thought I would provide some details on a workaround in the meantime.

What's the problem?

This issue occurs when you have Visual Studio 2008 SP1 (any edition) and SQL Server 2008 Express x64 installed. This won't happen if you have the 32bit version of SQL Express installed. You know you've hit the issue when you see this error:

Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: https://go.microsoft.com/fwlink/?LinkId=49251

The problem is caused by an error when checking the registry to verify that SQL Express is actually installed; it's installed, but Visual Studio doesn't think it's installed.

Two ways to work around the problem

The easy way out is to uninstall the 64bit version of SQL Express and replace it with the 32bit version installed in the WoW. You can download this version from the Express website. If you're fiercely devoted to the native 64bit installation, or you just don't like taking the easy way out, there is another option...

Congratulations on boldly accepting the challenge to stick with the native 64bit version of SQL Express in the face of this error. In order to "trick" Visual Studio into working with a database on this architecture of SQL while still preserving the expected behavior of how the database works, we're going to have to plumb the depths of working with a User Instance in order to create the database manually, add the existing database to the project and then "hook it up" once it's there.

Create the Database

To perform this part of you're task you'll need to connect directly to the User Instance with the client tool of your choice. I've already posted instructions on how to do this, so check those out first to get a connection to the User Instance. Once connected, we need to create a database in the same folder as the project you want the database to live in. You can do this using the standard UI dialogs in Management Studio by right-clicking on the Databases folder and selecting New Database. Once you have the dialog open, give the database a name and set the file path to the folder where your project lives. You can also do this in T-SQL if you'd like, here is a sample of the statement that would do this:

CREATE DATABASE database1

ON PRIMARY

(NAME = database1,

FILENAME = N'C:\Users\<username>\Documents\Visual Studio 2008\Projects\WindowsFormsApplication1\WindowsFormsApplication1\database1.mdf',

SIZE = 10MB,

MAXSIZE = 50MB,

FILEGROWTH = 10%)     

LOG ON

( NAME = database1_log,

FILENAME = N'C:\Users\<username>\Documents\Visual Studio 2008\Projects\WindowsFormsApplication1\WindowsFormsApplication1\database1_log.ldf',

SIZE = 10MB,

MAXSIZE = 50MB,

FILEGROWTH = 10%)

GO

As you can see, this is a normal CREATE DATABASE statement where I've specified the FILENAME argument to be the project folder for my project. Just replace the <username> place holder with your user name and your ready to go.

Important: Once the database has been created, you need to detach it from the User Instance before you go onto the next step or you're in for trouble later.

Adding the database to your VS project

Make sure you've detached the database after creating in the previous section. (I told you it was important.) Back in Visual Studio you can now add the database to the project.

  1. On the Project menu, select Add Existing.
  2. In the File Open dialog, navigate to the project directory select the database you created. (You may need to change the file type being shown so that you see the Data Files in the dialog.)
  3. Select the database and click Add.

You'll get the same error that got us here in the first place. Ignore it! Hit OK as if all is right with the world and you'll see that the database was added to the project, take that foolish error message.

Hookin' the database up

At this point your database is in your project, but we want to take the final step to make it just like it would have been if you did this using the Add New Item | Service-based Database method. Add New Item, in this case, would have automatically dropped you into the DataSet wizard, so lets create one now.

  1. On the Data menu, select Create New Data Source.
  2. In the Data Source wizard, select Database and click Next.
  3. Click the New Connection button.
  4. In the Add Connection dialog, click the Browse button.
  5. Select the database you created at the beginning and click Open. (If you didn't detach it like I told you to, this is where you're going to run into trouble. Go ahead and detach it now, I'll wait…)
  6. If you don't trust me, click the Test Connection button.
  7. Click OK.
  8. Back in the Data Source Configuration Wizard, click Next.
  9. Accept the option to save the connection string, feel free the change it to something that makes sense to you, and click Next.
  10. After VS scans the database and discovers it's empty, click Finish.

You've now got a spankin' new DataSet in your project that's connected to your database. You can work with the database as you would expect, for example you could use the Server Explorer/Database pane to add new objects to the database which you could then add to your DataSet. Like I said, it's not the most straight forward workaround, but if you only need to create one database, this will take a lot less time than uninstalling the 64bit version and replacing it with the 32bit version. Hopefully VS will release a fix for this issue soon; go over to MS Connect and cast your vote to show that this issue has affected you. The more people affected, the better the chance for a fix.

- Mike