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

brief help on most popular functions of sql-server

Hello,
I need a good help (*.chm file is fine),
for sql-server functions & bit more...
Thanks :)Don't you already have Books Online? If not, get it here:
http://www.microsoft.com/sql/techin...00/default.mspx
David Portas
SQL Server MVP
--|||I need something totaly free.
Thanks :)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124281451.457156.163980@.g49g2000cwa.googlegroups.com...
> Don't you already have Books Online? If not, get it here:
> http://www.microsoft.com/sql/techin...00/default.mspx
> --
> David Portas
> SQL Server MVP
> --
>|||>I need something totaly free.
The BOL download as well as web access is free via the link David posted.
Hope this helps.
Dan Guzman
SQL Server MVP|||>I need something totaly free.
There are no price tags on the link that David provided. Did you actually
go to the link? Did you click on Download? Were you asked for a credit
card?|||About the price - sorry, my mistake.
Also,
I know this online help,
and it seems too massy.
I need just simple help, like old phasion help (*.chm is good enough).
Thanks :)|||The Books Online download IS in CHM format! You'll find the functions
in tsqlref.chm. Do you need someone to come over and help you install
it!
David Portas
SQL Server MVP
--|||> I need just simple help, like old phasion help (*.chm is good enough).
Sorry, no such thing that I know of, SQL Server is much too complex to be
documented by a single .chm file.
Download books online, install it on your machine, and familiarize yourself
with the search and index functionality. If there is only one set of
documentation you will have, this is the one you want. And you can ignore
all the "massy" stuff you don't need.|||> The Books Online download IS in CHM format!
Ah yes, I am stuck in SQL Server 2005 world, where everything is
<guid>.htm...

> Do you need someone to come over and help you install
> it!
LOL! I was going to suggest the same thing.|||> Ah yes, I am stuck in SQL Server 2005 world, where everything is
> <guid>.htm...
Still trying to get used to that myself. The format change was
inevitable I suppose but I still find the multiple pane layout quite
hard work.
David Portas
SQL Server MVP
--