We have several databases, say DB1, DB2, DB3 etc.
They have to have identical code base, so we use a DB project in Visual Studio 2012 and generate a SQL script for deployment based on comparison between the project and UAT/Prod DB1. Then this script is applied to DB1-DBn.
For the very first time in the history of this DB project I had to create a function that contained a hardcoded database name, example:
inner join DB1.schema.table1 as t1 on
And now the project cannot be built or comparison cannot be updated or script generated (Update and Generate Script buttons disabled) due to a number of errors pertaining to that database reference, as VS seems to believe that DB1 does not exist.
I tried to add a project level SQLCMD variable $(DB) and set it to DB1 default value and use it as
inner join [$(DB1)].schema.table1 as t1 on
to work around the errors, but it did not seem to make any difference.
Edit:
A suggestion was made to add a circular project reference to itself and assign to it the same variable I was trying to add manually, not sure how to accomplish that.
As per this article the reference should be added to a manually extracted .dacpac file as follows:
Extracted .dacpac file from the targed DB with the following command:
"C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe" /SourcePassword:p /SourceUser:u /Action:Extract /ssn:192.168.2.1 /sdn:DB1 /tf:DB1.dacpac
Included that as a database reference. It automatically assigned the correct SQLCMD variable name and the error disappeared.
From the source control point, even though when adding a database reference to a .dacpac file automatically creates a SQLCMD variable, it does not add the file to the project. The .dacpac file used still has to be added to the project as an existing item, which is kind of lame. Doing that in the solution explorer I encountered an error and had to do that through the team explorer instead, where that worked.