locked
SELECT failed because the following SET options have incorrect settings: ANSI_PADDING RRS feed

  • Вопрос

  • I come across the following error message quite frequently whenever I have to reinitialize any of the subscribers. Even though I know how to resolve it but always wondered why it would be produced at the first place as offending script is auto-generated by snapshot agent.

     

    Error messages:

    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 query notifications and/or xml data type methods. (Source: MSSQLServer, Error number: 1934)
    Get help: http://help/1934

    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 query notifications and/or xml data type methods. (Source: MSSQLServer, Error number: 1934)
    Get help: http://help/1934

    As a resolution what I have to do is to go to snapshot folder and update sch file for the offending table; i.e; change SET ANSI_PADDING from OFF to ON in sch file and it resolves the error message. What I have observed, though, that ANSI_PADDING is always off for those tables which contain only numeric data types. That is how I suppose it should be as I see that setting is only relevant to char, varchar, binary, and varbinary data types.

     

    BOL: We recommend that ANSI_PADDING always be set to ON. In a future version of SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error.

     

    That explains why replication insist for having it ON but what I don't understand is that why Snapshot agent generated script have it as off?

     

    I have enabled ANSI_PADDING from database properties but it would make no difference whatsoever.

    13 февраля 2008 г. 11:02

Все ответы

  • I'm getting this same message on a newly created subscription. Can someone please help us?

     

    7 марта 2008 г. 13:08
  • Sounds like the settings differ between your publication and subscription.  Have you checked the properties of the tables on both servers?  When you set it on the database properties, that only impacts new tables you create afterwards, it does not change the setting on existing tables or views.  If you have a distribution server as well, I would also check that out.

     

    7 марта 2008 г. 18:47
  • I used the same workaround as Asaf... searched each of the .sch files in the snapshot folder and replaced SET ANSI_PADDING OFF with SET ANSI_PADDING ON. The distribution job now works fine. Like Asaf, I also wonder why the scripts were incorrectly auto-generated by snapshot creation in the first place.

    Some of the scripts to create the tables had ansi padding set to ON and some set it OFF

     

    Here is a sample of one I found and changed:

     

    drop Table [dbo].[SAMPLETABLE]
    go
    SET ANSI_PADDING OFF
    go

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[SAMPLETABLE](
     [session_id] [int] NOT NULL,
     [obs_id] [int] NOT NULL
    )

    GO

     

    Here is a sample that was ok to begin with:

     

    drop Table [dbo].[DASH]
    go
    SET ANSI_PADDING ON
    go

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[DASH](
     [dash_id] [int] NOT NULL,
     [dash_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     [user_id] [int] NULL,
     [table_name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [fk_id] [int] NULL,
     [lock_filter_flag] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     [max_rows_per_portlet] [int] NULL,
     [dashboard_layout_data] [varchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [portlet_settings_data] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [update_date] [datetime] NULL,
     [update_user] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [create_date] [datetime] NULL,
     [create_user] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [delete_session_id] [int] NULL,
     [delete_date] [datetime] NULL
    )

    GO

    The ANSI Padding connection property on both the distributor and subscriber servers is not checked on and the ANSI Padding Enabled property of each of the databases is set to False.

    7 марта 2008 г. 19:34
  • Can you crack open the procs on the publisher to see if perhaps they were created with this setting off there?

     

    If so you will need to recreate them with it off. IIRC the default in SQL 7 was off, but in SQL 2000 it was on.

    7 марта 2008 г. 21:33
    Отвечающий
  • Also can you file a connect bug with the table script and the replication scripts used to create the publication? We will take a look at this. This looks like a snapshot bug. Also please post the version of the distributor and publisher you are using

    • Снята пометка об ответе Asaf Mohammad 16 марта 2009 г. 14:54
    8 марта 2008 г. 19:29
  • I am publishing from a database set at SQL Server 2000 (80) Compatibility level on a SQL Server 2005 (9.0.3215) server

    to a database set at SQL Server 2000(80) Compatibility level on a SQL Server 2005 (9.0.3215) server. We are in the process of migrating to SQL 2005 and this is the first application/database we have attempted. It has one issue with 2005 so we are leaving the database at 2000 compatibility level until the application vendors "fix" the issue. We run a snapshot replication once daily to another server for ad hoc querying/reporting purposes. We are also doing a daily snapshot of the same database/publication to the old SQL 2000 server and it is running without errors.

     

    My workaround has not worked all weekend apparently because the scripts are recreated each time the snapshot runs and the create table script on some of the tables continues to set ansi padding OFF -

     

    drop Table [dbo].[DLTACCT]
    go
    SET ANSI_PADDING OFF
    go

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[DLTACCT](
     [session_id] [int] NOT NULL,
     [acct_id] [int] NOT NULL
    )

    GO

     

    Here is the error message in the job history

     

    Date  3/10/2008 5:00:02 AM
    Log  Job History (Reports_Publicatio-NEW-3)

    Step ID  2
    Server  SvrName
    Job Name  Reports_Publicatio-NEW-3
    Step Name  Run agent.
    Duration  00:09:07
    Sql Severity  0
    Sql Message ID  0
    Operator Emailed  
    Operator Net sent  
    Operator Paged  
    Retries Attempted  0

    Message
    2008-03-10 09:08:48.638 Applied script 'COSTTYPE_11.sch'
    2008-03-10 09:08:56.248 Applied script 'DASHBOARD_13.sch'
    2008-03-10 09:09:00.357 Applied script 'DASHUSER_14.sch'
    2008-03-10 09:09:05.826 Agent message code 1934. 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 query notifications and/or xml data type methods.
    2008-03-10 09:09:05.951 Category:COMMAND
    Source:  Failed Command
    Number: 
    Message: CREATE TABLE [dbo].[DLTACCT](
     [session_id] [int] NOT NULL,
     [acct_id] [int] NOT NULL
    )


    2008-03-10 09:09:05.951 Category:NULL
    Source:  Microsoft SQL Native Client
    Number:  1934
    Message: 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 query notifications and/or xml data type methods.

    The above error also shows that some of the tables are created just fine... note that ansi padding is set ON

     

    drop Table [dbo].[COSTTYPE]
    go
    SET ANSI_PADDING ON
    go

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[COSTTYPE](
     [cost_type_id] [int] NOT NULL,
     [seq_num] [int] NOT NULL,
     [cost_type] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     [update_date] [datetime] NULL,
     [update_user] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [create_date] [datetime] NULL,
     [create_user] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [delete_session_id] [int] NULL,
     [delete_date] [datetime] NULL
    )

    GO

     

    10 марта 2008 г. 14:49
  • What now? I filed a connect bug, but haven't heard anything since... Replication is still failing.

     

    13 марта 2008 г. 17:35
  • This is the same error which i get all the time.

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2681793&SiteID=1

     

    no solutions so far !!

    24 марта 2008 г. 6:49
  • The last I heard was from the Microsoft Connect team on 3/13/08 - "We are investigating this. Thanks." Meanwhile, I am in a holding pattern on migrating two of our servers from SQL 2000 to 2005. Ugh.

    24 марта 2008 г. 13:18
  • Whats your bug id number?

    lemme vote for it to boost priority

     

    24 марта 2008 г. 21:20
  •  

    Quite recently we had to speak to Microsoft Technical support about some other replication issues we were having and during that I mentioned ANSI PADDING related error message as well. The only feedback I had was that this issue was under investigation by their development team and likely fix for it would be in next service pack sometime in the future.

     

     

    24 марта 2008 г. 21:58
  • They didn't give me a bug id number... just a feedback id - it's 332573.

     

    25 марта 2008 г. 11:49
  • I'm going to try a pull subscription to see if that works... will let you all know. I've already got a smaller one running from a 2000 server and it works fine. The only issue is the dev reporting server is not very robust - it has problems handling the load on the processor. Will give it a shot though...

     

    25 марта 2008 г. 11:54
  • Well, that failed miserably. I tried several different attempts at creating a pull subscription and kept getting the following error when it tried to run

     

    'U:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData\unc\ANOtherSrverName_PublName\ 20080325090104\WKFLUSER_166.pre' due to OS error 3.

     

    I manually changed the snapshot path property to a UNC to try to fix it... since the subscription creation for some reason "thought" the publication was in a local directory. That didn't work either.

     

    Wow. Looks like there is more than one issue with the automated creation of snapshot replication in SQL Server 2005.

    25 марта 2008 г. 13:19
  • The original issue is most likely a replication bug, if this is a blocking production issue you can call technical support and try to push for a QFE, otherwise it might get addressed in a future Service Pack, but cannot make any promises.  At the same time see if you can work around the issue by changing the database compat level at the subscriber to 90 (just a suggestion, i have no idea if that will fix it). 

     

    For the pull subscription issue, it's using local path to snapshot files because that's how you set up the publication, you didn't say what the error was after changing it to a UNC path.

    26 марта 2008 г. 15:36
  • Ok... after reading the "fine print" I saw in setup where you need to enter a UNC for the publication path. Thanks. But, I didn't use a UNC during the setup of a 2000 database publication, with a pull subscription on the same server as the 2005 pull and it works just fine. Hmmm.

     

    Anyway the pull connected just fine. I tried setting the Compatiblity level on the subscription database to 2005. The distribution failed with the same error...

     

    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 query notifications and/or xml data type methods.

     

    One note - it ran just fine the first time during the setup. Only when I scheduled it and it ran, did it fail with the above error. So the .sch files seem to be the only culprits with their irritating inconsistent setting of ANSI PADDING OFF.

    27 марта 2008 г. 14:22
  • If your pull subscription is on the same box as the distributor, then local path or UNC path will work.  If it's on a separate machine, then local path will never work. But if it's push subscription, then it should work just fine since the agent is running on the distributor, which has access to the local path.

     

     

    27 марта 2008 г. 15:46
  • Has there been any update on this ANSI_PADDING issue?  I had a perfectly working publication to which I added some tables and now I get the ANSI_PADDING error on an Insert.

     

    Thanks

    30 июля 2008 г. 21:40
  • About the SET options, this is an issue that we cannot figure out as we don't have a solid repro, if this is blocking production I suggest opening case with CSS so an engineer can debug the issue.

    30 июля 2008 г. 21:44
  • What version of SQL is running on your server(s)?

     

    We just got back around to testing this again and have successfully implemented and scheduled a snapshot replication from a 2005 (2000 compatibility level) database to a 2005 database on another server. None of the .sch files set ANSI Padding OFF... so it is working. About the only difference I can think of between now and the last time we tested is that we are now updated to version 9.0.3233 ( 2005 (QFE) SP2+Q941203 / 948108 ). In March we were at 9.0.3215 (sp2 + Q941450 - Cumulative update 5)

     

    Whew... now maybe we can migrate our reporting servers to SQL 2005.

     

    31 июля 2008 г. 12:51
  •  

    Got this figured out. There was a DDL trigger on the replicated database. It was passing dynamic SQL which, according to what I found reseaching, causes SQL Server to check permissions in the security context of the user who executed the procedure instead of the procedure owner. So a break in the ownership chain was causing the distribution to fail. Once the trigger was removed, distribution worked fine.

    5 августа 2008 г. 13:01
  • Please open a case with customer support so an engineer can debug your scenario.

    5 августа 2008 г. 14:31
  • I had the same issue.

    DDL Trigger was set on the DB for all Schema changes and made this fail.

    This must be a bug? Surely DDL Triggers should not interfere with the settings that are being applied to the DB?
    2 октября 2008 г. 15:32
  • It was not technically the DDL trigger that was the issue - it is the stored procedure ( which uses dynamic sql ) within my trigger that broke the ownership chain. I am looking into signing stored procedures with a certificate as my solution.This article titled "Security Through Ownership Chains" may help you determine exactly what your issue may be

    http://www.sqlmag.com/Articles/ArticleID/25145/pg/3/3.html

     

    Good luck!

    2 октября 2008 г. 17:22
  • We are looking into the issue, It will be really helpful if everyone who hits this issue can post a simple one table repro script including the table schema, DDL trigger creations script and the replication setup (just to understand the options being set) through the connect website. http://connect.microsoft.com/

     

     

    3 октября 2008 г. 14:36
  • Gopal,

     

    I was wondering if there has been any progress related to ANSI_PADDING problem?

     

    Thank you

    • Предложено в качестве ответа Janmaurer 10 февраля 2009 г. 13:33
    • Отменено предложение в качестве ответа Asaf Mohammad 16 марта 2009 г. 10:28
    • Изменено Asaf Mohammad 16 марта 2009 г. 14:53 none
    4 декабря 2008 г. 15:01
  • I was having the same problem.  I think I have a solution so that I can keep the DDL trigger.  Simply specify SET ANSI_PADDING ON at the beginning of the DDL trigger.  I think the trigger was inheriting the settings from the REPLICATED table.  This was causing the trigger and the reinitialization to fail.
    10 февраля 2009 г. 13:37
  • i have a database with 1000+ tables, and more than half of these are showing up with SET ANSI_PADDING OFF in the schema files. is there any resolution/workaround?
    17 сентября 2009 г. 21:10
  • I've searched through all 10 of the SQL Server 2005 cumulative updates for SP3 and this bug is not mentioned so I guess this has not yet been fixed.

    Can anybody advise any kind of 'fix' other than the workaround of manually updating the *.sch files with SET ANSI_PADDING ON ?

    Would really appreciate anything anybody can suggest!

    30 июля 2010 г. 9:29
  • I can suggest two workarounds which can be used:

    1.) Manually update the *.sch files in the Publisher Replication snapshot directory; Instead of 'SET ANSI_PADDING OFF', manually change this to 'SET ANSI_PADDING ON'

    2.) I added two extra steps to the Subscriber Replication job where a Database DDL Trigger was present, this is the actual problem I believe;

         a.) DISABLE TRIGGER <schema>.<triggername> ON DATABASE
         b.) Subscriber Replication step
         c.) ENABLE TRIGGER <schema>.<triggername> ON DATABASE;

    The whole Replication process now runs fine.
    • Предложено в качестве ответа SQL Swerver 3 августа 2010 г. 8:44
    3 августа 2010 г. 8:44
  • To automate first approach suggested by 'SQL Swerver', we include the following PowerShell script in each of our snapshot jobs right after 'run agent' step;

    # Purpose : Find any occurrence of ''SET ANSI_PADDING OFF'' in *.sch files
    #    generated by snapshot agent and replace it with ''SET ANSI_PADDING ON'' which
    #   is the required setting for replication
    # Comments: Script would loop through specified folder and find all files with sch extension
    #    in current folder and its sub-folders. It would be added as third step in each
    #   snapshot job created by default whenever publication is created.
    # Script  : PowerShell 1.0

    foreach ($file in get-Childitem \\ServerName\ReplSnapshot -include *.sch -recurse |
     Sort-Object extension, length -descending)
    {
        $file.fullname + "`t " + $file.name + "`t " +$file.length + "`t " +    $file.LastAccessTime

        (Get-Content $file.fullname) |
        Foreach-Object {$_ -replace "SET ANSI_PADDING OFF", "SET ANSI_PADDING ON"} |
        Set-Content $file.fullname
    }

    Please make sure you change \\ServerName\ReplSnapshot to point to the snapshot directory according to your own configuration.

    Hope it helps.

    Asaf

    11 октября 2010 г. 13:38
  • I come across the following error message quite frequently whenever I have to reinitialize any of the subscribers. Even though I know how to resolve it but always wondered why it would be produced at the first place as offending script is auto-generated by snapshot agent.

     

    Error messages:

    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 query notifications and/or xml data type methods. (Source: MSSQLServer, Error number: 1934)
    Get help: http://help/1934

    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 query notifications and/or xml data type methods. (Source: MSSQLServer, Error number: 1934)
    Get help: http://help/1934

    As a resolution what I have to do is to go to snapshot folder and update sch file for the offending table; i.e; change SET ANSI_PADDING from OFF to ON in sch file and it resolves the error message. What I have observed, though, that ANSI_PADDING is always off for those tables which contain only numeric data types. That is how I suppose it should be as I see that setting is only relevant to char, varchar, binary, and varbinary data types.

     

    BOL: We recommend that ANSI_PADDING always be set to ON. In a future version of SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error.

     

    That explains why replication insist for having it ON but what I don't understand is that why Snapshot agent generated script have it as off?

     

    I have enabled ANSI_PADDING from database properties but it would make no difference whatsoever.

    The suggestion above to set ANSI_PADDING ON on the sch file of the replicated table in problem fixed mine.

    Thanks.

    13 декабря 2010 г. 10:43
  • Thanks Asaf - the PS script worked nicely.  Many many thanks!
    13 февраля 2012 г. 22:50
  • SO...... apparently this has never been resolved?  

    Am I going to be requried to open a ticket to get an answer? 

    9 августа 2012 г. 20:47
  • I am still having this issue too. My scenario involves SQL 2008 R2 to SQL 2008 transactional replication. I also have a DDL trigger that catches all DDL events and logs them.

    Does anybody have a Connect ID so we can follow up with Microsoft on this?

    Editing the .sch files every time isn't a solution IMHO.

    Thanks.

    EDIT: I found the answer: Microsoft has closed the connect item with a code of "Won't fix"
    http://connect.microsoft.com/SQLServer/feedback/details/334194/ansi-padding-options-in-replication-snapshot

    • Изменено Jeff Cox 19 октября 2012 г. 14:03
    19 октября 2012 г. 13:57