VSDB Common Pitfalls 1

  • Article

Symptom: The resulting database deployment script always creates the database

This seems to be a common problem users are struggling with like illustrated by the following set of forum post. A user creates a database project, imports an existing database, builds and deploys. The resulting script always performs a CREATE DATABASE, why?

The answer is very simple, by default there is no target database connection defined, the deployment detects this and instead of failing, it assumes the target database therefore does not exist and creates a deployment script which will create the database.

Lets walk through the sequence of events:

  1. Create a new database project, open the project properties and go to the Deploy tab

    image

    As you can see the target connection is empty.

  2. Import a database schema from an existing database. What most users expect, is when they import from an existing database, either as part of the New project izard or by using the “Import Database Objects and Settings…” option that the connection used will become the target connection, however this is not the case. The reason for this is because we do not want to incidentally override the database you import from. 

  3. Build and deploy the database project. The last step is to build and deploy the project. When doing this using the default settings you will get presented the following output in the Output Window

    ------ Build started: Project: mydb, Configuration: Debug Any CPU ------ mydb -> d:\users\gert drapers\documents\visual studio 2010\Projects\mydb\sql\debug\mydb.dbschema ------ Deploy started: Project: mydb, Configuration: Debug Any CPU ------ Deployment script generated to: d:\users\gert drapers\documents\visual studio 2010\Projects\mydb\sql\debug\mydb.sql

        The deployment script was generated, but was not deployed. You can change the deploy action on the Deploy tab of the project properties. ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ========== ========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

  4. Switch the deploy action to: “Create a deployment scripts (.sql) and deploy to the database”. By default the “Deploy” action only creates a deployment script and does not deploy it to a database.
    image

  5. Now Deploy again and it will fail, but why?

    ------ Build started: Project: mydb, Configuration: Debug Any CPU ------ Loading project files... Building the project model and resolving object interdependencies... Validating the project model... Writing model to mydb.dbschema... mydb -> d:\users\gert drapers\documents\visual studio 2010\Projects\mydb\sql\debug\mydb.dbschema ------ Deploy started: Project: mydb, Configuration: Debug Any CPU ------ mydb.dbschema(0,0): Error TSD01234: Before you can deploy a database, you must specify a connection string in the project properties or at a command prompt. Done executing task "SqlDeployTask" -- FAILED. Done building target "DspDeploy" in project "mydb.dbproj" -- FAILED. Done executing task "CallTarget" -- FAILED. Done building target "DBDeploy" in project "mydb.dbproj" -- FAILED. Done building project "mydb.dbproj" -- FAILED.

    Build FAILED. ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ========== ========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========

    So somewhere hidden inside an error message “Error TSD01234: Before you can deploy a database, you must specify a connection string in the project properties or at a command prompt.”, you will find that the connection string was not defined.

Now most users will not change the “Deploy action” because they first want to see what the deployment engine will produce, so they are at a complete loss and will never find out why they are not getting want they expect.

Summary:

  • If you want a incremental deployment script, the deployment engine must be able to perform a comparison between the project and the target database, which is identified via the “Target connection” setting on the project Deploy tab.
  • When you do not specify a target connection no errors nor warnings will get raised when the deployment engine only generates a deployment script which is the default setting.

I hope this dissolves one of the common VSDB pitfalls.

GertD @ DBProj.com