Deploy Project Not Using CreateDatabase script.

rconrad

I'm using SCA in Visual Studio.  I change my database connection to a database which does not exist.  I click "Deploy Project".  I get an error indicating the database does not have trustworthy on. I have altered CreateDatabase.sql as shown below.  It does not appear that this script is executed.  I also tried  a Pre-Deployment script. 01-Create_Database.sql shown below.  This script executes, but does not create the database (note output line in bold).  Where is the database created?

---- Partial Output

Generating "C:\Code\UtilityBilling\Databases\SCA\Client\ClientDB\obj\Debug\ClientDB.sql"...

Deploying "C:\Code\UtilityBilling\Databases\SCA\Client\ClientDB\obj\Debug\ClientDB.sql" to [WowClient2]

----- executing pre-deployment script "Pre-Deployment\01_Create_Database.sql" -----

Running Old Create Client2 (The predeployment script runs, but apparently the db already exists since we don't see any output from inside the if block)

----- executing pre-deployment script "Pre-Deployment\02-PayGoSymetricKey.sql" -----

# Beginning transaction

# Setting up migration log table

# Creating a new migration log table

Creating extended properties

# Setting up __SchemaSnapshot table

# Truncating __SchemaSnapshot

***** EXECUTING MIGRATION "Migrations\1.0.0-Baseline\001_20200708-1757_rconrad.sql", ID: {ff941300-0757-40bf-a502-3a5971605dd1} *****

Creating symmetric keys

Creating CLR assemblies

Msg 10327, Level 14, State 1, Server RCONRAD-LT, Line 2

CREATE ASSEMBLY for assembly 'PayGoSQLUtilities' failed because assembly 'PayGoSQLUtilities' is not trusted. The assembly is trusted when either of the following is true: the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission, or the assembly is trusted using sp_add_trusted_assembly.

C:\Code\UtilityBilling\Databases\SCA\Client\ClientDB\Migrations\1.0.0-Baseline\001_20200708-1757_rconrad.sql: Error:  : An error occurred during script deployment. Output from SQL Server follows.

---------------------------------------------------------------

Msg 10327, Level 14, State 1, Server RCONRAD-LT, Line 2

CREATE ASSEMBLY for assembly 'PayGoSQLUtilities' failed because assembly 'PayGoSQLUtilities' is not trusted. The assembly is trusted when either of the following is true: the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission, or the assembly is trusted using sp_add_trusted_assembly.

---------------------------------------------------------------

----- CreateDatabase.sql (in Provisioning Folder)

PRINT N'CUSTOM: Creating database [$(DatabaseName)]...';

GO

CREATE DATABASE [$(DatabaseName)] WITH TRUSTWORTHY ON; -- MODIFY THIS STATEMENT TO SPECIFY A COLLATION FOR YOUR DATABASE

GO

---- 01-Create_Database.sql (In Pre-Deployment Folder)'

print N'Running Old Create $(DatabaseName)'

IF (DB_ID(N'$(DatabaseName)') IS NULL)

BEGIN

PRINT N'NOT Creating $(DatabaseName)...';

END

--GO

--IF (DB_ID(N'$(DatabaseName)') IS NULL)

--BEGIN

-- CREATE DATABASE [$(DatabaseName)] WITH TRUSTWORTHY ON; -- MODIFY THIS STATEMENT TO SPECIFY A COLLATION FOR YOUR DATABASE

--END

rconrad

Further info.  The database did exist it just doesn't show in object explorer.  If I drop the database I am prompted to "Change Connection" or "Create Database" when I open the project.  It seems that I do not have access to how the database is created from Visual Studio.

Sergio R

If the project was created with SCA VS 4.2.20126 or earlier, then database creation uses the the Pre-Deployment script. If this is the case to use the script in the Provisioning folder you need to enable it by adding the following to the .sqlproj file:

<PropertyGroup>
<Provisioner>CustomScripts</Provisioner>
</PropertyGroup><br>