While working on setting up a database project by importing an existing database, I ran into the issue that some of the database objects referenced tables in a different database. I imported that database as a separate project, and set up a reference from my first project to the second project, which ideally should have done the trick.
Except it didn't, and the "Unresolved reference to object" error kept showing.
One way to resolve the error is to replace all references to the database with the variable Visual Studio creates. If you were to open the .proj file with your favorite text editor (I like Notepad++), you will find something like the following near the bottom of the project file:
The variable in this case would have the value of [$(MyDatabase)], and you could replace all instances of MyDatabase.dbo.MyTable with [$(MyDatabase)].dbo.MyTable.
However, that's quite annoying, and (in my humble opinion) not a very clean solution.
Luckily, there's a better solution. I also happened to add a reference to the master system database, in order to suppress the "unresolved reference to object" errors related to objects in the sys schema.
While inspecting the project, I noticed that had a different XML signature (emphasis mine):
The DatabaseVariableLiteralValue does not seem to exist in a ProjectReference. But let's try and add it, by opening the project file in Notepad++ and simply adding the following line:
So the new code for the ProjectReference looks like the following (I cleaned up the VS generated variable):
Save the .proj file, and open the solution in Visual Studio again, and if all went well, your code will no longer show the dreaded "unresolved reference" error.
Except it didn't, and the "Unresolved reference to object" error kept showing.
One way to resolve the error is to replace all references to the database with the variable Visual Studio creates. If you were to open the .proj file with your favorite text editor (I like Notepad++), you will find something like the following near the bottom of the project file:
XML:
<ProjectReference Include="..\MyDatabase\MyDatabase.sqlproj">
<Name>MyDatabase</Name>
<Project>{<GUID>}</Project>
<Private>True</Private>
<SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
<DatabaseSqlCmdVariable>MyDatabase</DatabaseSqlCmdVariable>
</ProjectReference>
...
<SqlCmdVariable Include="MyDatabase">
<DefaultValue>MyDatabase</DefaultValue>
<Value>$(SqlCmdVar__1)</Value>
</SqlCmdVariable>The variable in this case would have the value of [$(MyDatabase)], and you could replace all instances of MyDatabase.dbo.MyTable with [$(MyDatabase)].dbo.MyTable.
However, that's quite annoying, and (in my humble opinion) not a very clean solution.
Luckily, there's a better solution. I also happened to add a reference to the master system database, in order to suppress the "unresolved reference to object" errors related to objects in the sys schema.
While inspecting the project, I noticed that had a different XML signature (emphasis mine):
XML:
<ArtifactReference Include="$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\master.dacpac">
<HintPath>$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\master.dacpac</HintPath>
<SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
[B]<DatabaseVariableLiteralValue>master</DatabaseVariableLiteralValue>[/B]
</ArtifactReference>The DatabaseVariableLiteralValue does not seem to exist in a ProjectReference. But let's try and add it, by opening the project file in Notepad++ and simply adding the following line:
XML:
<DatabaseVariableLiteralValue>MyDatabase</DatabaseVariableLiteralValue>So the new code for the ProjectReference looks like the following (I cleaned up the VS generated variable):
XML:
<ProjectReference Include="..\MyDatabase\MyDatabase.sqlproj">
<Name>MyDatabase</Name>
<Project>{<GUID>}</Project>
<Private>True</Private>
<SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
<DatabaseVariableLiteralValue>MyDatabase</DatabaseVariableLiteralValue>
</ProjectReference>Save the .proj file, and open the solution in Visual Studio again, and if all went well, your code will no longer show the dreaded "unresolved reference" error.