CLR Integration and SQL Express XCOPY deployment hell!
I originally developed my application using SQL 2005 Developer Edition, but want to switch to using an XCOPY deployed DB on SQL Express for deployement.
I have successfully copied the database.mdf/ldf files over to me project, and can connect using Data Source=.\SQLExpress and AttachDbFilename=|DataDirectory|\[database].mdf attributes.
The question is, how do I enable CLR integration for my C# SP's?
I've tried executing the following in various places:
sp_configure 'clr enabled', 1
go
reconfigure
go
But I obviously haven't hit the spot because I'm getting the following error when VS deploys my SP library:
Error: starting database upload transaction failed.
Error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
Any ideas?
[790 byte] By [
Furty] at [2007-12-22]
You're using the right command to enable CLR, so that isn't the issue. This seems more to be about permissions. We need to take a few steps back to understand what you're doing here...
XCopy deployment typically involves using User Instances, but your connection string is not specifying a User Instance. On top of that, normal VS ClickOnce deployment usually puts things under Documents and Settings so that the path represented by |DataDirectory| is not accessible to a default installation of SQL Express (running as Network Service) unless you use User Instances.
You also have to consider that a User Instance is a special process of sqlservr.exe that is launched at runtime. This is not the parent instance; making settings on the parent instance does not affect the child User Instance. So here are my recomendations for mixing CLR and User Instances to support XCopy deployment.
- Enable CLR on your parent instance.
- Attach your database to the parent instance. (You may need to move it to a directory that is accesible to the parent.)
- In VS, make a connection to this database on the parent instance so you can work with it.
- Develop your CLR procedures, deploy and test them against the parent instance. (Hopefully this will get you past your first issue of the deployment failing.)
- Now that your procedures are working, detach your database from the parent instance and move it into your project directory.
- Add the database to your project if that is how you want to deploy it.
- Use a connection string that use User Instances.
- Have your code enable CLR at run time by running the appropriate sp_configure command. (CLR is off be default for User Instances)
- Use your CLR procedure.
If this is not an application that you are going to deploy repeatedly, you could manually enable CLR on the User Instance once SQL Express is installed on the computer. To do this, you would need to use a tool such as SSEUtil to connect to the specific User Instance and then run sp_configure as you've already indicated. If you're going to deploy this to multiple computers and users, it's easier to enable CLR at runtime.
Here are some resources to help you along:
Regards,
Mike Wachal
SQL Express team
Mark the best posts as Answers
