Referencing another database in SSDT – error SQL71561

If you have SQL Server database project in Visual Studio that needs to reference another database, ie a View containing a 3 level reference like [OEDB].[dbo].[Inventory], you need to reference the other database by right-clicking “References” and choosing “Add Database Reference”.

This brings up this screen:

image

Near the bottom is something called “Database variable”. If this is filled (which is the default) all references to that database in the imported SQL must go via the variable, as can be seen from the example just below [$(oedb)].[Schema1].[Table1] the “Database variable” field.

If you fail to correct occurrences in your SQL from [DatabaseName] to [$(DatabaseName)] you will probably experience an error list with hundreds of SQL71561 errors like this:

SQL71561: View: [dbo].[qryCreateYMD] 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: [OEDB].[dbo].[Inventory].[Id], [OEDB].[dbo].[Inventory].[OEDB]::[dbo].[Inventory].[Id] or [OEDB].[dbo].[Inventory].[OEDB]::[dbo].[Inventory].[Id]

In my mind this wasn’t really the expected behavior, but what you can do to just use the references you have without changing your views, is to simply clear out the “Database variable” field:

image

As can be seen from the Example usage, references wil now work as expected…

This is basically what is explained here (when I finally found the explanation), but I wanted to add the screenshots of how this works.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s