Error Handling in Merge Replication Custom Conflict Resolver
-
hi, i am using a stored procedure to resolve conflicts during merge replication. in the resolver, what i need to do is the get both records from subscriber and publisher having the conflict to decide what to do with it.
therefore, in the procedure, i make use of a distributed query by 4 part naming.
part of the code is as follows.
...
select sqlstring = 'select * from subscriber_server.database.dbo.tablename'
insert into temptable
exec(sqlstring)
if @@error <>0 goto error_1
....
the problem here is that whenever the query fails due to the publisher unable to resolve the subscriber servername in the 'select' statement. the replication agent terminates with an error instead of going to
in the code (which makes a default choice of conflict winner), resulting in replication not being completed. therefore, my question is that is there a way to do error handling for this situation? i have tried to nest this part of the code in a nested stored procedure (to try to see if its just a scope-termination error and beat it but it doesnt)
or is there a t-sql function that tests existence of a server before running the query but does not terminate the agent.
please advise (including any other method.) frustrated for weeks at this. :=======(
-
This is an 'old' problem - when a Transact SQL statement refers to a non-existing or non-responding remote server, it aborts hard. AFAIK, there's not much you can do about it, it's how things currently work.
One way to go around it I've seen is to set up either the query itself, or a 'ping' query in a job-fashion as separate steps. The step after the one trying the remote access can then decide if it was successful or not, and in any case, flow can continue even though the remote access failed.
Not entirely sure how feasible it would be in a merge replication scenario though...
/Kenneth
-
have a look at this thread. There is a DMO solution to detect if the connectivity to the server is good
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=152697#bm152737
* Noel -
Personally I'd never touch the sp_OAxx procs, they're just too much risk crashing the entire server imo...
Granted, this is a personal opinion only
On the other hand, you can easily enough get the same end result the 'batch' way by using, say xp_cmdshell and have isql/osql query the server with an output file specified, then parse that file for the results.
There are many ways to skin this cat, though none currently as lean as one would like.
/Kenneth
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply