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'