SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


replication help super scenario


replication help super scenario

Author
Message
san-1015392
san-1015392
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 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..
- Win.
- Win.
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1470 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 "
mangeshd
mangeshd
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search