following upgrading Visual Studio 2010 from 2008, the customer started to experience problems when debugging CLR assemblies.
The behaviors are:
After investigation, it turned to be an issue in CLR assembly deployment process. For the issue to occur, the target database needs to have many database objects such as views, stored procedures (in terms of thousands). Visual Studio 2010 tries to reverse engineer the objects as part of deployment. Large number of objects can delay the process.
Note that this issue doesn’t occur if you run TSQL Script (by issuing CREATE ASSEMBLY) manually yourself to deploy CLR objects (preferred way to deploy into production).
The issue does pose challenges to debugging. By default debugging will automatically involve deployment. So essentially debugging will take a long time or may fail (as in the error above). Here are a couple of solutions for this problem.
Solution 1 – manually deploy before debugging.
Step 1: do this only once by deploying your project to an empty database
Step 2: debugging
Solution 2: using post build events
Requirement: you need to have SQL Server tools installed as it needs sqlcmd.exe. Instead of relying on automatic deployment, post build event will deploy the assembly and objects.
Script 1 (an example script for solution 2)
if OBJECT_ID ('StoredProcedure1') is not null drop procedure StoredProcedure1
if exists (select * from sys.assemblies where name = 'CLRProject') drop assembly CLRProject
create assembly CLRProject from '$(ProjectDir)bindebugCLRProject.dll'
ALTER ASSEMBLY [CLRProject] DROP FILE ALL ADD FILE FROM '$(ProjectDir)bindebugCLRProject.pdb'
CREATE PROCEDURE [dbo].[StoredProcedure1]
AS EXTERNAL NAME [CLRProject].[StoredProcedures].[StoredProcedure1]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.