Data Dude Error TSD03006 – Explicit Database Reference

Problem

You’ve just created a new Data Dude project and imported your DB schema, or you’ve just synchronized schemas with an existing project having added several new views / UDFs / SPROCs etc. When you try to build your project you find that there are tons of TSD03006 errors that are stopping you. Why?

TSD03006: View: [dbo].[vFactResellerSales] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[DimProduct].[s]::[ProductKey], [dbo].[FactResellerSales].[ProductKey] or [dbo].[FactResellerSales].[s]::[ProductKey]

Solution

Ensure any explicit database name references do not exist in your code. If you need to explicitly reference a database do it by using variables.

Removing the explicit DB references from the code should make the TDS03006 errors disappear. Admittedly you should be cleaning up any explicit DB references within your code, but when whipping up a new script in the early hours of the morning this may be something that you’d miss. And suddenly getting a whole heap of build-blocking errors at 3am can be incredibly annoying. 

Sometimes explicit DB references can creep into code without you catching it. For example when creating views from large tables I often start by scripting out a SELECT statement within SSMS. The code that is generated by SSMS includes an explicit reference to the database.

SELECT [ProductKey]
       ,[OrderDateKey]
       ,[DueDateKey]
       ,[ShipDateKey]
       ,[ResellerKey]
       -- Shortened for brevity
       ,[CustomerPONumber]
FROM   [AdventureWorksDW2008].[dbo].[FactResellerSales]

It’s the explicit [AdventureWorksDW2008] reference that causes the TSD03006 errors. If you add to the SSMS generated code and join other tables but keep even one explicit DB reference you will continue to receive TSD03006 errors for each field in the view definition.

Data Dude is indeed bringing a valid problem to our attention but I think there should be a more elegant way to communicate the situation. One view containing 37 fields with 1 explicit DB reference returns 37 errors. In the end the fix is easy, but figuring out the fix takes more time than it should based on the content and volume of the error messages.

BTW make sure that you’re using Data Dude GDR R2 version 9.1.40413.00. I found an MS support document that detailed a similar issue pertaining to using Server and Database aliases in referenced projects that is fixed in this release. This issue is similar but different in that it’s not a direct problem with aliases or referenced projects, just careless coding on my part.