1

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) 
0

4 Answers 4

4

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

0
0

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.

0

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

-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.