Expired subscription clean up Agent job failing

  • Hi

    The "Expired subscription clean up" agent job, which is part of the merge replication running on one of our SQL servers is failing with the message 

    "Executed as user: FI\svc_nonprod_sqlag. DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934). The step failed."

    We tracked the error down to one of the replicated columns which is computed and has the persisted flag on it, so we can make an index on the column. It's really important that he subscriber has this column and index.

    We can't figure out a way to keep the column replicated and have the "Expired subscription clean up" agent job run successfully.

    We're also unsure of the implication of the agent job failing and what is it represents.

    Any ideas?

    Alex

  • alex.palmer - Wednesday, January 25, 2017 4:56 AM

    Hi

    The "Expired subscription clean up" agent job, which is part of the merge replication running on one of our SQL servers is failing with the message 

    "Executed as user: FI\svc_nonprod_sqlag. DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934). The step failed."

    We tracked the error down to one of the replicated columns which is computed and has the persisted flag on it, so we can make an index on the column. It's really important that he subscriber has this column and index.

    We can't figure out a way to keep the column replicated and have the "Expired subscription clean up" agent job run successfully.

    We're also unsure of the implication of the agent job failing and what is it represents.

    Any ideas?

    Alex

    Alex

    Check what the required settings for ANSI_NULLS and QUOTED_IDENTIFIER are, then add a couple of SET statements at the beginning of the T-SQL command for your job.

    John

  • John Mitchell-245523 - Wednesday, January 25, 2017 5:06 AM

    alex.palmer - Wednesday, January 25, 2017 4:56 AM

    Hi

    The "Expired subscription clean up" agent job, which is part of the merge replication running on one of our SQL servers is failing with the message 

    "Executed as user: FI\svc_nonprod_sqlag. DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934). The step failed."

    We tracked the error down to one of the replicated columns which is computed and has the persisted flag on it, so we can make an index on the column. It's really important that he subscriber has this column and index.

    We can't figure out a way to keep the column replicated and have the "Expired subscription clean up" agent job run successfully.

    We're also unsure of the implication of the agent job failing and what is it represents.

    Any ideas?

    Alex

    Alex

    Check what the required settings for ANSI_NULLS and QUOTED_IDENTIFIER are, then add a couple of SET statements at the beginning of the T-SQL command for your job.

    John

    Thanks for the reply John

    Microsoft (https://msdn.microsoft.com/en-GB/library/ms189292.aspx) recommend the following settings to delete or update persisted, computed columns

    NUMERIC_ROUNDABORT OFF
    ANSI_NULLS ON
    ANSI_PADDING ON
    ANSI_WARNINGS ON
    ARITHABORT ON
    CONCAT_NULL_YIELDS_NULL ON
    QUOTED_IDENTIFIER ON

    The Agent job runs EXEC sys.sp_expired_subscription_cleanup
    This SP has 
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    This SP calls sp_MSdrop_expired_mergesubscription which I can't find in any system or user databases and sp_MScleanup_agent_entry

    sp_MScleanup_agent_entry has the ANSI_NULLS and Quoted_identifier set to off. 

    I'm not sure if this is the cause of the issue and if it is how to change it

    Alex

  • Alex

    It sounds as if it may be the cause, but then I wonder why a lot more people don't have this problem.  Are you on the latest service pack and CU?

    John

  • John Mitchell-245523 - Wednesday, January 25, 2017 7:04 AM

    Alex

    It sounds as if it may be the cause, but then I wonder why a lot more people don't have this problem.  Are you on the latest service pack and CU?

    John

    We're on 2008 SP4 (10.0.6000.29)

    So the latest for the antiqued version of SQL we're using

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply