following upgrading Visual Studio 2010 from 2008, the customer started to experience problems when debugging CLR assemblies.
The behaviors are:
- Deploying (debugging will automatically invokes deploy) a CLR assembly will take a long time
- Deploying a CLR assembly may fail with error “Deploy error SQL01268: CREATE ASSEMBLY for assembly failed because assembly failed verification" after waiting for a long time.
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.
Technorati Tags: Performance CLR
Solution 1 – manually deploy before debugging.
Step 1: do this only once by deploying your project to an empty database
- Point your project to an empty database that doesn’t have lots of objects and deploy the project. This should be fast.d
- As part of the deploy, VS generate a script called $(ProjectName).sql in bin\debug directory. Copy and save this script to a new location.
- Make some slight modification in the script.
- replacing the binary value from CREATE/ALTER Assemblies with the actual file path.
- comment out :setvar lines
- comment out :on error exit
- replace $DatabaseName to your real database
- look for CREATE and ALTER assembly statements. Replace the binary data with file path for CLR dll and pdb
Step 2: debugging
- Configure your project to the real database you plan to use and debug
- Under build menu|Configuraiton Manager, uncheck “deploy” for the CLR project
- Build the project
- Manually run the script in from above step to deploy assembly
- Debug your code.
- Note that every time you need to debug, you need to run the script. So solution 2 may be preferred by you.
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.
- Under build menu | configuration manager, uncheck “Deploy” for the CLR project.
- Come up with a script that would create and drop assemblies objects and create assembly. Note that you can use the step 1 of solution one to generate a SQL Script as well for this step.
- Add this script the your project directory as part of the project.
- In the script you can use variables so that you can pass ProjectDir
- At minimum, you need to deploy .pdb file as it contains symbols for debugging.
- Then in post event do this: sqlcmd.exe –S<serverName> -E -d<databasename> -i"$(ProjectDir)deployscript.sql" -v ProjectDir="$(ProjectDir)" (where <serername> and <databasename> are your SQL Server and Database names).
- To debug, do two steps
- Build the project. When you build the project, it will run the sqlcmd.exe to deploy the assembly.
- Then start debugging
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)bin\debug\CLRProject.dll'
ALTER ASSEMBLY [CLRProject] DROP FILE ALL ADD FILE FROM '$(ProjectDir)bin\debug\CLRProject.pdb'
CREATE PROCEDURE [dbo].[StoredProcedure1]
AS EXTERNAL NAME [CLRProject].[StoredProcedures].[StoredProcedure1]