Blog

Currently, SSDT (SQL Server Data Tools) in Visual Studio doesn’t accept self referencing three part names. That is, objects qualified as DatabaseName.Shema.Object, where the database is the same as where the object resides.

A build will generate an error similar to this one:

[red_box]Error: SQL71561: View: [dbo].[VIEW1] has an unresolved reference to object [SSDT1].[dbo].[TBL1].[/red_box]

As a result, the solution won’t build as long as there are unsolved errors of this kind. Three part names in stored procedures only results in a warning for some reason, and won’t stop a build:

[yellow_box]Warning: SQL71562: Procedure: [dbo].[PROC1] has an unresolved reference to object [SSDT1].[dbo].[TBL1].[/yellow_box]

As a simple demonstration, create a simple database with the following script:

CREATE DATABASE SSDT1
GO

USE SSDT1
GO

CREATE TABLE dbo.TBL1
	(
		COL1 INT IDENTITY(1,1) NOT NULL
		,COL2 VARCHAR(10) NOT NULL
	)
GO

INSERT dbo.TBL1 (COL2) VALUES ('TestVal')
GO

--PROCEDURE
CREATE PROCEDURE dbo.PROC1

AS

SELECT * FROM SSDT1.dbo.TBL1
GO

--VIEW
CREATE VIEW dbo.VIEW1

AS

SELECT * FROM SSDT1.dbo.TBL1
GO

There is one table, one procedure and one view. Both the procedure and the view use a self referencing database name (SSDT1.dbo.TBL1) in the select statement.

If this database is imported into a SSDT project, a build will report an error in the view:

The obvious way to get rid of the error is of course to manually remove the database name from the select statements in all objects. But if there are lots of objects, this can be very time consuming, not to mention introducing the risk of creating unintentional errors while manually editing the code.

Another way to handle this is to use the classical “Edit” -> “Search and Replace” -> “Replace in Files” function:

Be very careful when you use this function however, so that valid references aren’t removed. References to other external (not self referencing) databases must not be removed.