Can''t set QUOTED_IDENTIFIER or ANSI_NULLS on mult-statement table valued function

• Chad Michael

  Old Hand

  Points: 367

  More actions

  January 10, 2006 at 12:03 pm

  #92552

  I can't get the following to "stick" on my mutl-statement tvfs.

  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_NULLS ON

  GO

  When I run OBJECTPROPERTY(id,'ExecIsAnsiNullsOn') AS 'ExecIsAnsiNullsOn' or OBJECTPROPERTY(id,'ExecIsQuotedIdentOn') AS 'ExecIsQuotedIdentOn' I basically get NULL for both. Could someone try this on a SQL Server 2000 (I've tried it on sp1 and sp4 but both behave the same). Let me know if your's shows NULL for ExecIsAnsiNullsOn and ExecIsQuotedIdentOn. Of course if you know what's going on, I would love to get the resolution. Couldn't find anything about this in google except that it "should" be working.

  Thanks,

  -Chad

  --*******************

  --Code to reproduce my problem (Note the NULL for the set options. I have isolated this problem to only my multi-statement tvfs. All other objects respect the set options.)

  --*******************

  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_NULLS ON

  GO

  CREATE  FUNCTION FN_TestQI_AN(@nParm1 INT)

   RETURNS @tResults TABLE (IDRow INT)

   AS

   BEGIN

    DECLARE @nSomeVar INT

    SELECT @nSomeVar = 10

   

     INSERT INTO @tResults SELECT 100

   

    RETURN

   

   END

  GO

  SELECT  id, name, xtype,

     OBJECTPROPERTY(id,'ExecIsAnsiNullsOn') AS 'ExecIsAnsiNullsOn',

     OBJECTPROPERTY(id,'ExecIsQuotedIdentOn') AS 'ExecIsQuotedIdentOn'

  FROM   sysobjects WHERE xtype IN ('P','TR','V','IF','FN','TF')

     AND name = 'FN_TestQI_AN'

• noeld

  SSC Guru

  Points: 96590

  More actions

  January 10, 2006 at 1:12 pm

  #613915

  Definitely you have hit a bug in SS.

  As far as I can tell the status bit IS updated correctly but the code generator is NOT generating the appropriate value

  Also I don't think M$ has acknowledged it on their KB neither

   

  
  * Noel