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
USE sourcedb
GO
CREATE TABLE Sourcetbl (
id INT
,NAMES VARCHAR(100)
)
GO
INSERT INTO Sourcetbl (
id
,NAMES
)
SELECT 1
,'Jayanth'
GO
CREATE TABLE Targettbl (
id INT
,NAMES VARCHAR(100)
)
GO
USE taregtdb
GO
CREATE PROCEDURE insDataFromSrctoTgt
AS
BEGIN
INSERT INTO Targettbl (
id
,NAMES
)
SELECT b.id , b.names
FROM Targettbl a
Inner Join sourcedb.dbo.Sourcetbl b
ON a.id != b.id
END
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.
CREATE PROCEDURE [dbo].[GetDataFromTarget]
AS
SELECT * from Targetdb.dbo.TargetDb
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.
--- CODE TO FIND CIRCULAR REFERENCES BETWEEN 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'
)
)
select 'select '+char(39)+aa.name+char(39)+' as SrcDB , '+char(39)+bb.TgtName+char(39)+' as TgtDb ,
SCHEMA_NAME(o.schema_id) as SchemaName , o.name as ObjectName
from '+aa.Name+'.sys.sql_modules a
inner join '+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 (select name as TgtName from cte b where aa.Name != b.name ) bb
order by 1