Error SQL71501: Error validating element on a stored procedure

PK_MS 1 Reputation point
2021-01-28T11:49:43.893+00:00

We have a stored procedure which calls a table valued function like this

DECLARE @PID INT
DECLARE @PCost DECIMAL(30,15)

SELECT @ID = PID, @PCost = Cost 
FROM [dbo].[myfunction] (@param1, @param2)

but when we try to generate a .dacpac from SSMS, we get this error

Error SQL71501: Error validating element [dbo].[MySP]: Procedure: [dbo].[MySP] has an unresolved reference to object [dbo].[myfunction].[Cost].

Error SQL71501: Error validating element [dbo].[MySP]: Procedure: [dbo].[MySP] has an unresolved reference to object [dbo].[myfunction].[PID].

Our table valued function looks like this

ALTER FUNCTION [dbo].[myfunction] 
    (@param1 INT NULL,
     @param2 INT NULL)
RETURNS @temptable TABLE (PID INT, Cost DECIMAL(15,5))
AS 
BEGIN
    INSERT INTO @temptable
        // some select statements
    RETURN
END

The stored procedure works fine without any issues but the error is only when trying to extract datatier .dacpac from SSMS. Does anybody have any idea what's going on in here ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,481 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,535 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Olaf Helper 40,156 Reputation points
    2021-01-28T14:04:05.997+00:00

    Which SSMS version are you using?

    If you use a TVF in a SELECT, then you should always use a alias to get robust SQL code; try it with

     DECLARE @PID INT
     DECLARE @PCost DECIMAL(30,15)
    
     SELECT @ID = MyTVF.PID, @PCost = MyTVF.Cost 
     FROM [dbo].[myfunction] (@param1, @param2) AS MyTVF
    

  2. Tom Phillips 17,716 Reputation points
    2021-01-28T15:19:59.643+00:00

    Your problem is a dependency issue. The dacpac is deploying the proc before the function. It does not hurt anything. In some edge cases, the dacpac cannot determine the correct dependency order.

    Make sure you are using the most current SQLPackage.exe.
    https://learn.microsoft.com/en-us/sql/tools/sqlpackage/release-notes-sqlpackage?view=sql-server-ver15