Problem with SET QUOTED_IDENTIFIER ON

Hi,
I am creating User defined function with
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
But function is created with QUOTED_IDENTIFIER OFF and SET ANSI_NULLS OFF.
What is wrong.
ThanksHi,
look here, Iposted that some time ago:
http://forums.microsoft.com/MSDN/Sh...228076&SiteID=1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||How do you know the settings are OFF? What version of SQL Server? The
following works for me under SQL 2000:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.TestFunction(@.Parameter1 int)
RETURNS int
AS
BEGIN
RETURN @.Parameter1
END
GO
SELECT
OBJECTPROPERTY(OBJECT_ID('dbo.TestFunction'), 'ExecIsAnsiNullsOn'),
OBJECTPROPERTY(OBJECT_ID('dbo.TestFunction'), 'ExecIsQuotedIdentOn')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"AMiha" <amiha@.hotmail.com.false> wrote in message
news:urdlGKmTGHA.5496@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I am creating User defined function with
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> But function is created with QUOTED_IDENTIFIER OFF and SET ANSI_NULLS
> OFF.
> What is wrong.
> Thanks
>|||I'm working with sql 2000 and result of
SELECT
OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'ExecIsAnsiNullsOn'),
OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'ExecIsQuotedIdentOn')
GO
is null for myUdf.
Result of
select OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'IsTableFunction')
is 1.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e7DFkFnTGHA.5900@.tk2msftngp13.phx.gbl...
> How do you know the settings are OFF? What version of SQL Server? The
> following works for me under SQL 2000:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE FUNCTION dbo.TestFunction(@.Parameter1 int)
> RETURNS int
> AS
> BEGIN
> RETURN @.Parameter1
> END
> GO
> SELECT
> OBJECTPROPERTY(OBJECT_ID('dbo.TestFunction'), 'ExecIsAnsiNullsOn'),
> OBJECTPROPERTY(OBJECT_ID('dbo.TestFunction'), 'ExecIsQuotedIdentOn')
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AMiha" <amiha@.hotmail.com.false> wrote in message
> news:urdlGKmTGHA.5496@.TK2MSFTNGP11.phx.gbl...
>|||The 'sticky' SET options for table valued functions are apparently not
reported correctly in SQL 2000 SP4. The create-time settings are used for
execution though. No problem in SQL 2005.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.myTableFunction(@.Parameter1 int)
RETURNS TABLE
AS
RETURN (SELECT 1 AS test)
GO
CREATE FUNCTION dbo.myInLineFunction(@.Parameter1 int)
RETURNS @.MyTable TABLE (Col1 int)
AS
BEGIN
RETURN
END
GO
CREATE FUNCTION dbo.myScalarFunction(@.Parameter1 int)
RETURNS int
AS
BEGIN
RETURN 1
END
GO
SELECT
OBJECTPROPERTY(id, 'IsInLineFunction'),
OBJECTPROPERTY(id, 'IsScalarFunction'),
OBJECTPROPERTY(id, 'IsTableFunction'),
OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn'),
OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn')
FROM sysobjects
WHERE id IN
(
OBJECT_ID('dbo.myTableFunction'),
OBJECT_ID('dbo.myInLineFunction'),
OBJECT_ID('dbo.myScalarFunction')
)
Hope this helps.
Dan Guzman
SQL Server MVP
"AMiha" <amiha@.hotmail.com.false> wrote in message
news:umq0ocnTGHA.4452@.TK2MSFTNGP12.phx.gbl...
> I'm working with sql 2000 and result of
> SELECT
> OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'ExecIsAnsiNullsOn'),
> OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'ExecIsQuotedIdentOn')
> GO
> is null for myUdf.
> Result of
> select OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'IsTableFunction')
> is 1.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e7DFkFnTGHA.5900@.tk2msftngp13.phx.gbl...
>|||Thank you Dan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e4CxrBoTGHA.196@.TK2MSFTNGP10.phx.gbl...
> The 'sticky' SET options for table valued functions are apparently not
> reported correctly in SQL 2000 SP4. The create-time settings are used for
> execution though. No problem in SQL 2005.
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE FUNCTION dbo.myTableFunction(@.Parameter1 int)
> RETURNS TABLE
> AS
> RETURN (SELECT 1 AS test)
> GO
> CREATE FUNCTION dbo.myInLineFunction(@.Parameter1 int)
> RETURNS @.MyTable TABLE (Col1 int)
> AS
> BEGIN
> RETURN
> END
> GO
> CREATE FUNCTION dbo.myScalarFunction(@.Parameter1 int)
> RETURNS int
> AS
> BEGIN
> RETURN 1
> END
> GO
> SELECT
> OBJECTPROPERTY(id, 'IsInLineFunction'),
> OBJECTPROPERTY(id, 'IsScalarFunction'),
> OBJECTPROPERTY(id, 'IsTableFunction'),
> OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn'),
> OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn')
> FROM sysobjects
> WHERE id IN
> (
> OBJECT_ID('dbo.myTableFunction'),
> OBJECT_ID('dbo.myInLineFunction'),
> OBJECT_ID('dbo.myScalarFunction')
> )
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AMiha" <amiha@.hotmail.com.false> wrote in message
> news:umq0ocnTGHA.4452@.TK2MSFTNGP12.phx.gbl...
>