Step 4
Now, let’s create the archive tables and the purge procedure on the Subscriber “MAK” [Fig 1.4]
Use sales
go
Create table Orders_Archive (Order_id int constraint Orders_PK1 primary key,
custid int, Date datetime, Total_Amount money, ship_address varchar(300),
archivedate datetime constraint Orders_archive_default default getdate())
Go
Create table Order_Details_Archive (order_details_id int constraint
order_details_PK1 primary key, Order_id int constraint Orders_archive_FK1 foreign
key references Orders_Archive(Order_Id), item_id int, quantity int, unit_price money,
discount decimal (5,2), archivedate datetime constraint Order_details_archive_default
default getdate())
Go
Create procedure Usp_Archive_Purge as
Insert into Orders_Archive (Order_id, custid, Date, Total_Amount, ship_address)
select Order_id, custid, Date, Total_Amount, ship_address from Orders
where convert(varchar(8),date,112)=convert(varchar(8),getdate()-1,112)
Insert into Order_Details_Archive
(order_details_id, Order_id, item_id , quantity , unit_price, discount )
select order_details_id, Order_id, item_id , quantity , unit_price, discount from Order_details
where order_id in (select order_id from orders where convert(varchar(8),date,112)=convert(varchar(8),getdate()-1,112))
Delete from Order_Details where
order_id in (select order_id from orders where convert(varchar(8),date,112)=convert(varchar(8),getdate()-1,112))
Delete from Orders where convert(varchar(8),date,112)=convert(varchar(8),getdate()-1,112)
Go
Step 5
Now, let’s add the stored procedure “USP_Archive_Purge” to the replication.
In the publisher “EBONY,” execute the following statements.
use Sales –change to your publishing database name
go
sp_addarticle
@publication =’Sales’, –change to your publication
@pre_creation_cmd=’none’,
@article = ‘USP_Archive_Purge’ ,
@destination_table = ‘USP_Archive_Purge’ ,
@type =’proc exec’,
@schema_option=0x01,
@destination_owner =’dbo’,
@source_owner =’dbo’,
@source_object=’USP_Archive_Purge’ ,
@force_invalidate_snapshot =0,
@status=0
go
exec sp_addsubscription
@publication = N’Sales’, –change to your publication
@article = N’USP_Archive_Purge’ ,
@subscriber = N’MAK’, — Change to your subscriber name
@destination_db = N’Sales’, — Change to your subscription database name
@sync_type = N’none’,
@update_mode = N’read only’ ,
@subscription_type = ‘PULL’ –change this to push or PULL depending on your environment
go
sp_refreshsubscriptions ‘Sales’ –change to your publisher name
go
Step 6
Execute the procedure “USP_Archive_Purge” on the publisher “EBONY” as shown below.
Use Sales
Go
Exec USP_Archive_Purge
Go
Results
You can see that some rows have been moved from the source table to archive table and have been deleted.
(5 row(s) affected)
(8 row(s) affected)
(8 row(s) affected)
(5 row(s) affected)
On the Replication folder in Enterprise Manager you will notice that only one transaction has been delivered. [Fig 1.3]
[Fig 1.3]
When you do “sp_browsereplcmds” on the distribution database, you can see only one command that was called, which is executing the procedure.
{call “dbo”.”USP_Archive_Purge” }