replacing sp_MSinsTable1

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.
















.

Relevant Pages

  • Re: Sync/Merge local sql compact databases to single global database
    ... with the host database you have the advantage of a unique number and knowing ... ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, ... CONSTRAINT fkPlayerID FOREIGN KEY REFERENCES Players ... sync capabilities of compact SQL handle the sessions table? ...
    (microsoft.public.sqlserver.ce)
  • Re: Altering columns...getting complicated...
    ... Unless there is a constraint, in which case I have to drop the constraint, alter the field, add the constraint back in. ... What I'm thinking is that I should dump all of the indexes and primary keys ... However, this database was "created" by using the Access upsizing wizard, so ... I don't know all the primary key names, constraint names, etc. ...
    (microsoft.public.sqlserver.msde)
  • Sync/Merge local sql compact databases to single global database
    ... for one of the tables i have had to use auto incrementing integer which works fine for the local database but i would like to merge all of the users databases into a global database. ... ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, ... CONSTRAINT fkPlayerID FOREIGN KEY REFERENCES Players ... how will the sync capabilities of compact SQL handle the sessions table? ...
    (microsoft.public.sqlserver.ce)
  • RE: Problems saving data to SQL database, timeout expired
    ... Thank you for having a look at the database structure. ... -- Installing the "event" database. ... ADD CONSTRAINT PK_AlarmState ... PRIMARY KEY ...
    (microsoft.public.sqlserver.server)
  • Transform with conditions
    ... I'm trying to transfer all records from Database A Table1 to Database ... B Table1 getting only the records that are not currently in Database B ... I have a primary key that is in both. ... How do I do this with DTS please? ...
    (microsoft.public.sqlserver.dts)