VS2010 Partial Database Projects, will they help in my scenario?

Question

Can I use partial Database Projects in the following scenario?

I have numerous databases which are all inter-dependent on each other. Database2 executes SPs on Database1 and Database3 and Database3 executes SPs on Database2. For example:

Database 1 could be defined as:

CREATE TABLE [dbo].[X]
(
    Id int NOT NULL, 
    X int NULL
)

CREATE PROCEDURE [dbo].[GetX]
AS
BEGIN
    SELECT * FROM [dbo].[X]
END

Database 2 could be defined as:

CREATE PROCEDURE [dbo].[GetX]
AS
BEGIN
    EXEC [Database1].[dbo].[GetX]
END


CREATE PROCEDURE [dbo].[GetXY]
AS
BEGIN
    EXEC [dbo].[GetX]
    EXEC [Database3].[dbo].[GetY]
END

Database 3 could be defined as:

CREATE TABLE [dbo].[Y]
(
    Id int NOT NULL, 
    Y int NULL
)

CREATE PROCEDURE [dbo].[GetX]
AS
BEGIN
        EXEC [Database2].[dbo].[GetX]
END

CREATE PROCEDURE [dbo].[GetY]
AS
BEGIN
    SELECT * FROM [dbo].[Y]
END

Because Database2 is dependent on Database3 and Database3 on Database2 there is a circular reference. I'm not sure how partial projects would help me here because if I separated Database2 between base and external objects, I would still have a circular reference on the external project. So as I understand it, this is not allowed.

Apologies for the explanation, the databases are hurting my head too. I will try to be more verbose if required.

score 0 · Accepted Answer · answered Jul 20 '11 at 12:53

This has effecivley been answered by http://social.msdn.microsoft.com/Forums/en/vstsdb/thread/8357d599-9d03-4ed6-879f-e090f9c96fcd

In short, for simple scenarios such as the example, this will work but for level of complexity the management required will prevent the of use of Database Projects.

We still plan generate the required object files using VS in order to store in TFS. Unfortunatley we will be unable to build deployment scripts.

VS2010 Partial Database Projects, will they help in my scenario?

1 Answers1