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
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.
---- Partial Output
---- 01-Create_Database.sql (In Pre-Deployment Folder)'