question

PranavK-2959 avatar image
0 Votes"
PranavK-2959 asked pituach edited

Error SQL71501: Error validating element on a stored procedure

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered PranavK-2959 edited

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




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for reverting back to me, yes we tried by adding an alias too but ended up in a changed error



Error SQL71501: Error validating element [dbo].[MySP]: Procedure: [dbo].[MySP]
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: [dbo].[myfunction].[Cost] or [dbo].[myfunction].[MyTVF].

Error SQL71501: Error validating element [dbo].[MySP]: Procedure: [dbo].[MySP]
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: [dbo].[myfunction].[PID] or [dbo].[myfunction].[MyTVF].



we are using SSMS version 18.5 and SQL Server 2014

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 commented

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://docs.microsoft.com/en-us/sql/tools/sqlpackage/release-notes-sqlpackage?view=sql-server-ver15

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your response, are you recommending us to use sqlpackage.exe instead of Extract DataTier Wizard from SSMS ?

Also I have seen a lot of posts suggesting to use SSDT from Visual Studio to generate the dacpac with verify extraction flag unchecked.

0 Votes 0 ·

The DataTier Wizard uses the same API as SQLPackage.exe. They will both likely generate the same results.

Using Extract DataTier is normally just a way to get the data into an SSDT project the first time. It is not normally used repeatedly.

1 Vote 1 ·