Publishing a SQL Data Tools 2012 Project: Enabling Single User Mode

Question

I have a CLR project that I am trying to publish using Visual Studio. I had to change the project to the SQL Data Tools project, and now it is not published. Every time I try, I get a timeout error. When I do this step by step, I find this line of code freezing on my server.

 IF EXISTS ( SELECT 1 FROM [master].[dbo].[sysdatabases] WHERE [name] = N'fwDrawings') BEGIN ALTER DATABASE [fwDrawings] SET READ_COMMITTED_SNAPSHOT OFF; END 

Basically, I know that he is trying to force the server in single user mode when I try to publish it. This is just for my staging server, not a production server, but it is still a problem. I can’t keep kicking everyone from the server and try to switch it to single user mode every time I want to update the CLR while I test its functionality. And I don’t want to wait for a maintenance cycle or downtime to advance it to production. Is there any way around this?

Answer 1

Presumably READ_COMMITTED_SNAPSHOT enabled for your database.

If so, you need to change the database design settings to fit. Check the isolation of the transactions “Read transactional snapshot” on the tab “Operation” in the section “Database parameters for the project”.

For me, this prevented the release of the publication, i.e. I can now publish successfully.

Answer 2

For a more secure way to deploy to your server, try using schema comparison .