Wednesday, March 12, 2014

SSDT 2012 Database Project Limitation with Change Tracking - using CHANGETABLE in inline table valued function

I recently worked on a SQL Server Data Tools (SSDT) 2012 database project that had Change Tracking enabled. There were a lot of stored procedures and functions written using the CHANGETABLE() function to retrieve changes. Also, there were a few inline table valued functions written to select records from CHANGETABLE(), that would look like something below,

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.


No comments: