none
Stored procedure based custom resolver???

    질문

  • Hi,

    I’m using Merge Replication, 2 handheld devices, 1 server
    and implementing Row level tracking.

    I get a conflict in 2 tables. The values that cause conflict
    are the change in foreign key value and the modified date field. The conflicts
    occur when i try to sync for the following scenarios:

    Scenario 1:

    • Initially sync both devices
    • Update a row in Device1
    • Update the same row in Device2
    • Sync Device1
    • Sync Device2

    Scenario 2:

    • Initially sync both devices
    • Update a row in Device1
    • Sync Device1
    • Update the same row in Device2 with out syncing second time
    • Sync Device2

    The conflicts occur. I tried using Microsoft SQL Server (Later Wins) Conflict Resolver. The issue is resolved for Scenario 1 whereas, conflicts occur for Scenario2.

    Using Microsoft SQL Server (Earlier Wins) Conflict Resolver, the issue is resolved for Scenario2, but conflicts for Scenario1.

    So I have written a Stored procedure conflict resolver. I have datetime field in both tables i.e ModifiedOn which updates to the latest date. I want to get the Latest date and that needs to be updated at the publisher and subscriber, without any conflicts.

    I tried with below mentioned 2 SP’s :

    Procedure 1:

    CREATE procedure spHandleConflict

     @tableowner sysname,

     @tablename sysname,

     @rowguid varchar(36),

     @subscriber sysname,

     @subscriber_db sysname,

     @log_conflict INT OUTPUT,

     @conflict_message nvarchar(512)OUTPUT

    AS

     DECLARE

      @ModifiedOn datetime,

      @SQL_TEXT nvarchar(2000)

    /*** Temp table to hold ModifiedOn value from the conflicting subscriber ***/

    create table #tempTable(

          ModifiedOn datetime,

          rowguid varchar(50)

    )

    SET @SQL_TEXT ='INSERT INTO #tempTable

    SELECT ModifiedOn
    FROM '
    +@subscriber+'.'+@subscriber_db+'.'+@tableowner+'.'+@tablename+

    ' WHERE rowguid =
    '
    + @rowguid +';'

    EXEC @SQL_TEXT

    Select @ModifiedOn = ModifiedOn from #tempTable where  rowguid=@rowguid

    drop table #tempTable

    GO


    Procedure 2:

    CREATE procedure [dbo].[sproc_products_conflicts_handler]

     @tableowner sysname,

     @tablename sysname,

     @rowguid varchar(36),

     @subscriber sysname,

     @subscriber_db sysname,

     @log_conflict INTOUTPUT,

     @conflict_message nvarchar(512)OUTPUT

    AS

    setnocounton

     DECLARE

      @ModifiedOn datetime,

      @SQL_TEXT nvarchar(2000),

      @RecentCheck datetime

    /*** Temp table to hold ModifiedOn value from the
    conflicting subscriber ***/

    create table #tempSubscriber

          (

          ModifiedOn DateTime,

          rowguid varchar(36)

          )

    SET @SQL_TEXT ='insert into #tempSubscriber ModifiedOn, rowguid)

                 Select ModifiedOn, rowguid

         From   ['+@subscriber+'].'+@subscriber_db+'.'+@tableowner+'.'+@tablename+' Where rowguid='''+@rowguid+''''


    EXEC sp_executesql @SQL_TEXT

    SELECT @RecentCheck = MAX(ModifiedOn) FROM #tempSubscriber

    SELECT TOP 1 * FROM #tempSubscriber where ModifiedOn=@RecentCheck;


    -- Cleanup and exit

    drop table #tempsubscriber


    But both of them are not working and I am getting the
    following error with Procedure 1.

    “A call to SQL Server Reconciler failed. Try to resynchronize.; The Stored Procedure Resolver encountered the following error executing the stored procedure 'spHandleConflict'. The name 'INSERT INTO #tempTable

    SELECT ModifiedOn FROM AssetRepl_SUB -
    3ffadf43bb0a.Flash File
    Store\AssetManagementMobile\AssetManagement.sdf.dbo.AssetVerification WHERE
    rowguid = 6912F169-1369-E111-82D8-00155DACE101;' is not a valid identifier.;
    The process was successfully stopped.”

    WHERE

    • AssetRepl_SUB à
         Subscription Name.
    • Flash File Store\AssetManagementMobile\ à Location
           where sdf file is stored in Handheld device.
    • AssetManagement.sdf -> sdf file in handheld device
    • dbo.AssetVerification -> table name.

    With Procedure 2 I am getting the below error:


    “A call to SQL Server Reconciler failed. Try to
    resynchronize.; The Stored Procedure Resolver encountered the following error
    executing the stored procedure 'spHandleConflict'. Incorrect syntax near the
    keyword 'File'.; The process was successfully stopped.”


    Please help me out by providing a solution to the problem.

    Regards,

    Sanketh


    2012년 3월 9일 금요일 오전 8:46

답변

  • From

    we have the following statement:

    http://msdn.microsoft.com/en-us/library/ms172380%28v=sql.90%29.aspx

    Stored procedure based merge conflict resolvers are not supported by SQL Server Compact Edition subscriptions.

    and

    http://msdn.microsoft.com/en-us/library/ms172380%28v=sql.105%29.aspx

    Stored procedure based merge conflict resolvers are not supported by SQL Server Compact 3.5 subscriptions.

    I don't see how you can accomplish this with conflict resolvers.

    But let me see if I have it right - you update the same row on device 1 and device 2 between syncrhonizations. Device 1's change is persisted between the publisher and device1, but device 2's change remains in device 2?


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • 답변으로 표시됨 Sanketh Bappal 2012년 12월 3일 월요일 오전 9:06
    2012년 3월 21일 수요일 오후 3:15

모든 응답

  • It appears as if both errors have to do with the procedure 1 spHandleConflict.

    I believe the offending line for the first error is:

    ' WHERE rowguid =
    '+ @rowguid +';'

    The @rowguid parameter will need single quotes around it.

    I believe the offending line for the second error is:

    SELECT ModifiedOn
    FROM '+@subscriber+'.'+@subscriber_db+'.'+@tableowner+'.'+@tablename+

    I think either a @subscriber or @subscriber_db might have a space in its name and will need brackets like:

    From   ['+@subscriber+']

    Brandon Williams (blog | linkedin)

    2012년 3월 9일 금요일 오전 9:10
  • There will only be one row with a rowguid value matching the conflict value as there is a unique index on this row Why do you think there will be two?

    What this resolver does is always retrieves the subscriber row and then resolves the conflict with it even if the publisher date is more recent.

    Also you are replicating to an SDF where stored procedure resolvers do not work.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • 답변으로 제안됨 Peja Tao 2012년 3월 12일 월요일 오전 6:47
    2012년 3월 9일 금요일 오후 3:34
  • The conflicts occur in 2 tables, since the same value (foreign key) is getting updated into these tables.For eg if foreign key value is 1000, i update it to 1001 in Device1 and 1002 in Device2. I sync Device1(successful). When i sync Device2 i get conflict. Even the ModifiedOn value (datetime) get updated based on what time i sync. I just need to to get Latest date by comparing at publisher and Subsriber and update both ends with data by choosing the latest date.

    As per my situation the Latest date will be at the Subscriber so i get that date and update it for that particular rowguid in the SP (Procedure 1).

     I'm not not replicating to an SDF. I've written the stored procedure at the database level. I'm just using Stored Procedure Conflict Resolver and given this SP name at the enter information box.

    2012년 3월 14일 수요일 오전 4:44
  • This sure looks like an sdf

    AssetRepl_SUB-3ffadf43bb0a.Flash File Store\AssetManagementMobile\AssetManagement.sdf.dbo.AssetVerification

    What are you replicating to?


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    2012년 3월 14일 수요일 오후 2:18
  • I found out that the parameter value that the stored procedure gets for "@subscriber_db" is the location of an sdf. This indicates i'm replicating to an sdf, as you said  which is not possible. The "@subscriber_db" parameter is got directly from the handheld when SP is executed.

    So is it not possible to solve this conflicts using stored procedure?

    So is there an other way so that i can resolve these conflicts?

    Please help.

    2012년 3월 21일 수요일 오후 1:01
  • From

    we have the following statement:

    http://msdn.microsoft.com/en-us/library/ms172380%28v=sql.90%29.aspx

    Stored procedure based merge conflict resolvers are not supported by SQL Server Compact Edition subscriptions.

    and

    http://msdn.microsoft.com/en-us/library/ms172380%28v=sql.105%29.aspx

    Stored procedure based merge conflict resolvers are not supported by SQL Server Compact 3.5 subscriptions.

    I don't see how you can accomplish this with conflict resolvers.

    But let me see if I have it right - you update the same row on device 1 and device 2 between syncrhonizations. Device 1's change is persisted between the publisher and device1, but device 2's change remains in device 2?


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • 답변으로 표시됨 Sanketh Bappal 2012년 12월 3일 월요일 오전 9:06
    2012년 3월 21일 수요일 오후 3:15
  • What is happening in my case is:

    CASE 1:

    Sync both devices initially. Update a row on Device1 and sync. Sync Device 2. Update the same row on device2 and sync. This works perfectly fine if the sync button is pressed multiple times.

    CASE 2:

    Sync both devices initially. Update a row on Device1 and sync; device 1's change is persisted between the publisher and device1. Update the same row on device2 and sync. The changes are successfully changed in device2 and Publisher. But when i press sync button for the 2<sup>nd</sup>time, conflicts arise.

    I've written code in such a way that, when i press sync button, i check if there's conflicts using the following query.

    SELECT count(*) FROM dbo.MSmerge_conflicts_info.

    If the above statement returns true it means there are no conflicts and i continue with the process; if false, exit.  First time when I press sync button, it returns true. But when i press sync button for 2nd time, it returns false (in Case 2).

    I’m not able to understand, why this is happening when I press sync button again (in Case 2).

    What may be the problem?

    2012년 3월 22일 목요일 오전 6:01