What are the challenges you face when working across database platforms? Take the survey
Options

Deploy Project Not Using CreateDatabase script.

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
Tagged:

Answers

  • Options
    rconradrconrad Posts: 9 New member
    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.
  • Options
    Sergio RSergio R Posts: 610 Rose Gold 5
    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>
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
Sign In or Register to comment.