Internal Paramaters on sp_MSdel<TableName>, sp_MSins<TableName>, sp_MSupd<TableName>

Question

The following procedures sp_MSdel, sp_MSins, sp_MSupd are the outcome of an configured publication/subscription on SQL Server.

These procedures let you do small transformations inside them so the streamed data can be transferred or reach the destination in different schema.

For instance having a publication for a table called "Data" with two columns into a publisher:

CREATE TABLE [dbo].[Data](
    [id] [int] NOT NULL,
    [description] [nchar](10) NULL,
    CONSTRAINT [PK_Dasta] PRIMARY KEY CLUSTERED ([id] ASC) ON [PRIMARY]
 )

I can have a little bit different structure for a table called "Data" with three columns into a subscriber:

CREATE TABLE [dbo].[Data](
    [tenant_id] NOT NULL,
    [id] [int] NOT NULL,
    [description] [nchar](10) NULL,
    CONSTRAINT [PK_Dasta] PRIMARY KEY CLUSTERED ([tenant_id] ASC, [id] ASC) ON [PRIMARY]
 )

While doing this I can also modify the replication procedure [dbo].[sp_MSins_dboData] to add logic in this new destination column tenant_id

ALTER procedure [dbo].[sp_MSins_dboData]
    @c1 int,
    @c2 nchar(10)
as
begin  
    insert into [dbo].[Data] (
        [tenant_id],
        [id],
        [description],
    ) values (
        1,
        @c1,
        @c2)

Now in a scenario where there are multiple publishers pointing to the same subscription, is it possible to pass a parameter of origin (so instead of the hardcoded one to make it variable) to make sure of which origin are the streamed data?

I suppose the easiest solution is to create different procedures for each source database like sp_MSins_dboData_1, sp_MSins_dboData_2 etc but I was thinking for something more dynamic.

Any ideas?

Answer 1

Now in a scenario where there are multiple publishers pointing to the same subscription

I take it to mean you'd be Publishing from multiple source tables to a single Subscriber table. This is not recommended and is error prone.

Instead replicate each Publication to a different table, and even schema for better manageability, and use a view (or some other mechanism) to unify the data. Please see this related DBA.StackExchange answer suggesting the same.

The following procedures sp_MSdel, sp_MSins, sp_MSupd are the outcome of an configured publication/subscription on SQL Server.

These procedures let you do small transformations inside them...

No, please don't do that either. Those are the native procedures that Replication generates for each Article in your Publication. It is also error prone and poor management to change those directly.

Instead, when you add an Article to Replication, under the Article Properties, in the Statement Delivery section, you can specify your own stored procedures for the INSERT, UPDATE, DELETE processes:

Unfortunately I don't think you can specify parameters here (though I haven't tried myself). But that should no longer be relevant if you heed my advice about not replicating multiple Publisher tables to the same Subscriber table.

Answer 2

I managed to do this for demo purposes, so I am not sure how this will behave for large scale projects like in the other answers are mentioning.

What I did is the following:

• Created the "Data" table into publisher1 database (two columns)
• Created the "Data" table into publisher2 database (two columns)
• Created the "Data" table into subscriber database (three columns)
• Created two stored-procedures (sp_MSins_dboData_1 & sp_MSins_dboData_2) for the inserts based on the originals to test the inserts.
• While creating the publications in the article configuration, I configured the following:
  • not to copy anything like schemas, constrains etc in order not to overwrite anything on the destination.
  • set the property 'COPY INSERT, UPDATE and DELETE stored procedures' to 'false' in order not to overwrite your procedures using the default procedures.
  • set the property 'INSERT stored procedure' to your procedure_name 'sp_MSins_dboData_1' in order to use this custom procedure for this specific publication for handling inserts holding your destination logic (in this case the additional column).
  • set the property 'action if name is in use' to 'Keep existing object unchanged' in order not to overwrite your table

One thing I noticed, is that you cannot initiate snapshot while using this configuration as the destination has a different schema but there are options under the Snapshot Page in the publication to apply scripts before and after applying a snapshot.