Top VSDB->SSDT Project Conversion Issues
This post is a guide to some of the more common problems that can be encountered when converting an existing VS2010 Database Project (VSDB) to an SSDT project. Possible solutions and workarounds are included where applicable. If you encounter a problem that is not addressed here, please post to the forum and the team will look into the issue. This post will be periodically updated to reflect new issues of which we are made aware.
Common Conversion Issues:
Database References
In the existing VSDB project system, database references to .dbschema files can be added to projects. These can be references to system databases such as master, or .dbschema files that represent other user databases. Since database references in SSDT projects must be made to .dacpac files, the database reference functionality in VSDB projects is not preserved upon project conversion to the SSDT project system. Thus, all database references must be recreated in the new SSDT project after conversion. If you rely on references to .dbschema files that were provided by an external source, it is important to obtain .dacpac representations of these databases before upgrading to an SSDT project.
It is possible to create a .dacpac representation of .dbschema files by doing the following:
1. Use VSDBCMD (documentation here), VSDB’s command line tool, to Deploy your referenced .dbschema file to a live database.
2. Via the SQL Server Object Explorer in SSDT, navigate to the database which you just created with VSDBCMD.
3. Right-click on the database in SQL Server Object Explorer and select Create New Project – This makes a new SSDT project from the database.
4. In Solution Explorer, right-click on the project node and select Snapshot Project – This creates a .dacpac single-file representation of your schema.
5. This .dacpac file, located in the Snapshots folder of your SSDT project’s directory, can now be used to create a database reference in a project converted from VSDB to SSDT, like a .dbschema reference in VSDB.
NOTE: This will only work if the original .dbschema file was created from a live database and was not modified or compressed in any way. (i.e. it must be a complete representation of the schema)
SQLCMD Variable Definitions
In VSDB projects, you can define multiple sets of SQLCMD variables and values via .sqlcmdvar property files. As mentioned *here* in the SSDT v. VSDB side-by-side, these files are only preserved when converting to an SSDT project in a certain circumstance. The means by which you can preserve them by leverages MSBuild configurations and SSDT publish profiles.
 |
To do this, begin by adding a new solution configuration to your VSDB project via the Configuration Manager of the VS Build menu.
 |
Next, in the VSDB project properties Deploy tab, switch the configuration to the newly created solution configuration. Set the configuration’s Sql command variable file to be the .sqlcmdvar file that contains the desired set of SQLCMD variables.
 |
Now, when the VSDB project is converted to an SSDT project, this configuration and associated SQLCMD variables will be brought in as an XML publish profile that can be used as a publish configuration. An example of the publish profile created on conversion and its XML segment corresponding to the SQLCMD variables is shown below.
Server-Level Permissions
 |
In VSDB Server Projects, you can grant server-level permissions to logins via the .sqlpermissions configuration file. An example of this is shown below, where VIEW ANY DEFINITION permissions are given to Login1.
In general, when VSDB projects (database or server) are converted, permissions that are defined in the .sqlpermissions file are scripted out into a Permissions.sql file that becomes part of the SSDT project. However, the SSDT project system does not support server level permissions, so upon project conversion, any server-level permissions that were part of the VSDB server project will spawn errors. An example of the resulting script segment and error is shown below.
 |
The workaround for this is to cut all of the server-level permissions statements from the Permissions.sql file and add them to a post-deployment script. This will enable the addition of server-level permissions upon deployment and will bypass the errors thrown by the project system.
Server Project Logins
Another common scenario that we have seen pop up on the forums is upgrading a solution that contains both a database project and a server project. More specifically, when you have a VSDB database project that references logins defined in a VSDB server project which is linked via a database reference you will be left in an error state after conversion.
As logins can’t be referenced in this manner in SSDT, the only way to bypass this issue and eliminate the unresolved reference errors is to redefine the logins in the converted database project. Keep in mind that there is only one project type in SSDT so both projects in the resulting solution will be SQL Server Database Projects. Adding the logins to what was originally the VSDB database project will allow them to be referenced by other objects in that same project.
Linked Server Definitions
In some scenarios in VSDB, it was valid to use SQLCMD variables as the SQLIDENTIFIER of an object. As this is not valid in SSDT object definitions, you will receive errors if you convert a VSDB project that contains this syntax to an SSDT project. One place in particular where we have seen folks hit this issue is in the definition of linked servers. This situation arises out of the need to change the definition of a linked server based on the target environment (Production, Test, etc.).
The following snippet shows a valid linked server definition in a VSDB project
where $(ProductionServer) is a SQLCMD variable that serves as the SQLIDENTIFIER of the linked server as well as the actual server path. As mentioned above, this definition will be flagged as an error once converted to an SSDT project.
There are two workarounds for this issue. First, if it is important to your application to keep this style of definition, you must move these blocks from Linked Server objects to a post-deployment script where this syntax is allowed, and deployment will then succeed.
It is suggested however, that the Linked Server object definition be changed so that the SQLCMD variable is used to define the @datasrc parameter. This is simply a different method of abstraction that allows the definition to be provided in a Linked Server object, yet still enables the use of SQLCMD variables to alter the path based on target environment. An example is shown below that sets up the same linked server as in the previous snippet.
Full Text Indexes and LocalDB
When you upgrade an existing VSDB project, the default debug database that is used as part of the F5 round-trip experience is localdb, the new on-demand local instance that is part of SQL Server 2012. However, localdb does not support full text at this time, so if you have any full text constructs in your VSDB project you will get build errors when you attempt to F5/debug to localdb after converting to an SSDT project.
 |
To work around this issue, you must change your default debug database from localdb to an instance that supports full text constructs. This can be done via the Debug tab of the Project Properties window in SSDT.
VSDB Schema Comparisons
Schema Comparison files that were created using the VSDB tooling may be added to new SSDT projects. Also, .scmp files that are part of a VSDB project will be retained through the conversion process to an SSDT project. There are several cases, however, in which the VSDB .scmp files will not be valid/usable in an SSDT project.
The first case is whenever either the source or the target of the comparison is a standalone .dbschema file. As mentioned in the above Database References section, .dbschema files are the build artifact of VSDB projects – single-file representations of the database schema. However, these .dbschema files are not supported in SSDT projects. Thus trying to utilize a schema comparison that references a .dbschema file, will leave SSDT in an error state. Attempting to execute a Compare in this scenario gives the following error:
If this .scmp file represents an important comparison scenario for your application, it is necessary to obtain a .dacpac representation of the schema contained in the .dbschema file so it can be used in an SSDT project.
 |
The second scenario occurs whenever you have a .scmp file that compares two VSDB database projects. Although these files are not preserved across the conversion process, they can still be added via Add Existing Item. If you add a .scmp of this type into the SSDT project and attempt to open it you will get the following error:
To avoid this error, please make sure that all VSDB projects that you would like to use in a schema comparison are converted to SSDT projects and contained inside the active solution.
NOTE: Any code snippets that are part of this blog are provided “as-is.” Microsoft and/or the author of this post bears no responsibility for what results from the use of the samples contained herein.