How to pass SQLCMD variables du MSBuild during Build?

Question

I am referencing a database project during publish in a second database project. For easier reference, let's call them Database and Database.Tests. Database is being referenced by Database.Tests

During deployment the Database project will be deployed twice to the same machine. Once as "Database" and once as part of "Database.Tests"

Doing this manually using the publish option is working as expected, however when using SqlPackage.exe I am running into an error when publishing the seoncd database:

*** Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg 33415, Level 16, State 4, Line 5 FILESTREAM DIRECTORY_NAME 'AZE' attempting to be set on database 'Database.Tests' is not unique in this SQL Server instance. Provide a unique value for the database option FILESTREAM DIRECTORY_NAME to enable non-transacted access.
Error SQL72045: Script execution error.  The executed script:
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET FILESTREAM(NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'AZE'),
                CONTAINMENT = NONE
            WITH ROLLBACK IMMEDIATE;
    END

Here is what I am doing. the first thing is to create the test database. To do that I am deploying tSQLt, the Database project and lastly the database.tests project using the following sqlPackage.exe commands:

"C:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe" /Action:Publish ^
    /SourceFile:"Database.dacpac" ^
    /TargetDatabaseName:"Database.Tests" ^
    /TargetServerName:"server\instance" ^
    /v:MSSQL_USER="some_user" ^
    /v:MSSQL_PASSWORD="somepassword"

"C:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe" /Action:Publish ^
    /SourceFile:"Database.dacpac" ^
    /TargetDatabaseName:"Database.Tests" ^
    /TargetServerName:"server\instance"

"C:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe" /Action:Publish ^
    /SourceFile:"Database.Tests.dacpac" ^
    /TargetDatabaseName:"Database.Tests" ^
    /TargetServerName:"server\instance"

# at this point we deploy the second database into a database called `Database`   

"C:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe" /Action:Publish ^
    /SourceFile:"Database.dacpac" ^
    /TargetDatabaseName:"Database" ^
    /TargetServerName:"server\instance" ^
    /v:MSSQL_USER="some_user" ^
    /v:MSSQL_PASSWORD="somepassword"

Can I dynamically change the filestream directory setting at deployment-time or circumvent this issue in some other way? In theory I only need the schema, sprocs and functions from the Database project in my test database to execute my tSQLt tests and nothing else.

Answer 1

This may not answer your question directly, but here goes.

We make extensive use of SSDT and tSQLt. In our database solutions, the unit tests reside in their own project. (Using composite projects the root project is everything common to all environments; dev, stage, prod and unit test are separate projects in the solution.) The dev project has a database reference to the unit test project. The reference is marked as same database. That way when dev deploys it carries the unit test project items with it. And the unit test project would not need the FILESTREAM settings.

FILESTREAM settings would be relative to each environment project and would not be in the unit test project. That way only a single reference is ever deployed.

If you only have the root project and a unit test project in the solution you could still accomplish it with a database reference as noted above. Either way, the unit test project is never deployed directly, only by reference from another project in the solution.