Cant seem to modify a SQL Server 2000 Table Valued Function in SQL Server Management Studi

Hi
I cant seem to modify a SQL Server 2000 Table Valued Function through
SQL Server Management Studio (2005)
Has anyone else experienced this issue?
DicksterAny errors?
For me it works, make sure that the database is set up compatibility level
90
<grd@.renre.com> wrote in message
news:1160058691.080724.39850@.k70g2000cwa.googlegroups.com...
> Hi
> I cant seem to modify a SQL Server 2000 Table Valued Function through
> SQL Server Management Studio (2005)
> Has anyone else experienced this issue?
> Dickster
>|||grd@.renre.com wrote:
> Hi
> I cant seem to modify a SQL Server 2000 Table Valued Function through
> SQL Server Management Studio (2005)
> Has anyone else experienced this issue?
> Dickster
>
What do you mean by "can't seem to modify" ? If you execute the DDL
script (i.e. the "ALTER FUNCTION" or "CREATE FUNCTION" script), what
happens?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> What do you mean by "can't seem to modify" ? If you execute the DDL
> script (i.e. the "ALTER FUNCTION" or "CREATE FUNCTION" script), what
> happens?
Tracy
I get the following error message
----
Property QuotedIdentifierStatus is not available for
UserDefinedFunciton '[bdo].[SplitCsvList]'. This property may not exist
for this object, or may not be retrievable due to insufficient access
rights (Microsoft.SqlServer.Smo)
----
NB: I am logged in as 'sa'
Dickster|||> What do you mean by "can't seem to modify" ? If you execute the DDL
> script (i.e. the "ALTER FUNCTION" or "CREATE FUNCTION" script), what
> happens?
>
Tracy
I right Mouse click on the Table Valued Function & Select 'Modify'
I get the following error message
----
Property QuotedIdentifierStatus is not available for
UserDefinedFunciton '[bdo].[SplitCsvList]'. This property may not exist
for this object, or may not be retrievable due to insufficient access
rights (Microsoft.SqlServer.Smo)
----
NB: I am logged in as 'sa'
Dickster|||Try without the GUI. ALTER FUNCTION. That would lead you to whether the problem is in the GUI or
server level. The GUI, however, will just pull out the source code from the system tables and slap
an ALTER FUNCITON around it and show that in a query window. So the problem seem to be getting at
the source code from SQL Servers meta-data views.
I ran a Profiler trace when right-clicking and selection Modify on a single statement table valued
function. Below seem to be the key parts. You can tru to modify the relevant parts in there and run
from a query window to see if that same error is returned.
Perhaps it is a compatibility level issue for the database?
SELECT
SCHEMA_NAME(udf.schema_id) AS [Schema],
udf.name AS [Name]
FROM
sys.all_objects AS udf
WHERE
(udf.type in ('TF', 'FN', 'IF', 'FS', 'FT'))and(udf.name=N'f' and SCHEMA_NAME(udf.schema_id)=N'dbo')
SELECT
udf.name AS [Name],
udf.object_id AS [ID],
udf.create_date AS [CreateDate],
udf.modify_date AS [DateLastModified],
SCHEMA_NAME(udf.schema_id) AS [Schema],
CAST(
case
when udf.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = udf.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit) AS [IsSystemObject],
usrt.name AS [DataType],
sret_param.name AS [DataTypeSchema],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND ret_param.max_length <> -1 THEN
ret_param.max_length/2 ELSE ret_param.max_length END AS int) AS [Length],
CAST(ret_param.precision AS int) AS [NumericPrecision],
CAST(ret_param.scale AS int) AS [NumericScale],
ISNULL(xscret_param.name, N'') AS [XmlSchemaNamespace],
ISNULL(s2ret_param.name, N'') AS [XmlSchemaNamespaceSchema],
ISNULL( (case ret_param.is_xml_document when 1 then 2 else 1 end), 0) AS [XmlDocumentConstraint],
CAST(OBJECTPROPERTYEX(udf.object_id,N'ExecIsAnsiNullsOn') AS bit) AS [AnsiNullsStatus],
CAST(OBJECTPROPERTYEX(udf.object_id, N'IsSchemaBound') AS bit) AS [IsSchemaBound],
CAST(CASE WHEN ISNULL(smudf.definition, ssmudf.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS
[IsEncrypted],
case when amudf.object_id is null then N'' else asmbludf.name end AS [AssemblyName],
case when amudf.object_id is null then N'' else amudf.assembly_class end AS [ClassName],
case when amudf.object_id is null then N'' else amudf.assembly_method end AS [MethodName],
CAST(case when amudf.object_id is null then CAST(smudf.null_on_null_input AS bit) else
amudf.null_on_null_input end AS bit) AS [ReturnsNullOnNullInput],
case when amudf.object_id is null then case isnull(smudf.execute_as_principal_id, -1) when -1 then 1
when -2 then 2 else 3 end else case isnull(amudf.execute_as_principal_id, -1) when -1 then 1 when -2
then 2 else 3 end end
AS [ExecutionContext],
case when amudf.object_id is null then ISNULL(user_name(smudf.execute_as_principal_id),N'') else
user_name(amudf.execute_as_principal_id) end AS [ExecutionContextPrincipal],
CAST(OBJECTPROPERTYEX(udf.object_id, N'IsDeterministic') AS bit) AS [IsDeterministic],
(case when 'FN' = udf.type then 1 when 'FS' = udf.type then 1 when 'IF' = udf.type then 3 when 'TF'
= udf.type then 2 when 'FT' = udf.type then 2 else 0 end) AS [FunctionType],
CASE WHEN udf.type IN ('FN','IF','TF') THEN 1 WHEN udf.type IN ('FS','FT') THEN 2 ELSE 1 END AS
[ImplementationType],
CAST(OBJECTPROPERTYEX(udf.object_id, N'IsQuotedIdentOn') AS bit) AS [QuotedIdentifierStatus],
ret_param.name AS [TableVariableName],
ISNULL(smudf.definition, ssmudf.definition) AS [Definition]
FROM
sys.all_objects AS udf
LEFT OUTER JOIN sys.all_parameters AS ret_param ON ret_param.object_id = udf.object_id and
ret_param.is_output = 1
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = ret_param.user_type_id
LEFT OUTER JOIN sys.schemas AS sret_param ON sret_param.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = ret_param.system_type_id and
baset.user_type_id = baset.system_type_id
LEFT OUTER JOIN sys.xml_schema_collections AS xscret_param ON xscret_param.xml_collection_id =ret_param.xml_collection_id
LEFT OUTER JOIN sys.schemas AS s2ret_param ON s2ret_param.schema_id = xscret_param.schema_id
LEFT OUTER JOIN sys.sql_modules AS smudf ON smudf.object_id = udf.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmudf ON ssmudf.object_id = udf.object_id
LEFT OUTER JOIN sys.assembly_modules AS amudf ON amudf.object_id = udf.object_id
LEFT OUTER JOIN sys.assemblies AS asmbludf ON asmbludf.assembly_id = amudf.assembly_id
WHERE
(udf.type in ('TF', 'FN', 'IF', 'FS', 'FT'))and(udf.name=N'f' and SCHEMA_NAME(udf.schema_id)=N'dbo')
SELECT
NULL AS [Text],
ISNULL(smudf.definition, ssmudf.definition) AS [Definition]
FROM
sys.all_objects AS udf
LEFT OUTER JOIN sys.sql_modules AS smudf ON smudf.object_id = udf.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmudf ON ssmudf.object_id = udf.object_id
WHERE
(udf.type in ('TF', 'FN', 'IF', 'FS', 'FT'))and(udf.name=N'f' and SCHEMA_NAME(udf.schema_id)=N'dbo')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<grd@.renre.com> wrote in message news:1160069137.082636.156350@.k70g2000cwa.googlegroups.com...
>> What do you mean by "can't seem to modify" ? If you execute the DDL
>> script (i.e. the "ALTER FUNCTION" or "CREATE FUNCTION" script), what
>> happens?
>
> Tracy
> I right Mouse click on the Table Valued Function & Select 'Modify'
> I get the following error message
> ----
> Property QuotedIdentifierStatus is not available for
> UserDefinedFunciton '[bdo].[SplitCsvList]'. This property may not exist
> for this object, or may not be retrievable due to insufficient access
> rights (Microsoft.SqlServer.Smo)
> ----
> NB: I am logged in as 'sa'
> Dickster
>|||grd@.renre.com wrote:
>> What do you mean by "can't seem to modify" ? If you execute the DDL
>> script (i.e. the "ALTER FUNCTION" or "CREATE FUNCTION" script), what
>> happens?
> Tracy
> I get the following error message
> ----
> Property QuotedIdentifierStatus is not available for
> UserDefinedFunciton '[bdo].[SplitCsvList]'. This property may not exist
> for this object, or may not be retrievable due to insufficient access
> rights (Microsoft.SqlServer.Smo)
> ----
> NB: I am logged in as 'sa'
> Dickster
>
This is a known bug...
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126099
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for your reply Tracy and to all others who replied.
Dickster