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


  • 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.
    Donnerstag, 24. Mai 2018 21:39

Alle Antworten

  • 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:

    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

    Freitag, 25. Mai 2018 06:48
  • 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? 


    Freitag, 25. Mai 2018 20:22
  • 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.

    Dienstag, 29. Mai 2018 14:37
  • Thanks for the reply I will follow up when I get this resolved, redirected to another task at this time.
    Mittwoch, 30. Mai 2018 16:18