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.

Resolving SSRS windows authentication issue, continuous pop up of user authentication dialog and the user never gets authenticated.

Recently there has been a issue where in a test environment, one of my colleagues was having a scaled out setup of SSRS. He had dedicated virtual servers for Database, SSRS and SharePoint (SSRS was installed in SharePoint Integrated mode). After the installation he could connect to the Report Server using the default url within the same server where SSRS was installed. However, from any of the other servers, Database and SharePoint, this was not possible, as when accesses the url, the user authentication dialog continuously pops up and it never gets authenticated even though the user name and the password was confirmed to be correct.

After hours of struggling, it turned out that the issue was with the authentication settings of SSRS in the Reporting Services Config file. The below link can be used as a guide to do this.

http://technet.microsoft.com/en-us/library/cc281253.aspx

SQL Server Data Tools - Resolving self-refereces (referencing local database objects with three part name) issue

I have been allocated to stream line the database solution management of one of the projects we had that consisted of several databases. The primary database was a Navision CRM database, and there was a Data Warehouse project consisted of several staging databases.

In some of the databases, there were database objects created such as views, that references tables from the same database using three part name,

[Current Database Name].[schema].[Local Table Name]

As the first step of the solution management, I had to create SSDT database projects by importing the schema of these databases. However I came across this issue, where you should resolve all the references to objects of different databases. Since three part names were used for local tables, SSDT treats them as external objects, and requires these references to be resolved. After reading a few articles, that explained the only work around was to remove Database Name from the table names used, which is the preferred option, I came across this workaround to resolve these self-references.

In general, to resolve external database references in SSDT database projects, we should add database reference to the project, and change the code to add SQLCMD variable in place of the database name of the three part name. SSDT provides a set of pre-defined SQLCMD variables, such as $(DatabaseName) that gives option to provide current database name.

My workaround was to replace database names of the self-references to $(DatabaseName), so that it would look like below,

[$(DatabaseName)].[schema].[Table Name]

This resolved the self-referencing issue. However, it is always a best practice to avoid three part names for local objects!