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.
-
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)
-
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
-
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))
-
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)
Best Answer
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