OmegaMan's Musings

Here are some of the tips and tricks required to get SQL Express working with Visual Studio 2008 under Windows 7.

SQL Management Studio

Why they don’t supply this with the install of Express is beyond me. But you will need it to solve a problem in a future step so I recommend you download it. Microsoft SQL Server Management Studio Express Note check for a newer version than the one at the time of this writing.

Before you actually install it, do the next section first.

User Account Control Not Just For Vista

This situation actually affects most of the older install programs when it comes to the UAC. The user account control will have an install quietly fail an install and provide no warning to the user that it was the UAC. For example if you run the install for SQL Management Studio with the default UAC settings in Windows 7 this is the error you will get:

The installer has encountered an unexpected error installing this package. This may indicated a problem with this package. The error code is 29506.

The UAC will never come up with any warning either.

So change the UAC settings to be minimal (search for UAC in the start bar and select Control Panel –> Change User Account Control Settings) and set to minimal settings.

Install the Management Studio Now.

Connecting to the Database Complains about .Net…WHAT?

Once you attempt to connect to the sql express database VS2008 will complain such as:

Execution of .NET Framework code is disabled.

Set "clr enabled" configuration option and restart the server.

I love this error because it implies you need change Visual Studio’s setting. Oh no, its the SQL Express Server setting. To add insult to injury one cannot use Visual Studio’s SQL editor to execute the change! It has to be done from management express.

Wow…as a new developer that has to be a challenge. Microsoft gives the tools such as express, but makes it impossible to use out of the gate.

To resolve the problem open up SQL Server Management Studio Express, open up the instance of SQL Express and run this command:

EXEC sp_configure 'CLR ENABLED' , '1'
GO
RECONFIGURE
GO
ALTER DATABASE PutYourDatabaseNameHere SET TRUSTWORTHY ON
GO

Once run find Sql Server Configuration Manager in the start program menu and by right clicking the “SQL Server (SQLEXPRESS)” instance restart the service. At that point you should be able to go back to Studio and run the application. HTH