Created some SQL CLR functions and procedures in Visual Studio 2013.

Now you need to prepare the sql script for the distribution of functionality. At the same time, the script should update the assemblies in the database or add them if they are not in the database. The same with CLR functions and procedures.

I would like the studio to generate the script itself, but it does only partially, and it is the script that generates the script based on the current database. That is, if the function is already present in the database, the creation of the function does not proceed.

How to set up a studio for the necessary output?

    1 answer 1

    A simple solution - when generating a script, specify the studio as the target of an empty database. This will generate a script to create the base from scratch.

    It’s inconvenient to edit the name of the database in the project properties each time, so it’s easier to generate deployment script via MSBuild - from the console or from the build script.

    Sample proj file:

    Creating a clean database:

    <MSBuild.Community.Tasks.SqlExecute ConnectionString="Data Source=$(TargetDatabaseServer);Integrated Security=true;" Command="CREATE DATABASE [$(TargetDatabase)]" > <Output TaskParameter="Result" PropertyName="SqlResult" /> </MSBuild.Community.Tasks.SqlExecute>

    Deploy with script generation:

     <PropertyGroup> <DatabaseProjectBuildProperties>Configuration=Debug;Platform=AnyCPU;TargetConnectionString=$(TargetConnectionString);TargetDatabaseName=$(TargetDatabase);UpdateDatabase=False;</DatabaseProjectBuildProperties> </PropertyGroup> <MSBuild Targets="Build;Deploy" Projects="@(DatabaseProjectsToDeploy)" Properties="$(DatabaseProjectBuildProperties)" />