Alright! You are the hero of your database team! You’ve received the okay to start using SQL Server Data Tools to manage one of your existing databases. You think to yourself: “Within a few minutes I’ll have the project up and running and I can move on to phase 2 of my plan to save database land from all that is evil…” You quickly run through the following steps:
- Perform a schema compare using a copy of production as the source and a the existing project as the target.
- Blindly, commit all the changes to the project based on the assumption that whatever’s in prod will be better than the old project in source control (which in my case hadn’t been updated in over a year.)
- Rebuild the solution by right clicking on the solution name in the solution explorer.
- Surprise! 201 errors. This is the max allowed by Visual Studio. The next step? Start grinding through them.
The first error looked something like this:
Error 1 SQL71561: Computed Column: [MySchema].[MyTable].[id] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [MyDatabase2].[MyDatabase2Schema].[MyDatabase2TableA].[t]::[id],[MyDatabase2Schema].[MyDatabase2TableB].[t]::[id], [MyDatabase2Schema].[MyDatabase2TableC].[t].[id], [MyDatabase2Schema].[MyDatabase2TableC].[t]::[id] or [MyDatabase2Schema].[MyDatabase2TableD].[t]::[id]. C:\1-Phil\tfs\BI\SQL\Database1\Database1\Schema Objects\Schemas\MySchema\Views\MyView.view.sql 3 14 MyDatabase
It’s a real mess of an error message but if you double click on the message VS 2012 will open up the offending file for you and what you’ll likely see is at least one or more three part names being used in the FROM of the script. Basically, SSDT is telling you it can’t build this script because it is referencing an object which doesn’t exist in the project.
As a quick way to resolve this type of problem I decided to try a trick I had used back with visual studio 2010 where I would generate a .dbschema file for the database named in the three part name using a command line executable. The dbschema would be generated (ignoring any errors from the source database) and could then be set up as a database reference in the project and all the unresolved references for that database would be magically cleaned up.
In Visual Studio 201o the executable was vsdbcmd.exe. The first hurdle? As of Visual Studio 2012 vsdbcmd.exe no longer exists. (Here’s a little writeup I did on that surprise.) Luckily, the replacement sqlpackage.exe looked to have the functionality I needed.
- Open the windows command prompt (cmd.exe)
- Set the directory to the location of sqlpackage.exe by typing: “cd C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin”
- run the sqlpackage.exe with the following settings: “sqlpackage.exe /Action:Extract /SourceConnectionString:Server=MyServer;Database=MyDatabase2; /TargetFile:C:\MyDatabase2.dacpac” Note: you’ll need VIEW DEFINITION perms on the database you’re attempting to connect to.
- If everything goes well you should see the following message generated:Connecting to database ‘MyDatabase2’ on server ‘MyServer’.
Extracting schema from database
Resolving references in schema model
Successfully extracted database and saved it to file ‘C:\MyDatabase2.dacpac’.
- Take a look at your C:\ drive and you should have the new .dacpac file.
Now that we have the .dacpac file for the database included in the three part name we just need to add it to the current solution.
- Right click on “References” in Solution Explorer and select “Database Reference”
- Select Data-Tier Application (.dacpac)
- Find the dacpac file you created in the previous section.
- Enter the database name of the external DB. In my example “MyDatabase2”. Make sure the database variable field is blank. If you do not the reference will only resolve to for files using the database variable.
- Rebuild the project and be amazed that your error count is dropping. In my case adding two dacpac database references dropped my error count down to 105.
The next set of errors I ran into was unresolved references to the database I was actually updating. To be more specific, there were views which were using a three part name referring to the database they are actually in. Although, SQL Server allows this, SSDT does not. To clean this up I went with a very careful global search and replace.
- Select “Find and Replace” from the Visual Studio “Edit” menu.
- Select “Replace In Files”
- In the find what enter the first section of the three part name including brackets. (e.g. [MyDatabase].) Include the period after the schema name as a way to make the replace as specific as possible.
- Set the replace with to just the “.”
- Set the “Look in” field to “Current Project”.
- Click “Replace All”.
Ta-da! In my case I am now down to 2 Errors!
The final error, was still of the “Contains an unresolved reference” type but was a little trickier to workout. Notice that the error number is now SQL71501:
Error 195 SQL71501: Procedure: [MySchema].[MyProc] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [MySchema].[TableA].[ColumnX], [MySchema].[TableB].[ColumnX], [MySchema].[TableC].[ColumnX], [MySchema].[TableD].[ColumnX], [MySchema].[TableE].[ColumnX], [MySchema].[TableF].[ColumnX] or [MySchema].[TableG].[ColumnX]. C:\1-Phil\tfs\BI\SQL\MyDatabase\MyDatabase\Schema Objects\Schemas\MySchema\Programmability\Stored Procedures\MyProc.proc.sql 99 6 MyDatabase
In my case, when I double clicked the file and opened it I found that one of the references to ColumnX was not using the two part name and thus SSDT was unable to determine which table it belonged to and furthermore whether the column existed in the table. Once I added the two part name. Bingo! I was down to no errors!