Warning: SQL71562: Procedure: [dbo].[Someproc] has an unresolved reference to object [Targetdb].[dbo].[TargetDb].

Recently I came across an issue I have faced multiple time before but unlike previous times I was not able to fix the issue., the problem was that I had a circular reference between the dependent objects and this resulted in the first database not building because of errors in the second and vice versa. This issue is a problem because when working in Azure we can only use DACPAC as the option to import and export the database from on Prem to the cloud. If DAC fails then essentially you are left with no options to import database to Azure than to manually script and deploy objects.

Before getting into the actual issue, I thought for those not familiar with the issue I would reproduce the issue using the below script. There are two databases below – sourcedb and targetdb. Both Source and target have a table of similar structure. The goal is to use a procedure on Targetdb to import data from source db. Normally we could use an SSIS package but in this case, we opted for stored procedure since the database a going to be hosted on the same database server.

CREATE DATABASE sourcedb;
GO

CREATE DATABASE taregtdb;
GO

-- Create and populate source table
USE sourcedb;
GO

IF OBJECT_ID('dbo.Sourcetbl', 'U') IS NOT NULL DROP TABLE dbo.Sourcetbl;
CREATE TABLE dbo.Sourcetbl (
    id INT PRIMARY KEY,
    NAMES VARCHAR(100)
);
GO

INSERT INTO dbo.Sourcetbl (id, NAMES)
VALUES (1, 'Jayanth');
GO

-- Create target table
IF OBJECT_ID('dbo.Targettbl', 'U') IS NOT NULL DROP TABLE dbo.Targettbl;
CREATE TABLE dbo.Targettbl (
    id INT PRIMARY KEY,
    NAMES VARCHAR(100)
);
GO

-- Switch to target database and create the procedure
USE taregtdb;
GO

IF OBJECT_ID('dbo.Targettbl', 'U') IS NOT NULL DROP TABLE dbo.Targettbl;
CREATE TABLE dbo.Targettbl (
    id INT PRIMARY KEY,
    NAMES VARCHAR(100)
);
GO

-- Procedure to insert data from source to target where IDs do not match
IF OBJECT_ID('dbo.insDataFromSrctoTgt', 'P') IS NOT NULL DROP PROCEDURE dbo.insDataFromSrctoTgt;
GO

CREATE PROCEDURE dbo.insDataFromSrctoTgt
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.Targettbl (id, NAMES)
    SELECT b.id, b.NAMES
    FROM sourcedb.dbo.Sourcetbl b
    WHERE NOT EXISTS (
        SELECT 1 FROM dbo.Targettbl a WHERE a.id = b.id
    );
END
GO

With a above script ready and deployed on the database server we can now import the schema in Visual Studio database project for checking in to Git etc. However, when we try to build the projects, we get the below errors.

Luckily for us the error message is a common one that we see in SSMS and tells us that it because visual studio is having trouble identifying the database called SourceDB. Since there is no reference for the same in the project called TargetDB. SQL Server is actually quite smart in this case and so knows to look into the master database and identify any other database with the 3-part name and then look into the catalog of that database to identify the correct table and column. Unfortunately, Visual Studio needs some help – simply add a reference to the Database project explain where it can find the appropriate object definitions within the solution.

STEP 1

Since the TargetDB needs a reference to the source database we simply right click the “References” Tree Item in the solution explorer

STEP 2

In the Add Database Reference screen we select the project for the source database. As shown below.

Pay close attention to the Database Variable option, it shows the value that needs to be used within the code when referencing the Database called SourceDB. This is ideal when creating a database from scratch. However, in most cases we already have the 3-part name hardcoded in the procedure and now it’s impossible to change the name across hundreds of procedures. In such cases we can simply erase the text in the field Database Variable in the above window as shown below.

This results in the text as shown in the example usage screen, allowing us to use the database name as it is without resorting to a variable.

STEP 3

In some cases, we may want to use a system database such as master or msdb. In such cases you can simply use the option system database.

In the above scenario there is no need to add any variables etc. as you can see the option is disabled by default. After making the above changes we can see the error from earlier is now resolved.

This is how things are supposed to work ideally. However, in the project I referred to earlier in the post, we have a circular dependency. Sticking to the above code I can now add a procedure in SourceDB which will read off the TargetDB and perform some operation.

IF OBJECT_ID('dbo.GetDataFromTarget', 'P') IS NOT NULL DROP PROCEDURE dbo.GetDataFromTarget;
GO

CREATE PROCEDURE dbo.GetDataFromTarget
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM taregtdb.dbo.Targettbl;
END
GO

The moment that new procedure is added to the project you can see a build will return the below error.

Writing model to C:\Temp\ForDelete\BuildErrors\SourceDB\obj\Debug\Model.xml… C:\Temp\ForDelete\BuildErrors\SourceDB\dbo\GetDataFromTarget.sql(4,16): Warning: SQL71562: Procedure: [dbo].[GetDataFromTarget] has an unresolved reference to object [Targetdb].[dbo].[TargetDb].

Upon seeing the above build error, we would assume the answer would be to simply add a reference to the TargetDB in the SourceDB project as before. Upon doing this we will be faced with the below pop up.

If there is a single procedure or just a few that you can manually check and verify then the easiest way to handle the issue is to use the option to set the build option to None instead of build.

This allows you to by pass the problem procedure during the build process and therefore the build will succeed. However, when you have multiple procedures, this isn’t a practical option. Which leaves me in the situation where the only way for me fix the issue is to create a copy of the code database and rename it as SourceDB_2 or something and then use this database a reference instead of the SourceDB. Technically this will work because the database name is different it is treated as a different database and so even if the objects are the same it wont matter. Except I have no choice but the manually edit each and every hardCoded 3 part name within the database to refer to the database called SourceDB_2.

-- CTE to get a list of user databases, excluding system and specific databases
WITH cte AS (
    SELECT 
        name, 
        ROW_NUMBER() OVER (ORDER BY name ASC) AS Rid  
    FROM sys.databases
    WHERE name NOT IN (
        'master',
        'tempdb',
        'model',
        'msdb',
        'test',
        'SSISDB',
        'distribution',
        'DWDiagnostics',
        'DWConfiguration',
        'DWQueue'
    )
)
-- Generate dynamic SQL to find references to other databases in object definitions
SELECT 
    'SELECT ' 
        + QUOTENAME(aa.name, '''') + ' AS SrcDB, ' 
        + QUOTENAME(bb.TgtName, '''') + ' AS TgtDb, ' 
        + 'SCHEMA_NAME(o.schema_id) AS SchemaName, o.name AS ObjectName ' 
        + 'FROM ' + QUOTENAME(aa.Name) + '.sys.sql_modules a '
        + 'INNER JOIN ' + QUOTENAME(aa.Name) + '.sys.objects o ON a.object_id = o.object_id '
        + 'WHERE a.definition LIKE ''%' + bb.TgtName + '%'' '
    + 'UNION ALL'
FROM cte aa
CROSS APPLY (
    -- For each source database, get all other target databases
    SELECT name AS TgtName FROM cte b WHERE aa.Name != b.name
) bb
ORDER BY 1;