CREATE FUNCTION [dbo].[fn_MsBuildTest]
()
RETURNS TABLE AS RETURN
(
select ID
from CHANGETABLE(changes dbo.TBL1_DTL , 1) as CT
)
It basically selects IDs of dbo.TBL1_DTL table from the CHANGETABLE() function.
When I built the project within visual studio, it built without any errors or warnings. However the issue came up with the project was built using TFS build. The build failed with the following error,
"F:\Documents\Personal\Projs\DB\RecoverTest\RecoverTest\RecoverTest.sqlproj" (r
ebuild target) (1) ->
(SqlBuild target) ->
F:\Documents\Personal\Projs\DB\RecoverTest\RecoverTest\dbo\Functions\fn_MsBui
ldTest.sql(5,9,5,9): Build error SQL71501: Computed Column: [dbo].[fn_MsBuildTe
st].[ID] has an unresolved reference to object [ID]. [F:\Documents\Personal\Pro
js\DB\RecoverTest\RecoverTest\RecoverTest.sqlproj]
It cannot identify the column ID and throws a unresolved reference error.
After digging we found out that the issue is with MSBuild. When the project was built locally, on the same computer where I successfully built the project using visual studio 2012, using MSBuild utility, the same error was thrown. So it turns out that MSBuild has a limitations building inline functions with CHANGETABLE() function used in the select statement.
To verify that, I changed the function to a multi-lined table valued functions, and here it goes. MSBuild successfully built it. Below is how it was after it has been changed to multi-lined table valued function.
CREATE FUNCTION [dbo].[fn_MsBuildTest]
()
RETURNS @changes TABLE(ID bigint)
AS
BEGIN
insert @changes
select ID
from CHANGETABLE(changes dbo.TBL1_DTL , 1) as CT
return
END
According to this experience, it turns out that if CHANGETABLE can be used in a multi-lined table valued function, but not inside a inline table valued function.
When I built the project within visual studio, it built without any errors or warnings. However the issue came up with the project was built using TFS build. The build failed with the following error,
"F:\Documents\Personal\Projs\DB\RecoverTest\RecoverTest\RecoverTest.sqlproj" (r
ebuild target) (1) ->
(SqlBuild target) ->
F:\Documents\Personal\Projs\DB\RecoverTest\RecoverTest\dbo\Functions\fn_MsBui
ldTest.sql(5,9,5,9): Build error SQL71501: Computed Column: [dbo].[fn_MsBuildTe
st].[ID] has an unresolved reference to object [ID]. [F:\Documents\Personal\Pro
js\DB\RecoverTest\RecoverTest\RecoverTest.sqlproj]
It cannot identify the column ID and throws a unresolved reference error.
After digging we found out that the issue is with MSBuild. When the project was built locally, on the same computer where I successfully built the project using visual studio 2012, using MSBuild utility, the same error was thrown. So it turns out that MSBuild has a limitations building inline functions with CHANGETABLE() function used in the select statement.
To verify that, I changed the function to a multi-lined table valued functions, and here it goes. MSBuild successfully built it. Below is how it was after it has been changed to multi-lined table valued function.
CREATE FUNCTION [dbo].[fn_MsBuildTest]
()
RETURNS @changes TABLE(ID bigint)
AS
BEGIN
insert @changes
select ID
from CHANGETABLE(changes dbo.TBL1_DTL , 1) as CT
return
END
1 comment:
Supun-
I realize this is an older post, but this is one of the few references I can find using CHANGETABLE() within a Visual Studio database project. Do you remember how you got the solution to successfully build in VS in the first place? I have database references added for master and msdb, but upon build I get '..contains an unresolved reference to an object...' errors for any functions with CHANGETABLE() included.
Thanks,
Chris
Post a Comment