Self Referencing

Lately we have been getting questions about self-referencing database calls. What I mean with that is that you are referencing SQL objects using a 3-part name while referencing objects that reside inside your current database context.

For example:

    1: use Northwind

    2: go

    3:  

    4: SELECT * FROM Northwind.dbo.Categories

    5: go

Just to be clear, the use of self-referencing database calls does not add any value or functionality. In my opinion it only make the code more verbose and harder to maintain since you are dealing with literal references that you will need to update when you need to deploy to a different database name, which is why this technique was never promoted as a best never promoted as a best practice.

But based on the question we are receiving it seems that there are people that use this technique and want to use this in combination with DBPro.

Lets see want happens by creating a simple project that I will name NW which imports the Northwind database schema, after I have created the project I will add two objects, a stored procedure and a view which will both reference the NW database directly.

First we add the self referencing stored procedure, which looks like this:

    1: CREATE PROCEDURE [dbo].[selfref_proc]

    2: AS

    3: BEGIN

    4:     SET NOCOUNT ON

    5:     SELECT  [CategoryID],

    6:             [CategoryName],

    7:             [Description],

    8:             [Picture]

    9:     FROM    [NW].[dbo].[Categories]

   10:  

   11:     RETURN 0;

   12: END

NOTE: I am explicitly not using Northwind, but NW in this example.

The result of adding a self referencing stored procedure is that you will receive a TSD3025 warning

TSD3025: The following cross-database dependencies could not be verified: [NW].[dbo].[Categories], [NW].[dbo].[Categories].[CategoryID], [NW].[dbo].[Categories].[CategoryName], [NW].[dbo].[Categories].[Description], [NW].[dbo].[Categories].[Picture]. Your database application might fail at runtime when [dbo].[selfref_proc] is executed.

When you are adding a self referencing view, which looks like this:

    1: CREATE VIEW [dbo].[selfref_view]

    2: AS 

    3:     SELECT  [CategoryID],

    4:             [CategoryName],

    5:             [Description],

    6:             [Picture]

    7:     FROM    [NW].[dbo].[Categories]

You will get an TSD4001 error and a TSD3024 warning.

TSD4001: Invalid object name 'NW.dbo.Categories'. (SQL error = 208)

TSD3024: The following cross-database dependencies of [dbo].[selfref_view] could not be verified: [NW].[dbo].[Categories], [NW].[dbo].[Categories].[CategoryID], [NW].[dbo].[Categories].[CategoryName], [NW].[dbo].[Categories].[Description], [NW].[dbo].[Categories].[Picture]. You might not be able to deploy the project.  You can define a cross-database reference to resolve this warning.

The error is raised by the Design-time Validation database (DesignDB), since it use an arbitrary name for the scratch database which by definition is not NW.

So some of you might say, lets add Database Reference to your own DBMeta file, you are using a 3-part names and Database References are there to provide 3-part name resolution. This will not work, if you read the restrictions around database references closely, it states that:

Restrictions Since we added this functionality in a service release, we have some restrictions, below you will find an overview of the most important ones with an explanation on why these restrictions exist in the current system. Cyclic references: we do not support cyclic references. For example project A references project B and project B references A. We currently cannot support this because we cannot determine the correct build order in a guaranteed fashion (which project to build first) and since we always build the full database and cannot perform partial builds, cyclic references would result in to infinite builds. Only 3 or 4-part names are resolved, dynamic queries using for example OpenQuery, OpenRowset or OpenDataSource can not be resolved because the shape of the output can only be determined by executing the actual query against the actual targeted data source. Database references do not solve all causes for warnings and errors cause by cross database references, only those caused by explicit 3 and 4-part name usage, not for any of the dynamic query executions strategies. Self references are not allowed, in other words a database project cannot not establish a reference to its self. The underlying reason is that database reference contribute to a different namespace then the current project. You can not use the same database reference to resolve 3 and 4-part names at the same time; a database reference either contributes objects to a 4-part or a 3-part namespace. In real life you can have a linked server pointing to a local database on the same instance, this means you can reference objects in that database in two ways: through a 3-part name: SELECT * FROM D.S.T and through a 4-part name: SELECT * FROM SVR.D.S.T. If you have this situation you either have to change all your code to use a single access path, or you need two references. Variables used to identify 3 or 4-part name references need to be placed between square brackets or double quotes, to prevent parser errors.

The reason why you cannot resolve a self-reference through the current database reference implementation is that you would need to be able to build first, which and since then you would have to rebuild to resolve the references, invalidating the dbmeta file, so you would have to rebuild, and so on. In other words you would never finish building.

The conclusion therefore is that we currently cannot support self-referencing database call. The only option is to remove the self-referencing database part and use 2-part names instead.

-GertD

BTW: I am  investigating if we can provide a workaround for this issue by adding a well-known variable like $(ThisDB) that could be used to abstract the current database context.

Comments

• Anonymous
  January 03, 2008
  PingBack from http://geeklectures.info/2008/01/03/self-referencing/

• Anonymous
  January 07, 2008
  Joel Semeniuk on Team Based Development Video from TechEd. Sean Lumley on Web tests work at the HTTP...

• Anonymous
  January 09, 2008
  It's kinda hard to see how resolving "dbo.myproc" is harder than "mydb.dbo.myproc". I mean... you know the name of the current database, right?

• Anonymous
  January 11, 2008
  It appears that self-references in stored procedures are causing warning only(TSD3025) which can be suppressed using project options. However, self-references in views are causing warning(TSD3024) and error(TSD4001). There is no way an error can be suppressed by project options. Would it be possible to only report a warning(TSD3024) and NOT the error(TSD4001)? I would be able to easily suppress the warning for the self-references in the views/stor. procs using project options. Here is a reason why I'd like to suppress self-references warnings/errors: Let's say I import existing production DB into DBPro. I don't want to touch ANY TSQL code on initial import. The reason is that ANY code change will result in full regression testing effort which is very expensive for us.  I don't want to clean-up any self-references after the initial check-in. I would rather suppress any warnings about self-references. Thanks.