Sunday, February 02, 2014

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!

No comments: