Determine updated and inserted rows

I'm working on an algorithm to backup data from pocket pc sql server 2.0
database involved in an merge replication with sql server 2000 sp3 with rda
in case that replication fails from whatever reason. My question is if I can
determine on a sql server ce 2.0 table involved in merge replication what
rows have been updated or inserted form last replication in order to
transfer only those rows on backup database. I've noticed that inserted rows
have s_Generation field 0 but I have no clue about updated rows.
Any changed (inserted or updated) row in ce database which is part of a
replicated table will have the s_generation column set to zero until the
next sync. So this is just an indicator of changed rows between two
successive syncs.
thanks
gopal

Detecting status of snapshot agent...

Is there an easy way to determine the status/progress of the snapshot agent
for a particular merge publication?
Troy - much the same as the question for your merge agent:
sp_help_job will do it on an ad-hoc basis, but if you need to do something
as a result of the job status, you could modify this script to do it:
http://www.replicationanswers.com/Downloads/KillRunningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Detecting if merge agent is running

Hi There,
I was wondering if there is a way of determining programatically if the
merge agent is running? If it is running is there a way of stopping it and
restarting it?
Thanks
Vivek
Vivek,
you'd be better off posting this in the VB.NET group, but something like
this should do the trick:
Dim localAll As Process() = Process.GetProcesses()
iterate through this array to find the merge.exe process then use
myproc.kill() to remove it.
HTH,
Paul Ibison (MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||try this if you are using c#: (you'll need to add the SQLMERGXLib.dll,
REPLERRXLib.dll, SQLINITXLib.dll references)
using System;
using SQLMERGXLib ;
using REPLERRXLib ;
using SQLINITXLib ;
namespace SQLReplicationManager
{
/// <summary>
/// Class to handle merge replication initation and termination
/// </summary>
public class ReplicationHandler
{
private SQLMerge _sqlMerge ;
private SQLReplError _replErr ;
public event _SQLMergeEvents_StatusEventHandler StatusEvent ;
private string _PUBLISHER = "SomePublisher" ;
private string _PUBLISHER_DATABASE = "SomePublishedDatabase" ;
private string _PUBLISHER_LOGIN_NAME = "TestName" ;
private string _PUBLISHER_PASSWORD = "Password" ;
private string _SUBSCRIBER_LOGIN_NAME = "SubscriberLoginName" ;
private string _SUBSCRIBER_PASSWORD = "SubscriberPassword" ;
private string _DISTRIBUTOR_LOGIN_NAME = "DistributorLoginName" ;
private string _DISTRIBUTOR_PASSWORD = "DistributorPassword" ;
private string _DISTRIBUTOR_SERVER_NAME = "DistributionServer"
private string _MERGE_PUBLICATION_NAME = "SomeMergePublicationName" ;
private string _SUBSCRIBER_NAME = "SubscribingMachineName" ;
//System.Dns.GetHostName() for local machine
private string _SUBSCRIBER_DATABASE = "SubscribingDatabaseName" ;
public ReplicationHandler()
{
Initialise() ;
}
private void Initialise()
{
_sqlMerge = new SQLMergeClass() ;
_replErr = new SQLReplErrorClass() ;
_sqlMerge.Status += new
_SQLMergeEvents_StatusEventHandler(_sqlMerge_Statu s);
_sqlMerge.Publisher = _PUBLISHER;
_sqlMerge.PublisherDatabase = _PUBLISHER_DATABASE ;
_sqlMerge.PublisherSecurityMode =
SQLMERGXLib.SECURITY_TYPE.DB_AUTHENTICATION ;
_sqlMerge.PublisherLogin = _PUBLISHER_LOGIN_NAME ;
_sqlMerge.PublisherPassword = _PUBLISHER_PASSWORD ;
_sqlMerge.Subscriber = _SUBSCRIBER_NAME ;
_sqlMerge.SubscriberDatabase = _SUBSCRIBER_DATABASE ;
_sqlMerge.SubscriberSecurityMode =
SQLMERGXLib.SECURITY_TYPE.DB_AUTHENTICATION;
_sqlMerge.SubscriptionType = SUBSCRIPTION_TYPE.PULL ;
_sqlMerge.SubscriberLogin = _SUBSCRIBER_LOGIN_NAME ;
_sqlMerge.SubscriberPassword = _SUBSCRIBER_PASSWORD ;
_sqlMerge.DistributorLogin = _DISTRIBUTOR_LOGIN_NAME ;
_sqlMerge.DistributorPassword = _DISTRIBUTOR_PASSWORD ;
_sqlMerge.Distributor = _DISTRIBUTOR_SERVER_NAME;
}
private SQLMERGXLib.STATUS_RETURN_CODE _sqlMerge_Status(string Message,
int Percent)
{
StatusEvent(Message, Percent) ;
return new SQLMERGXLib.STATUS_RETURN_CODE ();
}
public void SynchroniseMergeData()
{
try
{
_sqlMerge.Publication = _MERGE_PUBLICATION_NAME ;
_sqlMerge.Initialize() ;
_sqlMerge.Run() ;
_sqlMerge.Terminate() ;
}
catch(Exception exp)
{
if(_sqlMerge.ErrorRecords.Count > 0)
{
string errors = "" ;
foreach(SQLMERGXLib.ISQLReplError err in _sqlMerge.ErrorRecords)
{
errors += err.Description + "\n" ;
if(err.ErrorNumber == 21036)
{
throw new AgentRunningException("Merge agent is already
running.\n") ;
}
if(err.ErrorNumber == 20084 || err.ErrorNumber == 17)
{
throw new System.Exception("Can not connect to server.\nPlease
check network connection") ;
}
}
throw new System.Exception("The following errors occured:\n" + errors
+ "\n" + "The following system exception occured:\n" + exp.Message) ;
}
else
{
throw new System.Exception(exp.Message) ;
}
}
finally
{
// do something usefull ?
}
}
private SQLINITXLib.STATUS_RETURN_CODE _sqlSnapshot_Status(string
Message, int Percent)
{
StatusEvent(Message, Percent) ;
return new SQLINITXLib.STATUS_RETURN_CODE ();
}
}
public class AgentRunningException : System.ApplicationException
{
public AgentRunningException(string message) : base(message)
{
}
}
}
|||I use the merge control as you showed it here but always when I want to
access the ErrorRecords property to loop through the errors (in a foreach as
you did) I get an exception that says that the cast is invalid.
I tried to cast to everything (SQLReplError, ISQLReplError,
SQLReplErrorClass - in REPLERRXLib and SQLMERGXLib) - nothing worked for me.
(casting to a SQLReplErrorClass always works but accessing a property of the
object results in an exception that says that QueryInterface for
REPLERRXLib.ISQLReplError did not work)
"ranjeet" wrote:

> try this if you are using c#: (you'll need to add the SQLMERGXLib.dll,
> REPLERRXLib.dll, SQLINITXLib.dll references)
> using System;
> using SQLMERGXLib ;
> using REPLERRXLib ;
> using SQLINITXLib ;
> namespace SQLReplicationManager
> {
> /// <summary>
> /// Class to handle merge replication initation and termination
> /// </summary>
> public class ReplicationHandler
> {
> private SQLMerge _sqlMerge ;
> private SQLReplError _replErr ;
> public event _SQLMergeEvents_StatusEventHandler StatusEvent ;
> private string _PUBLISHER = "SomePublisher" ;
> private string _PUBLISHER_DATABASE = "SomePublishedDatabase" ;
> private string _PUBLISHER_LOGIN_NAME = "TestName" ;
> private string _PUBLISHER_PASSWORD = "Password" ;
> private string _SUBSCRIBER_LOGIN_NAME = "SubscriberLoginName" ;
> private string _SUBSCRIBER_PASSWORD = "SubscriberPassword" ;
> private string _DISTRIBUTOR_LOGIN_NAME = "DistributorLoginName" ;
> private string _DISTRIBUTOR_PASSWORD = "DistributorPassword" ;
> private string _DISTRIBUTOR_SERVER_NAME = "DistributionServer"
> private string _MERGE_PUBLICATION_NAME = "SomeMergePublicationName" ;
> private string _SUBSCRIBER_NAME = "SubscribingMachineName" ;
> //System.Dns.GetHostName() for local machine
> private string _SUBSCRIBER_DATABASE = "SubscribingDatabaseName" ;
> public ReplicationHandler()
> {
> Initialise() ;
> }
> private void Initialise()
> {
> _sqlMerge = new SQLMergeClass() ;
> _replErr = new SQLReplErrorClass() ;
> _sqlMerge.Status += new
> _SQLMergeEvents_StatusEventHandler(_sqlMerge_Statu s);
> _sqlMerge.Publisher = _PUBLISHER;
> _sqlMerge.PublisherDatabase = _PUBLISHER_DATABASE ;
> _sqlMerge.PublisherSecurityMode =
> SQLMERGXLib.SECURITY_TYPE.DB_AUTHENTICATION ;
> _sqlMerge.PublisherLogin = _PUBLISHER_LOGIN_NAME ;
> _sqlMerge.PublisherPassword = _PUBLISHER_PASSWORD ;
> _sqlMerge.Subscriber = _SUBSCRIBER_NAME ;
> _sqlMerge.SubscriberDatabase = _SUBSCRIBER_DATABASE ;
> _sqlMerge.SubscriberSecurityMode =
> SQLMERGXLib.SECURITY_TYPE.DB_AUTHENTICATION;
> _sqlMerge.SubscriptionType = SUBSCRIPTION_TYPE.PULL ;
> _sqlMerge.SubscriberLogin = _SUBSCRIBER_LOGIN_NAME ;
> _sqlMerge.SubscriberPassword = _SUBSCRIBER_PASSWORD ;
> _sqlMerge.DistributorLogin = _DISTRIBUTOR_LOGIN_NAME ;
> _sqlMerge.DistributorPassword = _DISTRIBUTOR_PASSWORD ;
> _sqlMerge.Distributor = _DISTRIBUTOR_SERVER_NAME;
> }
> private SQLMERGXLib.STATUS_RETURN_CODE _sqlMerge_Status(string Message,
> int Percent)
> {
> StatusEvent(Message, Percent) ;
> return new SQLMERGXLib.STATUS_RETURN_CODE ();
> }
> public void SynchroniseMergeData()
> {
> try
> {
> _sqlMerge.Publication = _MERGE_PUBLICATION_NAME ;
> _sqlMerge.Initialize() ;
> _sqlMerge.Run() ;
> _sqlMerge.Terminate() ;
> }
> catch(Exception exp)
> {
> if(_sqlMerge.ErrorRecords.Count > 0)
> {
> string errors = "" ;
> foreach(SQLMERGXLib.ISQLReplError err in _sqlMerge.ErrorRecords)
> {
> errors += err.Description + "\n" ;
> if(err.ErrorNumber == 21036)
> {
> throw new AgentRunningException("Merge agent is already
> running.\n") ;
> }
> if(err.ErrorNumber == 20084 || err.ErrorNumber == 17)
> {
> throw new System.Exception("Can not connect to server.\nPlease
> check network connection") ;
> }
> }
> throw new System.Exception("The following errors occured:\n" + errors
> + "\n" + "The following system exception occured:\n" + exp.Message) ;
> }
> else
> {
> throw new System.Exception(exp.Message) ;
> }
> }
> finally
> {
> // do something usefull ?
> }
> }
> private SQLINITXLib.STATUS_RETURN_CODE _sqlSnapshot_Status(string
> Message, int Percent)
> {
> StatusEvent(Message, Percent) ;
> return new SQLINITXLib.STATUS_RETURN_CODE ();
> }
> }
> public class AgentRunningException : System.ApplicationException
> {
> public AgentRunningException(string message) : base(message)
> {
> }
> }
> }
>

Detecting if a merge agent is currently in process.

How can you determine if a merge agent (pull subscription) is currently in
process?
Troy,
sp_help_job will do it on an ad-hoc basis, but if you need to do something
as a result of the job status, you could modify this script to do it:
http://www.replicationanswers.com/Downloads/KillRunningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .