Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

replication help super scenario Expand / Collapse
Author
Message
Posted Friday, March 19, 2010 3:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 20, 2013 11:18 AM
Points: 35, Visits: 235
Here is the scenario..

I have server A and Server B.



Server A will send us data from their table to our table in server B.



i am working in server B and dont have any access to server A.



server A setup a transactional replication to send data from their tables to our server B tables.



My work: When i see replicated data to table in server B, i will exec SP that push some data from that table to some other table in server b itself. this needs to be exec. after replication job finish.



replication job occurs once a week.



question: i want to know when replication finish and then i exec SP.(I dont know the time and exact day of week, when server A user do the replication)



My thing is to do the exec of SP after successfully done with replication.



Transaction replication:

Server A has publisher and distributor.

Server B(My server) has subscription. Server A push the data to server B.



Please let me know the steps, what i can do from my server B to check the replication is finish or not. Pls remember, no user from server B has access to server A. and also i want the result of last subscription done..Do i need to create a job or any SSIS pls let me know..

 

Hint:

I check this subscription database system tables which are generated at sub. side.





http://msdn.microsoft.com/en-us/library/ms179855(SQL.90).aspx but could not get thing done..


Post #886738
Posted Wednesday, April 07, 2010 2:54 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 29, 2012 5:22 AM
Points: 260, Visits: 800
I think you can get some info from this table 1: MSsubscription_properties

&

2: MSsnapshotdeliveryprogress

progress_timestamp
datetime
The datetime value that indicates when a snapshot file was successfully delivered.



Good Luck !



Cheers,
- Win.

" Have a great day "
Post #898296
Posted Wednesday, April 07, 2010 10:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 11, 2012 6:00 AM
Points: 52, Visits: 556
Win, as he has stated, he (or any user from Server B) does not have access to server A (publisher and distributor). So, they cannot query the replication metadata tables (that you pointed out).
In that scenario, you can ask the adminstrator on Server A to configure a Replication:Agent Success alert which will send an email. He can add you as an operator (or recipient) and you can recieve an email once the distribution agent completes work. Upon recieving the email, you can execute it on the server.
Or, you will need to ask the admin on server B to create a stored procedure that can query (SELECT) the replication metadata tables (namely distribution..msdistribution_history) and expose it as a webservice. You can then execute this webservice which will tell you when the distribution agent completed sync and then you can execute your code on server B.

OR your SP on server B will need to be executed from Server A as a 4-part (or distributed) query. You can add this as an extra jobstep in the distribution agent job step.
Post #898726
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse