Generate an executable SQL script from an SQL Server database project

Question

I have a SQL Server database project that can deploy the schema to a database, it creates a .dacpac and applies it to a database.

I would like to use the SQL file generated in an automated test class that will create all the tables and objects to an empty database.

I can't use the generated SQL file because it contains unrecognized commands like

:setvar DatabaseName "MyDB"
:setvar DefaultFilePrefix "MyDB"

How can I generate an executable SQL script from a SQL Server database project deployment?

Possibilities for getting the generated publish SQL script to work programmaticaly: C# via Process.Start to call SQLCMD.EXE -i filename.sql: msdn.microsoft.com/en-us/library/… C# via open source library to handle "GO" statements and SQLCMD-mode colon-commands (currently handles SQL files, but can be easily updated to accept a string instead): github.com/rusanu/DbUtilSqlCmd PowerShell via Invoke-SqlCMD: sqlservercentral.com/Forums/Topic1502697-1351-1.aspx — xLiSPirit
– xLiSPirit, Commented Feb 16, 2016 at 1:23

Community · Accepted Answer · 2017-05-23 12:23:20Z

2

Thanks to xLiSPirit and this SO post, I ended up making it work with the DacServices class and this piece of code :

    private void UpgradeDatabaseWithDacpac(string connectionString, string databaseName, string dacpacFileName)
    {
        DacPackage dacPackage = DacPackage.Load(dacpacFileName);
        DacServices dacServices = new DacServices(connectionString);
        dacServices.Deploy(dacPackage, databaseName, true);
    }

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Feb 16, 2016 at 1:33

Bruno

4,6757 gold badges65 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Generate an executable SQL script from an SQL Server database project

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related