How do I deploy a CLR Stored Procedure via the MsBuild command line?

Question

I can deploy a SqlClr project project from Solution Explorer by right clicking it an selecting Deploy. However, I would like a command line version where I can also specify custom ConnectionString.

Answer 1

The command is msbuild MySqlClrProject.csproj /T:deploy. This assumes the code is built, and, at least on my machine, build the debug build by default. If you want to rebuild the solution, deploy the release binaries, and use a custom connection string, the command is msbuild MySqlClrProject.csproj /T:Clean;Build;Deploy /p:Configuration=Release;ConnectionString="Data Source= .;Initial Catalog=dropme;Integrated Security=True"

You need to do this from a x86 2010 command prompt (MSBuild 4.0). It does not work in Visual Studio 2008 (MSBuild 3.5). I don't have Visual Studio 2012 to see if it works there.

If you attempt to run this from a 64 bit command prompt you will get the following:

c:\Users\jdearing\Documents\MySqlClrProject\MySqlClrProject.csproj(48,11): error MSB4019: The imported project "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\SqlServer.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk.     

Below is an example of what running the command successfully looks like:

Setting environment for using Microsoft Visual Studio 2008 x86 tools.

C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC>cd c:\Users\jdearing\Documents\MySqlClrProject

c:\Users\jdearing\Documents\MySqlClrProject>msbuild MySqlClrProject.csproj /T:deploy
Microsoft (R) Build Engine Version 4.0.30319.1
[Microsoft .NET Framework, Version 4.0.30319.261]
Copyright (C) Microsoft Corporation 2007. All rights reserved.

Build started 7/11/2012 4:58:04 PM.
Project "c:\Users\jdearing\Documents\MySqlClrProject\MySqlClrProject.csproj" on node 1 (Deploy target(s)).
SqlClrDeploy:
  Beginning deployment of assembly MySqlClrProject.dll to server . : dropme
  The following error might appear if you deploy a SQL CLR project that was built for a version of the .NET Framework that is incompatible with the target instance of SQL Server: "Deploy error SQL01268: CREATE ASSEMBLY for assembly failed because assembly failed verification". To resolve this issue, open the properties for the project, and change the .NET Framework version.
  Deployment script generated to:
  c:\Users\jdearing\Documents\MySqlClrProject\bin\Debug\MySqlClrProject.sql

  Dropping [MySqlClrProject].[SqlAssemblyProjectRoot]...
  Creating [MySqlClrProject].[SqlAssemblyProjectRoot]...
  The transacted portion of the database update succeeded.
  Deployment completed
AfterDeploy:
  ---SqlReference---
  Data Source=.;Initial Catalog=dropme;Integrated Security=True
Done Building Project "c:\Users\jdearing\Documents\MySqlClrProject\MySqlClrProject.csproj" (Deploy target(s)).


Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:09.37

c:\Users\jdearing\Documents\MySqlClrProject>