SET QUOTED_IDENTIFIER option not reported correctly

alex.weatherall

Hi,

There is a problem that occurs with SQL Server 2000 Table-Valued functions (multi statement). When setting QUOTED_IDENTIFIER ON and ANSI_NULLS ON on this type of function, the set option is ignored by SQL Compare and QUOTED_IDENTIFIER OFF and ANSI_NULLS OFF is migrated to the synchronised database.
After some research online it would appear that this could be a bug in SQL Server 2000, not reporting the options correctly for this type of object.
If so then there might not be anything you can do about it, but I thought I'd raise the problem with you so that you are aware of the issue. I'm going to try and raise this as a bug with Microsoft.

Thanks

Alex Weatherall
TeleWare.com

Brian Donahue

Hello Alex,

Is this similar to the way SQL Server 2000 handles stored procedures? In other words, if you create a stored procedure, SQL Server inserts the QUOTED_IDENTIFIER statement for you, without even asking.

alex.weatherall

Hi Brian,

No, its a bug in the way it reports the Quoted Identifier and ansi nulls options.
f I create a table-valued function (note this is SQL Server 2000) 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 also means that I can't edit this Table Functions in SQL Server Management Studio as it returns an error, because it can't get at these options.

I've put this on the Microsoft SQL Server forum, but not got a reply yet.

Thanks
Alex Weatherall
TeleWare.com

alex.weatherall

I was just wondering whether anyone at Red-gate has been able to reproduce this problem.

It's an issue for us when using the product as we have functions that access indexed views which require both these options to be set on.

When we synchronise any changes to these functions, the set options are lost, so we have to follow a manual process.

Please let me know if you can reproduce the problem.

Please also see bugs (FDBK47725) and (FDBK48533) in http://lab.msdn.microsoft.com/productfeedback/

One is my bug submission, but the other is a related bug in SQL Server 2005 with Multi-Statment table valued functions.

Thanks

Alex Weatherall
TeleWare.com

alex.weatherall

I take it that's a no then :-(

Neil Davidson

Alex,

Sorry about the delay in replying. I'll ask somebody to take a closer look and get back to you.

alex.weatherall

Thanks Neil,

Microsoft are unlikely to fix the bug (see previous post), they said, but I was wondering if there was anything you might be able to do to work it out.

I've noticed that the status column in sysobjects does have the 30th bit set with the SET ANSI_NULLS ON option on which seems to be the important one for use with indexed views, but OBJECTPROPERTY function doesn't report it.

If nothing can be done with it, so be it, it's MS's fault, but it's really frustrating not to be able to rely on SQL Compare to migrate with these functions with the correct set options.

Thanks

Alex Weatherall

JonathanWatts

Alex,

We have reproduced the issue. We will try to get a fix in for this, but I am not sure what timescales will be at this time.

Regards,

Jonathan

alex.weatherall

You are stars, that's cheered me up after one of those days :-)

Alex

Eddie D

This issue has now been fixed in SQL Compare V7.0 release.

Eddie Davis