Execute a script after transactional replication setup completes

Question

We successfully set up transactional replication with 15 articles. After successful of article changes Done in subscriber, we need to execute ".SQL File" at subscriber database. Please help on this.

1. Create the below table in Publisher database:

   Create Table DwDimTable (
   Ino int Primary Key
   ,Item Varchar(10)
   ,Usage numeric(16,2)
   ,StartDate date
   ,EndDate date)
   

2. Create the below table and run the query in the Subscriber database:

   Create table DwFactTable(
   Ino int,
   Item    varchar(15),
   PerDayUsage numeric(16,5),
   [Day]   int,
   [Month] int,
   [Year]  int,
   [Date]  date)
   Go
   SELECT TOP (1000) IDENTITY(INT,0,1) AS N
   INTO dbo.Multi
   FROM sys.columns c1, sys.columns c2 
   

3. Insert the data in the Publisher with below query:

   insert into DwDimTable 
   values 
       (1,'Gas Bill','9500.00',cast(GETDATE() as date),
       cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) as date))
   

4. Set up transactional replication for the article ‘DwDimTable’. After successful of transaction replication.

Here, I need assistance to execute/run automatically below .SQL file/Query
automatically at subscriber once publication is done:

insert into DwFactTable
SELECT DISTINCT  
Ino
,Item
,sum(Usage)/(DATEDIFF(DD,StartDate,EndDate)+1) [PerDayUsage]
,DD as [Day]
,MM as [Month]
,YY as [Year]
,CAST(CAST(MM AS VARCHAR)+'/'+CAST(DD AS VARCHAR)+'/'+CAST(YY AS VARCHAR) AS DATE) AS [Date]
FROM   DwDimTable
CROSS APPLY (SELECT DAY(DAYDT) AS DD, MONTH(DAYDT) AS MM, YEAR(DAYDT) AS YY
             FROM (SELECT TOP (DATEDIFF(DAY,StartDate,EndDate) + 1) 
                          DATEADD(DAY, N, StartDate) DAYDT
                   FROM DBO.Multi) AS R
            ) F
GROUP BY Ino,Item,Usage ,DD,MM,YY,StartDate,EndDate,
CAST(CAST(MM AS VARCHAR)+'/'+CAST(DD AS VARCHAR)+'/'+CAST(YY AS VARCHAR) AS DATETIME) 

Answer 1

I need assistance to execute/run automatically below .SQL file/Query automatically at subscriber once publication is done

You should use sp_addscriptexec tsql to run sql script on all subscribers of a publication once the snapshot is applied to subscribers. Make sure you use @skiperror = 0 to stop the distribution agent when an error is encountered.

Brandon Williams has a good blog post on - Executing scripts with sp_addscriptexec

Answer 2

from what I understand, you are trying to gather some information and then move it from publisher to subscribe, but they are in different formats (for warehousing purposes), I think you can use a linked server between two servers and you can use a 4 part query (like: server.Database.Schema.ObjectName) for accessing your subscriber from publisher or the other way, depending where your replication agents are.

then you can add a step, in the replication job, and add your query to that step and change the order of job execution so that it runs just after the replication agent succeeded, so after the replication process is finished, the query will be executed on the desired server.

Answer 3

Please try below highlighted options from your publication property. Create your script at some place and mention it in box.

Answer 4

You can create After Insert/Update/Delete trigger if you want to run script when any data Insert/Updates/Deletes at publication side. Here i have created script for After Insert.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[DwDimTable_INS]
   ON  [dbo].[DwDimTable]
   AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;

IF NOT EXISTS (SELECT * FROM INSERTED) BEGIN
    RETURN;
END

insert into DwFactTable
SELECT DISTINCT  
Ino
,Item
,sum(Usage)/(DATEDIFF(DD,StartDate,EndDate)+1) [PerDayUsage]
,DD as [Day]
,MM as [Month]
,YY as [Year]
,CAST(CAST(MM AS VARCHAR)+'/'+CAST(DD AS VARCHAR)+'/'+CAST(YY AS VARCHAR) AS DATE) AS [Date]
FROM   DwDimTable
CROSS APPLY (SELECT DAY(DAYDT) AS DD, MONTH(DAYDT) AS MM, YEAR(DAYDT) AS YY
             FROM (SELECT TOP (DATEDIFF(DAY,StartDate,EndDate) + 1) 
                          DATEADD(DAY, N, StartDate) DAYDT
                   FROM DBO.Multi) AS R
            ) F
GROUP BY Ino,Item,Usage ,DD,MM,YY,StartDate,EndDate,
CAST(CAST(MM AS VARCHAR)+'/'+CAST(DD AS VARCHAR)+'/'+CAST(YY AS VARCHAR) AS DATETIME) 

END
GO