locked
Subscriber Cannot Update in Transactional Replication RRS feed

  • Question

  • Hello,

    I set up a queued updatable push subscription for transactional replication between two SQL Server 2005 boxes. When the Subscriber updated one of tables, it returned the following error:

    Msg 14126, Level 16, State 3, Procedure sp_check_sync_trigger, Line 28
    You do not have the required permissions to complete the operation.
    Msg 20512, Level 16, State 1, Procedure sp_MSreplraiserror, Line 8
    Updateable Subscriptions: Rolling back transaction.
    Msg 3609, Level 16, State 1, Procedure <my stored proc>, Line 65
    The transaction ended in the trigger. The batch has been aborted.

    The account I used is already sysadmin role. Did I miss something else?

    Thanks a lot,

    Frank


    Monday, January 21, 2013 8:43 AM

Answers

  • Hi Karthick,

    Just wanted to keep you updated. You were right that the extra update trigger was causing the problem. I have dropped the replication and recreated it. After that, there is only 1 update trigger on the subscriber side. And Subscriber is able to update the table.

    Your help is very appreciated.

    Best Regards,

    Frank Yu

    • Marked as answer by Fengjun Yu Friday, January 25, 2013 7:49 AM
    Friday, January 25, 2013 7:49 AM

All replies

  • Looking at store proc which is failing, It looks like one if the trigger is missing in MSreplication_objects.

    {

        if not exists (select so.object_id from (dbo.MSreplication_objects as ro join sys.objects as so     on ro.object_name = so.name) 

            where so.object_id = @trigger_procid and ro.object_type = 'T'    and (@owner is null or schema_name(so.schema_id) = @owner)) 

        begin 

            raiserror(14126, 16, 3) 

            return -1 

        end 

    }

    Share the output of below query.

    select * from  MSreplication_objects  where article='Table for which update is failling'


    Thank you,

    Karthick P.K |My Site|My Scribbles |Blog Space |Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Monday, January 21, 2013 12:46 PM
  • Karthick,

    Thanks for looking into it. Here is the query result:

    MYSQLSVR MYDB MYPUB sp_MSdel_MYTABLE P MYTABLE
    MYSQLSVR MYDB MYPUB sp_MSdel_MYTABLE P MYTABLE
    MYSQLSVR MYDB MYPUB sp_MSdel_MYTABLE P MYTABLE

    Three triggers are in place. For the table, there are 4 triggers. Other than the above three, there is another one named sp_MSsync_upd_trig_MYTABLE_9.

    Any other thought?

    Thanks,

    Frank

    • Edited by Fengjun Yu Monday, January 21, 2013 3:12 PM reformatting
    Monday, January 21, 2013 3:10 PM
  • Hello Frank,

    Please share the output of below query.

    select object_name,* from dbo.MSreplication_objects ro
    where object_name not in (select name from sys.sysobjects)


    Thank you,

    Karthick P.K |My Site|My Scribbles |Blog Space |Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Tuesday, January 22, 2013 12:03 PM
  • Hi Karthick,

    The query returned nothing. I ran against the replication database on Subscriber.

    Any other thought?

    Thanks,

    Frank

    Wednesday, January 23, 2013 3:15 AM
  • Hi Karthick,

    The query returned nothing. I ran against the replication database on Subscriber.

    Any other thought?

    Thanks,

    Frank

    Hello Frank,

    I was really busy yesterday and so could not look in to this yesterday, But I was able to reproduce the issue in my test system today.

    As per the below logic in “sp_check_sync_trigger”  If you have a update trigger in a table which is not part of MSreplication_objects then we raise “You do not have the required permissions to complete the operation.”

    {

    if not exists (select so.object_id from (dbo.MSreplication_objects as ro join sys.objects as so 

            on ro.object_name = so.name) 

            where so.object_id = @trigger_procid and ro.object_type = 'T'  

            and (@owner is null or schema_name(so.schema_id) = @owner)) 

        begin 

            raiserror(14126, 16, 3) 

            return -1 

       end

    }

    Identify all the triggers of the table in question and see which trigger is not part of MSreplication_objects and disable it.

    You can use the below query

    {

    select * from sys.objects where type='TR' and name not in (select object_name from  MSreplication_objects)

    and parent_object_id in (select OBJECT_ID('Replace your Table here'))

    }

    Let me know how it goes


    Thank you,

    Karthick P.K |My Site|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Thursday, January 24, 2013 7:38 AM
  • Hi Karthick,

    Thank for your time! I verified that it indeed failed at where you pointed out. It called raiserror(14126, 16, 3).

    The following query returned 1 record (sp_MSsync_upd_trig_MYTABLE_9):

    select * from sys.objects where type='TR' and name not in (select object_name from MSreplication_objects)

    and parent_object_id in (select OBJECT_ID('MYTABLE'))

    Then I ran another query and it return 1 record matched with the update trigger:

    select * from

    (dbo.MSreplication_objects as ro join sys.objects as so

    on ro.object_name = so.name)

    where ro.object_type='T' and

    parent_object_id in (select OBJECT_ID('MYTABLE'))

    and object_name ='trg_MSsync_upd_MYTABLE'

    Also I manually ran the following query and it worked fine too.

    select so.object_id from (dbo.MSreplication_objects as ro join sys.objects as so

    on ro.object_name = so.name)

    where so.object_id = 1289107683 and ro.object_type = 'T'

    and schema_name(so.schema_id) = N'dbo')

    Oddly, Subscriber is able to update other tables involved in the replication but only this table cannot be updated.

    Thanks,

    Frank

    Thursday, January 24, 2013 8:31 AM
  • Disable  "sp_MSsync_upd_trig_MYTABLE_9" trigger. It will start working. Update on other tables will work as long as there is no user created trigger which is not part of replication

    Thank you,

    Karthick P.K |My Site|My Scribbles |Blog Space |Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem



    Thursday, January 24, 2013 8:35 AM
  • Karthick,

    It did not make any difference after I disabled "sp_MSsync_upd_trig_MYTABLE_9". Here is the definition of "sp_MSsync_upd_trig_MYTABLE_9":

    ALTER trigger [dbo].[sp_MSsync_upd_trig_MYTABLE_9] on [dbo].[MYTABLE  ] for update as

     declare @rc int

     select @rc = @@ROWCOUNT

     if @rc = 0 return

     if update (msrepl_tran_version) return

     update [dbo].[MYTABLE] set msrepl_tran_version =newid()from [dbo].[MYTABLE], inserted 

          where        [dbo].[MYTABLE].[LogID] = inserted.[LogID]      

    Also from the Profiler trace, the above trigger never gets called. It failed within the "trg_MSsync_upd_MYTABLE" when it called the sys.sp_check_sync_trigger:

    ALTER trigger [dbo].[trg_MSsync_upd_MYTABLE] on [dbo].[MYTABLE] for update not for replication

    as

         declare @rc int

                    ,@retcode int

                    ,@connect_string nvarchar(300)

                    ,@islocalpublisher bit

                    ,@rpc_proc nvarchar(500)

                    ,@update_mode_id int

                    ,@bitmap varbinary(4000)

                    ,@version_guid uniqueidentifier

                    ,@trigger_op char(10) 

                    ,@failover_mode_id int

                    ,@queue_server sysname

                    ,@queue_id sysname

                    ,@tran_id varchar(255)

                    ,@subscriber sysname

                    ,@subscriber_db sysname

                    ,@partial_cmd bit

                    ,@start_offset int

                    ,@end_offset int

                    ,@vb_buffer varbinary(8000)

                    ,@vb_bufferlen int       

                

        declare       @c1 char(15)     ,@c2 int     ,@c3 bit     ,@c4 int     ,@c5 int     ,@c6 datetime     ,@c7 smalldatetime     ,@c8 uniqueidentifier     ,@c9 int

        declare       @c1_old char(15)     ,@c2_old int     ,@c3_old bit     ,@c4_old int     ,@c5_old int     ,@c6_old datetime     ,@c7_old smalldatetime     ,@c8_old uniqueidentifier     ,@c9_old int

        select @rc = @@rowcount

                ,@subscriber = @@servername

                ,@subscriber_db = db_name()

                ,@version_guid = newid()

        if @rc = 0

            return

        set nocount on 

        select @bitmap = columns_updated()

        

        -- set the bit for msrepl_tran_version

        select @bitmap = substring(@bitmap, 1, 0) + (convert(binary(1), substring(@bitmap, 1, 1) | convert(tinyint,128))) + substring(@bitmap, 2, 1) 

        -- trigger nesting check

        -- error = -1, nested call = 1, not a nested call = 0

        exec @retcode = sys.sp_check_sync_trigger @@procid, @trigger_op OUTPUT, N'dbo'

        if (@retcode = -1)

            goto FAILURE

    Thanks a lot,

    Frank

    Thursday, January 24, 2013 9:01 AM
  • Run this query check if you are getting the same error

    declare @trigger_op char(10)
    Declare @pid int
    select @pid=OBJECT_ID('trg_MSsync_upd_MYTABLE') //Replace both the update trigger in this place and check one by one
    exec sp_check_sync_trigger1 @pid, @trigger_op OUTPUT, N'dbo'


    Thank you,

    Karthick P.K |My Site|My Scribbles |Blog Space |Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Thursday, January 24, 2013 10:34 AM
  • Hi Karthick,

    Here are query results:

    declare @trigger_op char(10)
    Declare @pid int
    select @pid=OBJECT_ID('trg_MSsync_upd_MYTABLE')
    exec sp_check_sync_trigger @pid, @trigger_op OUTPUT, N'dbo'

    Msg 14126, Level 16, State 2, Procedure sp_check_sync_trigger, Line 16
    You do not have the required permissions to complete the operation.

    =================================================================

    declare @trigger_op char(10)
    Declare @pid int
    select @pid=OBJECT_ID('trg_MSsync_upd_MYTABLE_9')
    exec sp_check_sync_trigger @pid, @trigger_op OUTPUT, N'dbo'

    Msg 14126, Level 16, State 3, Procedure sp_check_sync_trigger, Line 28
    You do not have the required permissions to complete the operation.

    It raised the same error but interestingly the line number is different for these two runs.  

    Thanks,

    Frank

    Thursday, January 24, 2013 3:22 PM
  • Hi Karthick,

    Just wanted to keep you updated. You were right that the extra update trigger was causing the problem. I have dropped the replication and recreated it. After that, there is only 1 update trigger on the subscriber side. And Subscriber is able to update the table.

    Your help is very appreciated.

    Best Regards,

    Frank Yu

    • Marked as answer by Fengjun Yu Friday, January 25, 2013 7:49 AM
    Friday, January 25, 2013 7:49 AM
  • Thanks for the update. I have blogged about this on http://www.sqlserver-wiki.com/2013/01/updateinser-or-delete-on-tables-at.html

     


    Thank you,

    Karthick P.K |My Site|My Scribbles |Blog Space |Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Monday, January 28, 2013 6:21 AM