SET QUOTED_IDENTIFIER option not reported correctly

alex.weatherallalex.weatherall Posts: 54
edited July 24, 2008 11:24AM in SQL Compare Previous Versions
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
Alex Weatherall
TeleWare.com

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • 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
    TeleWare.com
  • 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
    TeleWare.com
  • I take it that's a no then :-(
    Alex Weatherall
    TeleWare.com
  • Alex,

    Sorry about the delay in replying. I'll ask somebody to take a closer look and get back to you.
    - Neil Davidson
    Red Gate Software Ltd
  • 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
    Alex Weatherall
    TeleWare.com
  • 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
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • You are stars, that's cheered me up after one of those days :-)

    Alex
    Alex Weatherall
    TeleWare.com
  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    This issue has now been fixed in SQL Compare V7.0 release.

    Eddie Davis
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
Sign In or Register to comment.