SET QUOTED_IDENTIFIER option not reported correctly

• 
  Hello Alex,
  
  Is this similar to the way SQL Server 2000 handles stored procedures? In other words, if you create a stored procedure, SQL Server inserts the QUOTED_IDENTIFIER statement for you, without even asking.

  Brian Donahue April 10, 2006 13:06

  - 0 +
• 
  Hi Brian,
  
  No, its a bug in the way it reports the Quoted Identifier and ansi nulls options.
  f I create a table-valued function (note this is SQL Server 2000) with the QUOTED_IDENTIFIER and ANSI_NULLS options both set to ON e.g.
  

  SET QUOTED_IDENTIFIER ON
  GO
  SET ANSI_NULLS ON
  GO
  
  CREATE FUNCTION dbo.tfn_Test()
  RETURNS TABLE
  AS
  RETURN (SELECT 1 AS test)
  GO
  

  Then when Scripting this function out again (or using external tools to analyse it - SQL Compare for example), it is scripted as
  

  SET QUOTED_IDENTIFIER OFF
  GO
  SET ANSI_NULLS OFF
  GO
  CREATE FUNCTION dbo.tfn_Test()
  RETURNS TABLE
  AS
  RETURN (SELECT 1 AS test)
  GO
  

  
  It appears to run OK with the original settings, but obviously this script is incorrect.
  And if I run this script against my database :

  SELECT Name,
  Type,
  OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn') IsQuotedIdentOn,
  OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') IsAnsiNullsOn
  FROM sysobjects
  WHERE type IN ('FN','IF','TF')
  

  
  Then I get NULLs back for the ObjectProperty statements when the type is TF. The other function types return 1 or 0 as expected.
  This also means that I can't edit this Table Functions in SQL Server Management Studio as it returns an error, because it can't get at these options.
  
  I've put this on the Microsoft SQL Server forum, but not got a reply yet.
  
  Thanks
  Alex Weatherall
  TeleWare.com

  alex.weatherall April 11, 2006 04:42

  - 0 +
• 
  I was just wondering whether anyone at Red-gate has been able to reproduce this problem.
  
  It's an issue for us when using the product as we have functions that access indexed views which require both these options to be set on.
  
  When we synchronise any changes to these functions, the set options are lost, so we have to follow a manual process.
  
  Please let me know if you can reproduce the problem.
  
  Please also see bugs (FDBK47725) and (FDBK48533) in http://lab.msdn.microsoft.com/productfeedback/
  
  One is my bug submission, but the other is a related bug in SQL Server 2005 with Multi-Statment table valued functions.
  
  Thanks
  
  Alex Weatherall
  TeleWare.com

  alex.weatherall April 19, 2006 06:07

  - 0 +
• 
  I take it that's a no then :-(

  alex.weatherall May 03, 2006 09:32

  - 0 +
• 
  Alex,
  
  Sorry about the delay in replying. I'll ask somebody to take a closer look and get back to you.

  Neil Davidson May 03, 2006 12:25

  - 0 +
• 
  Thanks Neil,
  
  Microsoft are unlikely to fix the bug (see previous post), they said, but I was wondering if there was anything you might be able to do to work it out.
  
  I've noticed that the status column in sysobjects does have the 30th bit set with the SET ANSI_NULLS ON option on which seems to be the important one for use with indexed views, but OBJECTPROPERTY function doesn't report it.
  
  If nothing can be done with it, so be it, it's MS's fault, but it's really frustrating not to be able to rely on SQL Compare to migrate with these functions with the correct set options.
  
  Thanks
  
  Alex Weatherall

  alex.weatherall May 03, 2006 12:31

  - 0 +
• 
  Alex,
  
  We have reproduced the issue. We will try to get a fix in for this, but I am not sure what timescales will be at this time.
  
  Regards,
  
  Jonathan

  JonathanWatts May 03, 2006 12:43

  - 0 +
• 
  You are stars, that's cheered me up after one of those days :-)
  
  Alex

  alex.weatherall May 03, 2006 12:44

  - 0 +
• 
  This issue has now been fixed in SQL Compare V7.0 release.
  
  Eddie Davis

  Eddie D July 24, 2008 11:24

  - 0 +

Add comment

Please sign in to leave a comment.