Bug with SET options for table-valued functions.

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

Further to this :

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
|||

Solution:

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=0750596e-9915-42ea-9295-62d1fb31d0a4

|||

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