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



 


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 (1)

  1. Sean says:

    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?