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:
RestrictionsSince 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.
|
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.