Hi,
I think I've come across a bug in SQL Server 2000 (SP3):
If I create a table-valued function 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 would appear to be a bug in the way that SQL Server reports the properties out via DMO and the system functions for Table-Valued functions. It is breaking our syncronisation process (using SQL compare).
How do I submit this as a bug to Microsoft And is it likely to be fixed in an update or Service Pack soon
Thanks
Alex Weatherall
I think I've come across a bug in SQL Server 2000 (SP3):
If I create a table-valued function 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 would appear to be a bug in the way that SQL Server reports the properties out via DMO and the system functions for Table-Valued functions. It is breaking our syncronisation process (using SQL compare).
How do I submit this as a bug to Microsoft And is it likely to be fixed in an update or Service Pack soon
Thanks
Alex Weatherall
Bug with SET options for table-valued functions.
Michael Oberhardt
I can't actually modify table valued functions on SQL Server 2000 in the new SQL 2005 Management Studio due to this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Property QuotedIdentifierStatus is not available for UserDefinedFunction '[dbo].[fn_nums]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&
EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=QuotedIdentifierStatus&LinkId=20476
This is due to the same problem. This bug needs fixing asap, it means that I can't use the new management tools to manage SQL Server 2000, I need to use Query Analyzer to access the code for all table valued functions.
Please can someone get back to me asap.
Thanks
Alex Weatherall
TeleWare.com
Avida
Solution:
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx feedbackid=0750596e-9915-42ea-9295-62d1fb31d0a4
GutterBug
You're right - the properties don't apply to table-valued functions in SQL Server 2000. In SQL Server 2000, the behavior is as if QUOTED_IDENTIFIER and ANSI_NULLS were always on.
The defect where Management Studio tries to get these properties for tabled-value functions in SQL Server 2000 servers has been fixed for SP2.
Thanks,
Steve