How to get notification about completion of applying snapshot

All,
In Transactional Pull model, is there a way from any storedprocedure to
get notification from the distribution agent that the initial snapshot
has been applied on the subscriber?
This notification has to be got in my Windows forms C# application and
related UI changes done.
Can anyone help me?
Thanks,
Lavanya
Have a final job step executed on completion of the snapshot job. This could
notify the C# application. You could do this through a file existence check,
mail slots, named pipes or some other mechanism.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lavanya" <lavanyah@.gmail.com> wrote in message
news:1165406832.173244.309460@.l12g2000cwl.googlegr oups.com...
> All,
> In Transactional Pull model, is there a way from any storedprocedure to
> get notification from the distribution agent that the initial snapshot
> has been applied on the subscriber?
> This notification has to be got in my Windows forms C# application and
> related UI changes done.
> Can anyone help me?
> Thanks,
> Lavanya
>
|||I would require to obtain notification from the distribution agent and
not the snapshot agent.
And since the agent runs on the subscriber for pull model, how do i
track the end of application of the first snapshot?
Thanks,
Lavanya
Hilary Cotter wrote:[vbcol=seagreen]
> Have a final job step executed on completion of the snapshot job. This could
> notify the C# application. You could do this through a file existence check,
> mail slots, named pipes or some other mechanism.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Lavanya" <lavanyah@.gmail.com> wrote in message
> news:1165406832.173244.309460@.l12g2000cwl.googlegr oups.com...
|||Am still not clear from where i can get the state of the
pull-distribution agent.
Fyi: I have a centralised read only subscriber which has Pull
transactional model configured.
1. Can i use sp_repltrans at publisher database and confirm that the
status of Log reader agent is idle if no rows are returned?
2. Can i use sp_browsereplcmds at distribution database and confirm
that the status of the distribution agent running at the subscriber is
idle if no rows are returned?
Is there any way i can find at the subscriber whether all my
publishers' distribution agents are in idle state?
Please reply asap which would help in increasing the robustness of my
application.
Thanks in advance,
Lavanya
Lavanya wrote:[vbcol=seagreen]
> I would require to obtain notification from the distribution agent and
> not the snapshot agent.
> And since the agent runs on the subscriber for pull model, how do i
> track the end of application of the first snapshot?
> Thanks,
> Lavanya
> Hilary Cotter wrote:
|||I don't believe you can use sp_repltrans as this only, nor is sp_
browsereplcmds helpful. I think you would be best off setting
historyverboselevel to 2 and then querying mdistribution_history looking for
messages like Delivered snapshot from the
'unc\ServerName_PublicationName\20061219073651\' sub-folder in 1522
milliseconds
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lavanya" <lavanyah@.gmail.com> wrote in message
news:1166511416.572294.66530@.80g2000cwy.googlegrou ps.com...
> Am still not clear from where i can get the state of the
> pull-distribution agent.
> Fyi: I have a centralised read only subscriber which has Pull
> transactional model configured.
> 1. Can i use sp_repltrans at publisher database and confirm that the
> status of Log reader agent is idle if no rows are returned?
> 2. Can i use sp_browsereplcmds at distribution database and confirm
> that the status of the distribution agent running at the subscriber is
> idle if no rows are returned?
> Is there any way i can find at the subscriber whether all my
> publishers' distribution agents are in idle state?
> Please reply asap which would help in increasing the robustness of my
> application.
> Thanks in advance,
> Lavanya
> Lavanya wrote:
>
|||Thanks for your response.
Just to confirm my understanding, both sp_repltrans and
sp_browsereplcmds will not help in finding the current state of
logreader and (pull)distribution agents
As you said, i set the historyverboselevel to 2 for the distribution
agent and tried to look into
MSdistribution_history table but could not find "Delivered snapshot..."
string. I found the below instead:
Applied script 'Table1_4.dri'
Applied script 'Table2_4.dri'
Applied script 'Table3_4.dri'
Applied script 'Table4_4.dri'
3 transaction(s) with 35 command(s) were delivered.
No replicated transactions are available.
Hence, can i look out for "No replicated transactions are available."
instead?
What is that 1522 milliseconds?
Also, is there a way to look into my centralized pull subscriber(SQL
2005) whether all the distribution agents for publishers(SQL 2000) are
currently idle or not?
Regards,
Lavanya
Hilary Cotter wrote:[vbcol=seagreen]
> I don't believe you can use sp_repltrans as this only, nor is sp_
> browsereplcmds helpful. I think you would be best off setting
> historyverboselevel to 2 and then querying mdistribution_history looking for
> messages like Delivered snapshot from the
> 'unc\ServerName_PublicationName\20061219073651\' sub-folder in 1522
> milliseconds
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Lavanya" <lavanyah@.gmail.com> wrote in message
> news:1166511416.572294.66530@.80g2000cwy.googlegrou ps.com...
|||try looking for > Applied script 'Table4_4.dri' if that is the last thing to
be delivered in your snapshot.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lavanya" <lavanyah@.gmail.com> wrote in message
news:1166594011.399191.87600@.n67g2000cwd.googlegro ups.com...
> Thanks for your response.
> Just to confirm my understanding, both sp_repltrans and
> sp_browsereplcmds will not help in finding the current state of
> logreader and (pull)distribution agents
> As you said, i set the historyverboselevel to 2 for the distribution
> agent and tried to look into
> MSdistribution_history table but could not find "Delivered snapshot..."
> string. I found the below instead:
> Applied script 'Table1_4.dri'
> Applied script 'Table2_4.dri'
> Applied script 'Table3_4.dri'
> Applied script 'Table4_4.dri'
> 3 transaction(s) with 35 command(s) were delivered.
> No replicated transactions are available.
> Hence, can i look out for "No replicated transactions are available."
> instead?
> What is that 1522 milliseconds?
> Also, is there a way to look into my centralized pull subscriber(SQL
> 2005) whether all the distribution agents for publishers(SQL 2000) are
> currently idle or not?
> Regards,
> Lavanya
>
> Hilary Cotter wrote:
>
|||Thanks Hilary for your help.
For the snapshot successful delivery confirmation, i check the status
of 'No replicated Transaction available' comment in
MSdistribution_history table from my C# application. This is more
reliable as am not sure of the number that succeeds the table name nor
am sure of the table which is the last to be delivered. This works
fine.
Now, i have another clarification.
>From my C#.Net application, i need to check whether there are any
pending transactions from publ. to subs. before i allow the user to
remove replication setup from the UI.
As previously asked,
1. Can i use sp_repltrans at publisher database, check if no rows are
returned and confirm that no data is pending to be transferred ?
2. Can i use sp_browsereplcmds at distribution database,check if no
rows are returned and hence confirm that subscriber and publisher are
in total sync ?
Reply asap.
Thanks in advance,
-Lavanya
Hilary Cotter wrote:[vbcol=seagreen]
> try looking for > Applied script 'Table4_4.dri' if that is the last thing to
> be delivered in your snapshot.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Lavanya" <lavanyah@.gmail.com> wrote in message
> news:1166594011.399191.87600@.n67g2000cwd.googlegro ups.com...
|||No, to both. You have to hack msdistribution_status for the info you are
looking for.
Query MSsubscriptions to get the values you need for your subscriber
SELECT t.article_id,s.agent_id,
'UndelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno > h.maxseq THEN 1 ELSE 0
END),
'DelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno <= h.maxseq THEN 1 ELSE 0 END)
FROM (SELECT article_id,publisher_database_id, xact_seqno
FROM MSrepl_commands with (NOLOCK) ) as t
JOIN (SELECT agent_id,article_id,publisher_database_id FROM MSsubscriptions
with (NOLOCK) ) AS s
ON (t.article_id = s.article_id AND
t.publisher_database_id=s.publisher_database_id )
JOIN (SELECT agent_id,'maxseq'= isnull(max(xact_seqno),0x0) FROM
MSdistribution_history with (NOLOCK) GROUP BY agent_id) as h
ON (h.agent_id=s.agent_id)
where s.publisher_database_id=57 --obtained from mssubscriptions
and s.publisher_id=0 --obtained from mssubscriptions
and s.subscriber_id=7 --obtained from mssubscriptions
and s.subscriber_db='test' --obtained from mssubscriptions
GROUP BY t.article_id,s.agent_id
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lavanya" <lavanyah@.gmail.com> wrote in message
news:1167212049.957146.58730@.a3g2000cwd.googlegrou ps.com...
> Thanks Hilary for your help.
> For the snapshot successful delivery confirmation, i check the status
> of 'No replicated Transaction available' comment in
> MSdistribution_history table from my C# application. This is more
> reliable as am not sure of the number that succeeds the table name nor
> am sure of the table which is the last to be delivered. This works
> fine.
> Now, i have another clarification.
> pending transactions from publ. to subs. before i allow the user to
> remove replication setup from the UI.
> As previously asked,
> 1. Can i use sp_repltrans at publisher database, check if no rows are
> returned and confirm that no data is pending to be transferred ?
> 2. Can i use sp_browsereplcmds at distribution database,check if no
> rows are returned and hence confirm that subscriber and publisher are
> in total sync ?
> Reply asap.
> Thanks in advance,
> -Lavanya
> Hilary Cotter wrote:
>