Friday, April 28, 2006

SQL Server 2005 Express

I was playing around with SQL Server Express 2005 at work today. We are thinking about using it for the next release of one of our products. I moved my database over and attached it to the server and started working in Visual Studio 2005.

When I finished creating a CLR based stored procedure, on deployment, I got an error about “Incorrect syntax near 'EXTERNAL'”. I did some searching and finally found that you have to update the compatibility index on your database when moving from SQL Server 2000 to SQL Server 2005. The command to do this is ...

Exec sp_dbcmptlevel 'DatabaseName', '90'

Even after setting the compatibility index, I was still unable to execute my stored procedure, because CLR Integration was not enabled for SQL Server 2005 Express. I had to use the SQL Server Surface Area Configuration tool to enable the CLR Integration. Once that was done, all worked fine.

I hope this helps someone to avoid headaches in the future.

Technorati Tags: , , ,

No comments: