Replicate production data for reporting (3)–Change Sps

/*
Use the same script as (2) to create the publication and subscription databases and populate data.
Set up the pull subscription on the subscriber. The replication must be comnfigured to use stored procedures.
SQL server generates three procedures:
sp_MSdel_PubTable
sp_MSins_PubTable
sp_MSupd_PubTable
After snapshot is applied and two databases are syncronized, we need to change those stored procedures. The code in red is added to validate the timestamp of the data involved.
*/
ALTER procedure "sp_MSdel_PubTable" @pkc1 int
as
DECLARE @St datetime, @Et datetime
SELECT @St=dateadd(day,-45,getdate()),@Et=dateadd(day,-15,getdate())
IF NOT EXISTS(SELECT 1 FROM deleted WHERE RecordTime<@St OR RecordTime>=@Et)
BEGIN
 RETURN
END
delete "PubTable"
where "C1" = @pkc1
if @@rowcount = 0
 if @@microsoftversion>0x07320000
  exec sp_MSreplraiserror 20598
GO
ALTER procedure "sp_MSins_PubTable" @c1 int,@c2 int,@c3 datetime
AS
BEGIN
DECLARE @Et datetime
SELECT @Et=dateadd(day,-15,getdate())
IF NOT EXISTS(SELECT 1 FROM inserted WHERE RecordTime>=@Et)
BEGIN
 RETURN
END
insert into "PubTable"(
"C1", "C2", "RecordTime"
 )
values (
@c1, @c2, @c3
 )
END
GO
ALTER procedure "sp_MSupd_PubTable"
 @c1 int,@c2 int,@c3 datetime,@pkc1 int
,@bitmap binary(1)
as
DECLARE @Et datetime
SELECT @Et=dateadd(day,-15,getdate())
IF NOT EXISTS(SELECT 1 FROM deleted WHERE RecordTime>=@Et)
BEGIN
 RETURN
END
if substring(@bitmap,1,1) & 1 = 1
begin
update "PubTable" set
"C1" = case substring(@bitmap,1,1) & 1 when 1 then @c1 else "C1" end
,"C2" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else "C2" end
,"RecordTime" = case substring(@bitmap,1,1) & 4 when 4 then @c3 else "RecordTime" end
where "C1" = @pkc1
if @@rowcount = 0
 if @@microsoftversion>0x07320000
  exec sp_MSreplraiserror 20598
end
else
begin
update "PubTable" set
"C2" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else "C2" end
,"RecordTime" = case substring(@bitmap,1,1) & 4 when 4 then @c3 else "RecordTime" end
where "C1" = @pkc1
if @@rowcount = 0
 if @@microsoftversion>0x07320000
  exec sp_MSreplraiserror 20598
end
GO
— On the publisher, run the following tests:
— remove expired data
DELETE dbo.PubTable WHERE C1=30
— The data is deleted from publisher but not subscriber
GO
— remove worng live data
DELETE dbo.PubTable WHERE C1=5
— The data is deleted from publisher and subscriber
GO
— insert live data
INSERT dbo.PubTable (C1,C2,RecordTime) VALUES (-1,-100,dateadd(day,1,getdate()))
— The data is inserted in publisher and subscriber
GO
— update live data
UPDATE dbo.PubTable SET C2=1000 WHERE C1=-1
— The data is updated in publisher and subscriber
GO
— On the subscriber database, run the following test:
— remove expired data:
DELETE dbo.PubTable WHERE C1=50
— Data is removed from the subscription database.
 
Advertisements
This entry was posted in SQL server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s