March 19, 2010 at 3:04 pm
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..
April 7, 2010 at 2:54 am
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 "
April 7, 2010 at 10:17 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply