Snippet Name: Set_ANSI

Tags:

Created Date: 2025-06-16 13:12:39
Last Modified Date: 2025-05-31 10:31:35

Dependencies:

DBA Notes:

Sample Output:

Title: Set ANSI_PADDING ON
Author: user
i SQL server 2019 i am rebuilding a replication from a database that existed previously.  I scripted out the replication via the SSMS GUI,, did an application upgrade, and when ran the  script to recreate the replication successfully. however, now I am getting the following error on the subscriber: SELECT 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.

Author: ChatGPT
The error you're encountering indicates that the `ANSI_PADDING` setting is not configured correctly on the subscriber database, which is crucial for certain database operations, especially those involving indexed views, computed columns, filtered indexes, query notifications, XML data type methods, and spatial index operations.

### Steps to Resolve the Error:

1. **Verify and Set ANSI_PADDING:**
   Ensure that `ANSI_PADDING` is set to `ON` in your session and for the entire database when dealing with tables, indexed views, computed columns, and other specific operations. Here???s how to check and set it correctly:

   **Session Level:**
   ```sql
   SET ANSI_PADDING ON;
   ```

   **Database Level:**
   To set `ANSI_PADDING` at the database level, you may need to re-create the table or the objects in question because `ANSI_PADDING` is a setting that affects the creation of the table or index.

   If you want to ensure that new connections to the database have `ANSI_PADDING` set to `ON`, you can create a logon trigger:

   ```sql
   CREATE TRIGGER SetAnsiPaddingOn
   ON ALL SERVER
   FOR LOGON
   AS
   BEGIN
       SET ANSI_PADDING ON;
   END;
   ```

   However, using triggers to enforce session settings is generally not recommended for production environments.

2. **Re-create the Objects with Correct Settings:**
   If the objects (like indexed views or tables with computed columns) were created with `ANSI_PADDING` set to `OFF`, they need to be re-created with `ANSI_PADDING` set to `ON`.

   Here???s a general example of how to do this:

   ```sql
   SET ANSI_PADDING ON;
   GO

   -- Re-create the table or indexed view
   CREATE TABLE YourTableName (
       Column1 INT,
       Column2 VARCHAR(100) -- Example column
       -- Other columns and constraints
   );

   -- Or for indexed views and other objects, use the appropriate CREATE statement
   ```

3. **Replication Initialization:**
   Reinitialize the replication after ensuring that `ANSI_PADDING` is correctly set. You can use the SSMS GUI or T-SQL commands to reinitialize the subscription.

   ```sql
   -- To mark subscription for reinitialization
   EXEC sp_reinitmergepullsubscription 
       @publisher = 'PublisherName',
       @publisher_db = 'PublisherDBName',
       @publication = 'PublicationName',
       @subscriber = 'SubscriberName',
       @subscriber_db = 'SubscriberDBName';
   ```

4. **Check Database Settings:**
   Ensure the database options for both the publisher and the subscriber databases are correctly set to support replication with the necessary `SET` options.

   ```sql
   USE YourDatabaseName;
   GO

   EXEC sp_dboption 'YourDatabaseName', 'ansi_padding', 'ON';
   ```

### Steps to Diagnose Further:

1. **Check SET Options at Runtime:**
   You can use the following query to check the current settings for a session:

   ```sql
   DBCC USEROPTIONS;
   ```

2. **Check Object Creation Settings:**
   Inspect how the objects are created in the publisher and ensure the same settings are replicated on the subscriber.

   ```sql
   SELECT name, is_ansi_padding_on
   FROM sys.columns
   WHERE object_id = OBJECT_ID('YourTableName');
   ```

3. **Ensure Compatibility Levels:**
   Ensure the compatibility level of the databases on both the publisher and subscriber are appropriate for SQL Server 2019.

   ```sql
   USE YourDatabaseName;
   GO

   EXEC sp_dbcmptlevel 'YourDatabaseName';
   ```

### Final Note:
After ensuring `ANSI_PADDING` is set correctly and the objects are re-created as needed, you should be able to reinitialize the replication without encountering the `ANSI_PADDING` error.

If you continue to experience issues, consider examining the replication monitor for more detailed error messages or further clues on what might be misconfigured.

Author: user
would that error be introduced if i did not set ansi padding on when i ran the script to create the replciaiton?

Author: ChatGPT
Yes, it's very likely that the error was introduced because the `ANSI_PADDING` setting was not properly configured when the replication was initially set up or when the replication script was executed. 

When you create objects like tables, indexed views, or other schema-bound objects without setting `ANSI_PADDING` explicitly, SQL Server will use the default setting for the session, which might not always be what you expect. If `ANSI_PADDING` was `OFF` when the objects were created, and if the replicated database requires `ANSI_PADDING` to be `ON` for certain operations, you'll encounter issues like the one you described when those operations are attempted on the subscriber.

So, it's crucial to ensure that the `ANSI_PADDING` setting is consistent and appropriate both during the initial setup of objects and during replication. If the objects were created with `ANSI_PADDING` set to `OFF`, you should recreate them with `ANSI_PADDING` set to `ON`, and then reinitialize the replication.

Author: user
Replication-Replication Distribution Subsystem: agent vmprodwdb001-NLS-TR-VMPRODWDB001-NLS-VMPRODWDBREP001-35 failed. SELECT 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 t

Author: ChatGPT
The error message you're encountering indicates that the replication agent `vmprodwdb001-NLS-TR-VMPRODWDB001-NLS-VMPRODWDBREP001-35` failed due to incorrect settings of the 'ANSI_PADDING' option. This error typically occurs when the replication process attempts to execute a SELECT statement on the subscriber side and encounters issues related to the 'ANSI_PADDING' setting.

Here's a step-by-step guide to troubleshoot and resolve this issue:

1. **Verify ANSI_PADDING Setting:**
   First, confirm whether the ANSI_PADDING setting is correctly configured on the subscriber database. You can check this by executing the following query on the subscriber database:

   ```sql
   SELECT DATABASEPROPERTYEX('YourDatabaseName', 'IsAnsiPaddingEnabled') AS AnsiPaddingEnabled;
   ```

   Ensure that the result is '1', indicating that ANSI_PADDING is enabled. If it's not enabled, you need to enable it.

2. **Enable ANSI_PADDING:**
   If ANSI_PADDING is not enabled on the subscriber database, you can enable it using the following command:

   ```sql
   ALTER DATABASE YourDatabaseName SET ANSI_PADDING ON;
   ```

   This command ensures that ANSI_PADDING is enabled for the entire database, which is important for maintaining consistency across all objects.

3. **Reinitialize the Replication:**
   After enabling ANSI_PADDING on the subscriber database, you should reinitialize the replication to ensure that the changes are propagated correctly. You can use the SSMS GUI or execute the appropriate T-SQL commands to reinitialize the replication.

4. **Check for Object Dependencies:**
   Review the objects involved in replication, such as tables, indexed views, or computed columns, and ensure that they are created with the correct ANSI_PADDING setting. If any objects were created with ANSI_PADDING set to OFF, consider recreating them with ANSI_PADDING set to ON.

5. **Review Replication Agent Configuration:**
   Ensure that the replication agent configuration is correct and consistent with the settings of the publisher and subscriber databases. Review the replication agent job properties and verify that the connection settings and options are configured appropriately.

6. **Monitor Replication Log for Errors:**
   Monitor the replication log for any additional errors or warnings related to the ANSI_PADDING setting. This can provide further insights into the specific operations or queries that are encountering issues due to ANSI_PADDING settings.

By following these steps and ensuring that ANSI_PADDING is correctly configured on the subscriber database and all relevant objects, you should be able to resolve the replication failure related to incorrect ANSI_PADDING settings.