question

erlokeshsharma08 avatar image
erlokeshsharma08 asked

Unresolved reference in function

Hi I have been struggling with this since yesterday. I need to generate a dacpac file from a database. This database (db1) has objects that refer to some other database (db2). db1 Objects consist of functions, stored proc and tables. So I created a database project, imported a script file and generated a solution that has 1 function, 16 stored procs and 2 tables. I declared a sqlcmd variable $(db1) for database db1. Replaced the reference of db1 inside objects to [$(db1)]. oN BUILDING function errors out with the following message: >SQL71561: Function: [dbo].[ufn_GetFAID] has an unresolved reference to object [$(AIRDB)].[App].[ufn_Items]. STORED PROCS AND TABLES DOES NOT GIVE ME THIS ERROR Here is the script of the function: -- ============================================= -- Author: Lokesh Sharma -- Create date: 11/01/2014 -- Description: THIS FUNCTION IS USED TO GET THE FIRST ASSOCIATED ITEM PER TRANSACTION FOR A GIVEN BUSINESSDATE AND TRANCODE. -- ============================================= CREATE FUNCTION [dbo].[ufn_GetFAID] (@Businessdate smalldatetime, @trancode varchar(2) =null) RETURNS TABLE AS RETURN ( SELECT CASE WHEN TRANCODE IS NULL THEN NULL ELSE ITEMNUMBER END AS ITEMNUMBER, BUSINESSDATE, RN, CASE WHEN TRANCODE IS NULL THEN NULL ELSE TRANCODE END AS TRANCODE , LINEID FROM (SELECT I.BUSINESSDATE,ITEMNUMBER,TRANCODE,IL.LINEID, ROW_NUMBER() OVER ( PARTITION BY I.TRANCODE ORDER BY I.LINEID) AS RN FROM [$(AIRDB)].App.ufn_Items(@BUSINESSDATE,NULL) I INNER JOIN [$(AIRDB)].APP.UFN_ITEMLINES(@BUSINESSDATE,NULL) IL ON I.LINEID=IL.LINEID AND I.BUSINESSDATE=IL.BUSINESSDATE ) TT WHERE BUSINESSDATE=@BUSINESSDATE AND TRANCODE=@TRANCODE AND RN=1 ) GO GRANT SELECT ON [dbo].[ufn_GetFAID] TO [DatabaseProc] AS [dbo] GO EXEC sys.sp_addextendedproperty @name=N'Component', @value=N'CROSS CLIENT - AIR WEB' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ufn_GetFAID' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This procedure is used to fetch the first associated itemid per trasnaction' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ufn_GetFAID' GO EXEC sys.sp_addextendedproperty @name=N'Version ', @value=N'1.0.0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ufn_GetFAID'
sql-server-2014ssdtdatabase-project
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
Have you added db2 as a Database Reference http://msdn.microsoft.com/en-us/library/jj684584(v=vs.103).aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.