2

We have a single table-value function that causes the dacpac to fail when created with Error SQL71561 (unresolved reference). Like many of the other functions in the same database, this one references a table in another database on the same server. Unlike the other functions, this one consistently causes the dacpac to fail.

I’ve tried using 4-part names to reference the table, and I’ve also created a synonym. Neither seem to work, and I’m now at a complete loss. I can’t finding enough information online, and I don’t get enough feedback from SQL to properly troubleshoot the error. Can someone help point me in the right direction?

Details below:

We have a single instance of SQL Server 2014 enterprise that contains 3 separate databases named [dbParks], [dbLandmarks], and [dbPeople]. The server name is [sCity2].

Inside the [dbParks] database is a table-value function [fn_GetEmployeeList] that contains a reference to [dbPeople].[dbo].[Employees]. This function consistently errors with:

Error SQL71561: Error validating element [dbo].[fn_GetEmployeeList]: Function: [dbo].[fn_GetEmployeeList] 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: [sCity2].[dbPeople].[dbo].[Employees].[a] or [sCity2].[dbPeople].[dbo].[Employees].[EmployeeCode].

I updated the function (below) to use a 4-part name instead of the 3-part name when referencing the table, and it still failed:

CREATE FUNCTION [dbParks].[dbo].[fn_GetEmployeeList] ( )
 RETURNS TABLE
 AS 
 RETURN (
         SELECT 
              a.EmployeeCode,
              a.MunicipleCode,
              a.FullName,
              a.Supervisor
          FROM  
              [sCity2].[dbPeople].[dbo].[Employees] a
         )
 GO

I then created a synonym that references [dbPeople].[dbo].[Employees] named syn_Employees, which also causes the dacpac to fail during creation.

CREATE FUNCTION [dbParks].[dbo].[fn_GetEmployeeList] ( )
 RETURNS TABLE
 AS 
 RETURN (
         SELECT 
              a.EmployeeCode,
              a.MunicipleCode,
              a.FullName,
              a.Supervisor
          FROM  
              [dbo].[syn_Employees] a
         )
 GO

What’s really confusing is that there are other functions in the same database that use synonyms and 3-part name references that don’t cause the dacpac to fail. Also, when I remove the [fn_GetEmployeeList] the dacpac is created successfully.

Any help is truly appreciated

Md. Zakir Hossain
  • 1,082
  • 11
  • 24
acatala
  • 56
  • 1
  • 6
  • have a look https://stackoverflow.com/questions/12161392/using-ssdt-how-do-i-resolve-sql71561-errors-when-i-have-a-view-that-references – Md. Zakir Hossain Sep 26 '18 at 17:31
  • The problem is similar, but not exact. I'm trying to create the dacpac from SSMS not VS SSDT. Because of this there's no way to add database references, or perform any of the steps mentioned in the answer to their problem. FWIW - I did try to use VS SSDT to create a dacpac of the [dbParks] database, but It failed during validation. And when I disabled validation the dacpac was created successfully, but I was unable to import it as a database reference. – acatala Sep 26 '18 at 17:57
  • 2
    https://stackoverflow.com/questions/26758512/how-to-extract-mssqlserver-database-as-dacpac-without-verifyextraction check the 2nd answer, how to extract dacpac with sqlpackage. SSMS will not work in your case. – Dmitrij Kultasev Sep 28 '18 at 06:22
  • @DmitrijKultasev - you should add that comment as an answer, it was key in helping me extract the dacpac data. Thanks. – Marcos Dimitrio May 14 '21 at 22:30
  • 2
    Does this answer your question? [How to extract MSSQLServer database as .dacpac without VerifyExtraction?](https://stackoverflow.com/questions/26758512/how-to-extract-mssqlserver-database-as-dacpac-without-verifyextraction) – Dmitrij Kultasev May 16 '21 at 09:39
  • @MarcosDimitrio it's not my answer so I don't think that I should do that. I voted to close this answer as duplicate instead. – Dmitrij Kultasev May 16 '21 at 09:40

0 Answers0