PROBLEM:  You cannot resolve  self-references through the current DBPro Project database reference implementation . For more info see:   The Data Dude Blog : Self Referencing

 

Example 1:   Self-referencing store procedure calls cause Warning TSD04151

 

 

Steps to replicate the problem:

  • Create a simple DBPro project using Microsoft Visual Studio Team System 2008 Database Edition GDR Version 9.1.40413.00  and name it NW
  • Import the Northwind database schema
  • Add a self referencing stored procedure to your  DBPro project

CREATE PROCEDURE [dbo].[selfref_proc]

AS

BEGIN

SET NOCOUNT ON

SELECT [CategoryID],

[CategoryName],

[Description],

[Picture]

FROM [NW].[dbo].[Categories]

RETURN 0;

END

  •  Build the solution

You will get the following warnings:

SELFREF_PROC.PROC.SQL(9,2)Warning TSD04151: Procedure: [dbo].[selfref_proc] has an unresolved reference to object [NW].[dbo].[Categories].[Picture].
SELFREF_PROC.PROC.SQL(8,1)Warning TSD04151: Procedure: [dbo].[selfref_proc] has an unresolved reference to object [NW].[dbo].[Categories].[Description].
SELFREF_PROC.PROC.SQL(7,1)Warning TSD04151: Procedure: [dbo].[selfref_proc] has an unresolved reference to object [NW].[dbo].[Categories].[CategoryName].
SELFREF_PROC.PROC.SQL(6,9)Warning TSD04151: Procedure: [dbo].[selfref_proc] has an unresolved reference to object [NW].[dbo].[Categories].[CategoryID].
SELFREF_PROC.PROC.SQL(10,10)Warning TSD04151: Procedure: [dbo].[selfref_proc] has an unresolved reference to object [NW].[dbo].[Categories].

 

SOLUTION 1: Remove the self-referencing stored procedure and use 2-part names instead.

Sample modified script:

CREATE PROCEDURE [dbo].[selfref_proc]  

AS   

 BEGIN   

SET NOCOUNT ON   

SELECT  [CategoryID],  

 [CategoryName], 

  [Description],   

[Picture]   

FROM    [dbo].[Categories]  

RETURN 0; 

END

Example 2 :   Self-referencing view calls cause Error TSD03006

Steps to replicate the problem:

  • Create a simple DBPro project using Microsoft Visual Studio Team System 2008 Database Edition GDR Version 9.1.40413.00 and name it NW
  • Import the Northwind database schema
  • Add a self referencing view to your DBPro project

CREATE VIEW [dbo].[selfref_view]

                AS

               

                SELECT [CategoryID],

                [CategoryName],

                [Description],

                [Picture] 

                FROM    [NW].[dbo].[Categories]

               

  •  Build the solution

You will get the following errors:

SELFREF_VIEW.VIEW.SQL(6,2)Error TSD03006: View: [dbo].[selfref_view] has an unresolved reference to object [NW].[dbo].[Categories].[Description].

SELFREF_VIEW.VIEW.SQL(5,2)Error TSD03006: View: [dbo].[selfref_view] has an unresolved reference to object [NW].[dbo].[Categories].[CategoryName].

SELFREF_VIEW.VIEW.SQL(4,9)Error TSD03006: View: [dbo].[selfref_view] has an unresolved reference to object [NW].[dbo].[Categories].[CategoryID].

SELFREF_VIEW.VIEW.SQL(8,10)Error TSD03006: View: [dbo].[selfref_view] has an unresolved reference to object [NW].[dbo].[Categories].

SOLUTION 2 : Remove the self-referencing view and use 2-part names instead.

 Sample modified script:

CREATE VIEW [dbo].[selfref_view]

                AS

               

                SELECT [CategoryID],

                [CategoryName],

                [Description],

                [Picture] 

                FROM    [dbo].[Categories]

 

 

Additional Info: The Data Dude Blog:  Error and Warning Groups