SSDT and three part names

Microsoft SQL Server

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:

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

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:

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

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:

SSDT Error 71561

SSDT Error 71561

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:

Visual Studio Find And Replace

Visual Studio Find And Replace

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.

1 comment » Write a comment

  1. Hello 🙂

    i ran into the same problem trying to create a dacpac in SSMS. eventually this solution was not suitable for us since we use three part names that reference other databases on the same server and some even on other servers.

    db1.dbo.table
    db2.dbo.table
    server2.db3.dbo.table

    we tried to resolve some of the self referencing names but eventually got stuck on the server referencing names, so we decided to do it in Visual Studio with the SQL Server Explorer.

    this worked because by default the SQL Server explorer in Visual Studio does not perform the verify step, so you can create a dacpac without problems.

Leave a Reply

Required fields are marked *.