Re: How to test a custom conflict resolver procedure
- From: pmelies <pmelies@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 27 Jul 2006 11:40:02 -0700
I guess my bigger question is - Do I have to manually setup a linked server
once I set my stored procedure as a custom resolver? The stored procedure is
failing because the subscriber is not in sysservers?
We have hundreds of subscribers so if this is the case what are the
ramifications of setting up hundreds of linked servers?
"Hilary Cotter" wrote:
can you query the subscriber from the publisher using the linked server in.
QA?
Here is an example of a custom resolver I wrote which might help you
understand how to do it.
create database Steve
GO
create database SteveSub
GO
use Steve
GO
create table ConflictTest
(pk int not null identity constraint ConflictTestPK primary key,
col1 int,
col2 varchar(20),
col3 int,
col4 int,
col5 varchar(20),
col6 text, rowguid uniqueidentifier ROWGUIDCOL default newID())
GO
insert into ConflictTest(col1, col2, col3, col4, col5, col6)
values(1,'test',2,3,'test','this is a test of the text col')
insert into ConflictTest(col1, col2, col3, col4, col5, col6)
values(1,'test',2,3,'test','this is a test of the text col')
insert into ConflictTest(col1, col2, col3, col4, col5, col6)
values(1,'test',2,3,'test','this is a test of the text col')
insert into ConflictTest(col1, col2, col3, col4, col5, col6)
values(1,'test',2,3,'test','this is a test of the text col')
insert into ConflictTest(col1, col2, col3, col4, col5, col6)
values(1,'test',2,3,'test','this is a test of the text col')
insert into ConflictTest(col1, col2, col3, col4, col5, col6)
values(1,'test',2,3,'test','this is a test of the text col')
insert into ConflictTest(col1, col2, col3, col4, col5, col6)
values(1,'test',2,3,'test','this is a test of the text col')
insert into ConflictTest(col1, col2, col3, col4, col5, col6)
values(1,'test',2,3,'test','this is a test of the text col')
insert into ConflictTest(col1, col2, col3, col4, col5, col6)
values(1,'test',2,3,'test','this is a test of the text col')
insert into ConflictTest(col1, col2, col3, col4, col5, col6)
values(1,'test',2,3,'test','this is a test of the text col')
GO
sp_replicationdboption 'Steve','merge publish',true
go
sp_addmergepublication 'conflicttest'
GO
sp_addpublication_snapshot 'conflicttest'
GO
--creating the stored procedure conflict resolver
CREATE procedure customResolver(
@tableowner sysname,
@tablename sysname,
@rowguid uniqueidentifier,
@subscriber sysname,
@subscriber_db sysname,
@log_conflict int OUTPUT ,@conflict_message nvarchar(512) OUTPUT )
as
set nocount on
declare @intcol int
--notice we are not interested in col3
select @intcol=binary_checksum(col1,col2,col4,col5,col6) from
steve.dbo.conflicttest where rowguid = convert(nchar(36),@rowguid)
/* Get the dest row */
select @intcol=@intcol - binary_checksum(col1,col2,col4,col5,col6) from
stevesub.dbo.conflicttest where rowguid = convert(nchar(36),@rowguid)
if @intcol=0
begin
select @log_conflict =0
select @conflict_message ='successful'
end
select @log_conflict =1
select @conflict_message ='not successful'
return
go
sp_addmergearticle 'conflicttest','conflicttest',@source_owner
='dbo',@source_object='conflicttest',@article_resolver = 'Microsoft
SQLServer Stored Procedure Resolver',
@resolver_info = '[dbo].[customResolver]'
go
use steve
go
--this should not generate a conflict
update steve.dbo.conflicttest set col3=5
update stevesub.dbo.conflicttest set col3=5
--this should not generate a conflict
update steve.dbo.conflicttest set col1=5
update stevesub.dbo.conflicttest set col1=5
--this should generate a conflict
update steve.dbo.conflicttest set col4=5
update stevesub.dbo.conflicttest set col1=5
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"pmelies" <pmelies@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8E036961-7A3F-4DE0-B781-BBACF1EDDFF1@xxxxxxxxxxxxxxxx
I have a business need to implement a custom conflict resolver for a merge
replication. I've done this and I want to actually test the procedure to
view the output row before I change the resolver of the article in SQL
Server
2000 (we don't have a test environment) since this will affect a large
number
of subscribing databases.
However, when I execute the stored procedure in Query Analyer I get a
"Could
not find server 'x' in sysservers. Execute sp_addlinkedserver to add the
server to sysservers." 'x' is a valid subscriber servername but it SQL
Server can't resolve it from within the custom resolver.
Is there a way I can actually test this procedure without adding a linked
server or setting it as an article's resolver?
- References:
- Re: How to test a custom conflict resolver procedure
- From: Hilary Cotter
- Re: How to test a custom conflict resolver procedure
- Prev by Date: Express Merge Relication Failing at runtime
- Next by Date: Re: LogShipping: Get a copy of the destination DB?
- Previous by thread: Re: How to test a custom conflict resolver procedure
- Next by thread: Transactional Replication SQL 2005 Cannot connect to the subscriber
- Index(es):
Relevant Pages
|
|
