Share via

SQL server 2017 replication issue on particular tables and view

tgt-1055 46 Reputation points
2021-11-13T11:31:55.82+00:00

Hi,

We got below error in our prod environment when added 2 more tables and 1 view to replicate to another server in existing replication setup. We tested out that scenario in our staging and dev environment and no issue occurred.

We are not sure it's happened because of object schema or data issue. Please help advise how we can overcome below issue.

2021-11-13 10:46:30.094 Connecting to Subscriber 'ServerName'
2021-11-13 10:46:30.125 Initializing
2021-11-13 10:47:27.766 Skipping file 'ORC_MTL_ONHAND9ce349e7_148.pre' because it has already been delivered for a previous article or by a previously interrupted snapshot.
2021-11-13 10:47:27.766 Skipping file 'ORC_MTL_ONHANDa7c3e34f_116.pre' because it has already been delivered for a previous article or by a previously interrupted snapshot.
2021-11-13 10:47:27.781 Skipping file 'ORC_MTL_ONHANDc7c3e34f_115.pre' because it has already been delivered for a previous article or by a previously interrupted snapshot.
2021-11-13 10:47:27.828 Agent message code 1934. INSERT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. 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.
2021-11-13 10:47:27.860 Category:COMMAND
Source: Failed Command
Number:
Message: create procedure [sp_MSins_dboORC_MTL_ONHAND_QUANTITIES_1]
@c1 numeric(15,0),
@c2 int,
@c3 int,
@c4 datetime2,
@c5 datetime2,
@c6 int,
@c7 datetime2,
@c8 int,
@c9 numeric(38,0),
@c10 nvarchar(10),
@c11 int,
@c12 int,
@Curtis Asare int,
@c14 datetime2,
@c15 int,
@c16 numeric(38,0),
@c17 numeric(38,0)
as
begin
insert into [dbo].[ORC_MTL_ONHAND_QUANTITIES_1] (
[MTL_ONHAND_QUANTITIES_KEY],
[INVENTORY_ITEM_ID],
[ORGANIZATI
2021-11-13 10:47:27.860 Category:NULL
Source: Microsoft SQL Server Native Client 11.0
Number: 1934
Message: INSERT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. 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.

Thanks & Best Regards,
Theingi

SQL Server | Other
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2021-11-13T12:55:36.217+00:00

    As the error message says, the setting ANSI_PADDING must be ON for certain features.

    If this did not happen in dev or staging, it could because the setting was ON in these environments. It could also be that no feature that requires ANSI_PADDING to be on is present in these environments.

    These features requires ANSI_PADDING to be ON:

    • Indexed views.
    • Indexes on computed columns.
    • Filtered indexes.
    • XQuery and xml type methods.
    • Spatial indexes.

    Note that the error occurs at compile time, so if the procedure sp_MSins_dboORC_MTL_ONHAND_QUANTITIES_1 has a statement that makes an update to a table with, say, a filtered index, you will get this error, even if the logic in the procedure precludes this statement from being executed.

    The setting ANSI_PADDING OFF is a legacy setting, which should be avoided. The default setting is ON.

    ANSI_PADDING can be OFF because you have executed the statement SET ANSI_PADDING OFF. If so, stop doing that.

    But the setting is also saved by table columns of the types varchar and varbinary. You can use this column to find if you any such columns in your database: 

    SELECT s.name AS SchemaName, o.name AS TableName, c.name AS ColumnName
FROM   sys.columns c
JOIN   sys.objects o ON c.object_id = o.object_id
JOIN   sys.schemas s ON s.schema_id = o.schema_id
WHERE  type_name(c.system_type_id) LIKE 'var%'
  AND  c.is_ansi_padded = 0
    1 person found this answer helpful.
  2. Seeya Xi-MSFT 16,586 Reputation points
    2021-11-15T08:04:18.453+00:00

    Hi @tgt-1055 ,

    Can you reinitialize Subscription? Right-click Configured publication and select Reinitialize all Subscription.
    Agree with Erland.
    Instead of 'SET ANSI_PADDING OFF', manually change this to 'SET ANSI_PADDING ON'
    If it still doesn't work, could you upgrade your sql server to 2019 and the latest CU is CU13.

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.