ANSI NULL difference confusion in RC1

• 

  PDinCA wrote:

  Both instances (one is SS2000, the other SS2008 RTM) are set for connections: ANSI NULL DEFAULT ON. Both Databases have ANSI NULL ON. ALTER UDF Script has explicit SET ANSI_NULLS ON and just to be paranoid I ran the script on both (test) databases and refreshed SQL Compare RC1.
  
  WHY does it persist in showing me that the only "difference" between the 2 is that is has the first 2 lines in the SS2000 script as:
  
  SET ANSI_NULLS OFF
  GO
  
  Huh?
  
  What am I missing?
  
  This is the only difference for 20 of my UDFs, but I have 200 others that are identical and they have the same SET ANSI_NULLS ON in their scripts.
  
  Maybe this turns out to not be a SQL Compare issue but I'm at a loss as to know where else to look...
  
  Thanks for your "indulgence".

  
  Do you have SQL Compare 6 installed? Does it behave the same way? Also, when you script the object from Management Studio, does it display the correct ANSI_NULLS setting? Could you also send me in a private message oen of these functions please? Or whether it is a multistatement, inline, or scalar function.
  
  Regards,
  Andras

  Andras B February 16, 2009 03:48

  - 0 +
• 
  SQL Compare 6 has never been installed on this, new, box. Do you need it to be?
  
  SSMS now generates scripts in the intensely annoying form of a text string, rather than how we code them normally - thanks MS (idiots!). However, I'll PM you the file for the first UDF differenced, along with my actual file. There's no ANSI_NULLS statement in the generated script from SSMS. UDF is very much a multistatement table-value function.

  PDinCA February 16, 2009 12:49

  - 0 +
• 
  Thanks for getting back to me. The problem is that there is a bug in SQL Server 2000 concerning multi statement functions, and SET ANSI_NULLS for such functions is ignored by SQL Server. You can try this by creating such a function, and querying the system tables on 2000
  
  select objectpropertyex(object_id('theNameOfMyMultiStatementTableFunction'), 'ExecIsAnsiNullsOn' )
  
  This will be returning nulls
  
  There is an option for ignoring Quoted identifiers and ANSI_NULL statements in SQL Compare as a temporary workaround.
  
  Regards,
  Andras

  Andras B February 17, 2009 06:00

  - 0 +
• 
  Thanks, Andras, I was unaware of the SS2000 issue and will turn off the ANSI NULL option in Compare. Thankfully we should be off 2000 inside 2 months

  PDinCA February 17, 2009 11:51

  - 0 +

Add comment

Please sign in to leave a comment.