SQL Server Performance

Management Studio wierdness

Discussion in 'SQL Server 2005 General DBA Questions' started by kpayne, Oct 6, 2005.

  1. kpayne New Member

    Hello everyone!

    I am having trouble opening certain multi-statement table-valued functions in SQL Server Management Studio (RC1) in an SQL 2000 database. The strange part is that some objects in the same database will open via right-click/Modify and others will not. The message from Management Studio is:

    Property QuotedIdentifierStatus is not available for UserDefinedFunction '[dbo].[udf_AttritionClosedWithin]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

    When I try to script the object with right-click/Script Function As/Create... I get the same message as before. When I try Script Function As/Alter... I get the following message:

    Property AnsiNullStatus is not available for UserDefinedFunction '[dbo].[udf_AttritionClosedWithin]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

    The really wierd thing is that I can modify/script some multi-statement table-valued udfs in the same database with no problem at all.

    I can open all of the objects fine in QA and EM.

    I have tried dropping and re-creating the functions with the same results. I have dropped and re-created the entire database with the same results. The database is configured with the standard indexed view settings (ANSI Nulls, ANSI Padding, Quoted Identifier, Concat Null, Arithabort, etc..) and the connection properties in Management Studio match the database settings.

    I thought that perhaps there was a mismatch between the udfs and the tables that they are querying, so I recreated one of the udfs after commenting out all of the code. I was not able to open the udf afterwards.

    I tried creating a new simple table-valued udf with ANSI Nulls & Quoted Identifier OFF, and then dropped and re-created it with them ON. I was unable to modify or script either udf.

    I have no problems modifying/scripting inline udfs, scalar udfs, and stored procs.

    I am convinced that there is something going on between the database configuration settings and the settings that are saved with the objects (even though udfs are not supposed to have the settings saved with them). QA sets ANSI NULLS & QUOTED IDENTIFIER OFF when it scripts the functions.

    Any ideas on how to fix this?

    Thanks!



    Keith Payne
    Technical Marketing Solutions
    www.tms-us.com

Share This Page