3

I have a SSDT project that has a few sql clr elements. They rarely get altered but every time I redeploy my db project it decides it must drop the assembly and recreate it.

I think its somewhat wasteful to keep doing that. Is there anyway I can configure it so that it will only recreate the assembly anytime I actually change the correlated C# sprocs?

If this will be helpful here is the profile I am using to generate the change script:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>#{DatabaseName}</TargetDatabaseName>
    <DeployScriptFileName>#{DatabaseScriptName}</DeployScriptFileName>
    <TargetConnectionString>Data Source=#{DatabaseServer}</TargetConnectionString>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <ScriptDatabaseOptions>True</ScriptDatabaseOptions>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <GenerateSmartDefaults>True</GenerateSmartDefaults>
  </PropertyGroup>
</Project>

I use octopus to do the deployment.

  • How are you publishing? Via Visual Studio menu, command-line? – Solomon Rutzky Nov 19 '14 at 15:45
  • I am using sqlpackage to generate the script against the target database then executing the script via octopus. – Michael B Nov 19 '14 at 15:46
  • I don't do anything fancy I just pass a templated config that I have octopus do variable replacement on. I've added it to the profile above. – Michael B Nov 19 '14 at 15:52
  • It just runs the generated sql script that I build from sqlpackage against the target db. The script that gets generated has the drop assembly and recreate assembly lines which is the problem. – Michael B Nov 19 '14 at 16:29
  • Ok, I was confused based on how you said something above. Can you please post the exact (minus real paths, etc) command line you issue for SqlPackage. – Solomon Rutzky Nov 19 '14 at 16:35
3

I've got the same problem when using the Schema Compare tool in Visual Studio 2013. From what I can tell, the reason it wants to keep deploying the SQLCLR assemblies is the Module Version ID (MVID) in the DLL. The MVID is a guid that is automatically generated by the C# compiler during compilation so each rebuild get the DLL a new MVID.

One option is to try and avoid a rebuild of the DLLs. If you're building locally with Visual Studio, you'll have to avoid the "clean" and "rebuild" options. If you're using a build server such as TFS you'll need to configure the build to not clean the workspace.

The other option that I'm going to try is to have the C# code in a separate project, and "import" the DLLs into the main database project as and when the C# code changes. Pretty much as I used to do when using the older style "DataDude" projects. Using the latest version of SSDT for VS2013, I've added the SQLCLR assembly DLL as a reference in the main SQL Server project, and set the "Model Aware" property to "True". This properties seems to be required to allow me to reference the assembly in the store procedure / function DDL statements.

  • I am struggling with CLR assembly being recreated each time also. I just noticed that I am referencing to CLR project dacpac not dll as you mentioned. Could you explain differences between referencing to dacpac and dll? – scar80 May 16 '17 at 6:38
  • @scar80, I've not used DACPAC as a reference to a SSDT project, but I remember seeing is an option. It is possible to "unpack" a DACPAC, and if you unpack yours and examine the SQL script in it, I think that will be where the drop and create of the Assembly is. – Recce May 17 '17 at 10:15
  • @scar80 As for the difference between referencing a DLL and DACPAC, you want to add a straight Reference to the DLL file rather than a "Database Reference". (Sorry I was interrupted by my boss :-) ) – Recce May 17 '17 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.