Udostępnij za pośrednictwem

DBPro: How to resolve self-referencing database calls in Microsoft Visual Studio Team System 2008 Database Edition GDR Version 9.1.40413.00

  • Artykuł

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

Comments

  • Anonymous
    January 03, 2011
    This workaround is unfeasible. I am trying to get our 4 databases into source safe and I am looking at 468 three-part self-references. I am supposed to go fix them all?