January 25, 2017 at 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
January 25, 2017 at 5:06 am
alex.palmer - Wednesday, January 25, 2017 4:56 AMHiThe "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
January 25, 2017 at 6:57 am
John Mitchell-245523 - Wednesday, January 25, 2017 5:06 AMalex.palmer - Wednesday, January 25, 2017 4:56 AMHiThe "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
January 25, 2017 at 7:04 am
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
January 25, 2017 at 8:06 am
John Mitchell-245523 - Wednesday, January 25, 2017 7:04 AMAlexIt 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