none
Ansi_Nulls Off in some replication insert/update/delete procedures, not all

    Question

  • Hello, I am in the process of migrating off an old SQL 2000 system and I have done some compares of the objects in the newer system which is 2008 R2 and I am finding some, not all of the sp_MS insert update and delete procs on the subscriber have the SET ANSI_NULLS OFF on my test system (subscriber which is 2008 r2 in test) where as on the production (again subscriber is 2008 r2) there is no ANSI_NULL setting when you script out the procs. I am using Red-Gate SQL Compare to verify the differences. Any insight appreciated.
    Thursday, May 24, 2018 9:39 PM

Answers

  • Script out the tables for the problem procs on the publisher. You will likely find that they have the ANSI_NULLS set to off for these tables.

    • Marked as answer by Abeljdang Thursday, June 21, 2018 8:00 PM
    Tuesday, May 29, 2018 2:37 PM
    Moderator

All replies

  • Hi Abeljdang,

    In your scenario, you have two systems, test and production which both contain a 2008 r2 subscriber. And you are migrating a SQL 2000 system. Right?

    Then did you mean that you have migrated this SQL 2000 system to both test and production?

    For the SET ANSI_NULLS, you should know that it is used in SQL Server 2008 and newer. It also means that there's no SET ANSI_NULLS in SQL Server 2000.

    Please refer to this article: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-2017

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 25, 2018 6:48 AM
    Moderator
  • yes I am in the process of migrating the 2000 distributor to 2008 r2 then shortly on to current version. I have set up a test restoring the replicated db from the prod subscriber (which is 2008). I was not involved but I am sure that the prod subscriber db was restored from a SQL 2000 backup some time ago. Now, when I drop and recreate the subscriptions with my SQL 2008 r2 distributor and 2008 r2 subscriber, it has the set ansi_nulls off on some, again not all but a good number of the replication procs. I am just trying to understand why/where that comes from as it is not in my scripts. I have used the function  OBJECTPROPERTY on all of these procs and verified there are differences, where in the replication set up might this have happened? 

    Thanks

    Friday, May 25, 2018 8:22 PM
  • Script out the tables for the problem procs on the publisher. You will likely find that they have the ANSI_NULLS set to off for these tables.

    • Marked as answer by Abeljdang Thursday, June 21, 2018 8:00 PM
    Tuesday, May 29, 2018 2:37 PM
    Moderator
  • Thanks for the reply I will follow up when I get this resolved, redirected to another task at this time.
    Wednesday, May 30, 2018 4:18 PM
  • Hilary, this is true that the publisher tables are have ansi nulls off.  I do not understand why on the my test set up, the subscriber stored procedures for delete/insert/updtae have the "SET ANSI_NULLS OFF"  in their definition  and do not on the production subscriber. I am seeing this difference in the Red-Gate sql compare tool. What I am wondering is does it even matter since the subscriber tables have the setting of on their definition, and this as I understand it is a connection setting so it will be set when the agent account performing the replication calls these procedures? Is there anywhere I can verify what the existing agent is using compared to what my test agent is using? Thanks for your time
    Thursday, June 21, 2018 8:29 PM