Custom Resolver Stored Procedure

• Tuesday, November 13, 2012 2:50 PM

   

   
  

  0

  I have a merge replication that uses Sql Server Express 2008R2 on the client machines and SQL Server 2008 R2 on the main server.

  I tried to create a custom resolver stored procedure on my simplest table. I receive this error message when the resolver is needed:

  The Stored Procedure Resolver encountered the following
  error executing the stored procedure 'uspCustomerServiceNotesConflictsHandler'.
  Could not find server 'DLI000WHE\SQLExpress' in sys.servers. Verify that the cor
  rect server name was specified. If necessary, execute the stored procedure sp_ad
  dlinkedserver to add the server to sys.servers.

  Does a custom resolver work with SQL Express? I have 85 clients and setting them up as linked servers is not something I want to do.

  Here is my sproc

  ALTER procedure [dbo].[uspCustomerServiceNotesConflictsHandler]
   @tableowner sysname,
   @tablename sysname,
   @rowguid varchar(36),
   @subscriber sysname,
   @subscriber_db sysname,
   @log_conflict INT OUTPUT,
   @conflict_message nvarchar(512) OUTPUT

  AS
   set nocount on
   DECLARE
    @ServiceNotes nvarchar(max),
    @ServiceNotesConflict nvarchar(max),
    @ServiceNotesUsed nvarchar(max),
    @CustomerID int,
     @SQL_TEXT nvarchar(2000)

  /*** Temp table to hold ServiceNotes value from the conflicting subscriber ***/
  create table #tempSubscriber
   (CustomerID int,
   ServiceNotes nvarchar(max),
   rowguid varchar(36)
   )

  SET @SQL_TEXT ='insert into #tempSubscriber
     (CustomerID, ServiceNotes,  rowguid)
     Select CustomerID, CustomerServiceNotes, rowguid
       From  ['+@subscriber+'].'+@subscriber_db+'.'+@tableowner+'.'+@tablename+
         '  Where rowguid='''+@rowguid+''''

  EXEC sp_executesql @SQL_TEXT

  Select @ServiceNotesConflict = ServiceNotes
  From  #tempSubscriber
  where  rowguid=@rowguid

  /*** Get the current values from the publisher. Use the one with the most characters ***/
  select @ServiceNotes = CustomerServiceNotes
  from tblCustomerServiceNotes
  where rowguid=@rowguid

  If LEN(@ServiceNotes) >= LEN(@ServiceNotesConflict)
   Begin
    Select @ServiceNotesUsed = @ServiceNotes
   end
   else
   Begin
    Select @ServiceNotesUsed = @ServiceNotesConflict
   End
  /***Update the publisher and Subscriber with the new column values ***/
  select CustomerID, @ServiceNotesUsed, rowguid
  from tblCustomerServiceNotes
  where rowguid=@rowguid

  drop table #tempsubscriber

  
  
  

  • Edited by Del at Equisoft Tuesday, November 13, 2012 3:00 PM
  •  
  • Reply
  • Quote