The short story. Many branch databases need to be consolidated into one
central database.

Each branch has identical schemas:

CREATE TABLE [Table1] (
[PK1] [int] NOT NULL ,
CONSTRAINT [PK_Table1] PRIMARY KEY
(
[PK1]
) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [Table2] (
[DBGUID] [uniqueidentifier] NOT NULL ,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[DBGUID]
) ON [PRIMARY]
) ON [PRIMARY]


The consolidated database looks like:

CREATE TABLE [Table1] (
[PK1] [int] NOT NULL ,
[BranchID] [int] NOT NULL ,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[PK1],
[BranchID]
) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [Branch] (
[BranchID] [int] NOT NULL ,
[DBGuid] [uniqueidentifier] NULL ,
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
[BranchID]
) ON [PRIMARY]
) ON [PRIMARY]



Table1 in each branch gets replicated into table Table1 in the consolidated
database.

Branch 1 Table1 Data
1
2
3
4

Branch 2 Table1 Data
1
2
3
4

Consolidated Table1
1 1
2 1
1 2
3 1
2 2
3 2
4 2
4 1


What needs to happen is that the consolidated database needs the GUID from
each branch database to figure out the branch ID for that database. Once
it's figured it out, it needs to append it to the data. It seemes like
replacing sp_MSinsTable1 would be a slick, changing it from

create procedure "sp_MSins_Table1" @c1 int
AS
BEGIN
insert into "Table1"( "PK1" )
values ( @c1 )
END

-- note: not checked for syntax

create procedure "sp_CustomIns_Table1" @c1 int, @dbGUID GUID
AS
BEGIN
DECLARE BID INT
SET BID=sp_GetBranchID(GUID) -- note this would be a stored proc in the
consolidated db

insert into "Table1"( "PK1" ,"BranchID")
values ( @c1,BID )
END


The problem is I can't figure out how to get the GUID passed to the custom
proc. I can easily tell it to call the proc if I set up the replication in
the wizards, but beyond that, I'm lost. How, when and where does the
auto-created spMSinsTable1 get called and how to I intercept that?


Bob Castleman
Lost in Space


p.s. I lied. Not a short story.

Some more info ...

I suppose I could use DTS and make a transformable subscription. This sounds
great until I consider that in 24 months there would be 200 or more
databases replicated into the consolidated database and every replicated
table would have a DTS of its own resulting in potentially 20,000 DTS
packages. I could also hard code the sproc called by replication, but this
is worse since there is an update, delete, and insert query for each
article - or in 24 months 60,000 sprocs to manage. Ideally, a single sproc
for each table in the consolidated database that was smart enough to figure
out which database called it would GREATLY reduce the management involved.
Then I have only about 300 procs involved in replication no matter how many
databases get added to the system.

Does this make sense or am I on crack?

Bob Castleman
Replicating Misery