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

Can't modify the UDFs I create

I am logged in using an account that is dbo on this database. I created a new UDF, which works fine (I can execute it). When I right click on it in the object explorer and select modify, it says:

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

I would think that if I had the rights to create, as well as use, the function then I should have rights to modify it. I get a similar error if I "script to new window as ALTER..."

This sounds like the following bug, which is scheduled to be fixed in Service Pack 1:
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=FDBK38845

Steve Kass
Drew University

can't modify table in SMS...timeout expired?

Hi: I'm trying to add a field to a table by modifying it in Management
Studio. I'm running SQLSMS locally on the server.
When I try to save the table I get an error
"Post-Save Notifications...
[table name]
- Unable to modify table.
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding."
The table currently has 5454 rows in it.
geek-y-guy (noone@.nowhere.org) writes:
> Hi: I'm trying to add a field to a table by modifying it in Management
> Studio. I'm running SQLSMS locally on the server.
That is a very dangerous function, which has serious bugs and shortcomings.
I strongly recommend to use ALTER TABLE statements to change your tables.
Or, at the very least, never, I say NEVER, save directly to implement
a change, but always generate a script and perform these modifications:
o Remove all BEGIN and COMMIT TRANSACTION except for the first and last.
o Wrap all batches in IF @.@.trancount > 0 BEGIN ... END
o Change all WITH NOCHECK to WITH CHECK.
And be very careful to check that the script only include the
changes you want to make. There are bugs that can cause discarded
changes to be includd.

> When I try to save the table I get an error
> "Post-Save Notifications...
> [table name]
> - Unable to modify table.
> Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding."
> The table currently has 5454 rows in it.
Under Tools->Options there is a timeout for the Designers you can
change, if you insist on using that function. But if you run the script
from a query window,the timeout is not an issue.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

can't modify stored proc which is in a publication

Hi

SQL 2005 sp1 - merge replication - HTTPS.

We have 2 publications for the database - one which has subscription.SyncType = SubscriptionSyncType.Automatic
and another with SyncType = SubscriptionSyncType.None.

The first publication is there so we can add new stored procs etc, the second contains the initial schema and the data.

When we try to modify a proc which is in the publication with SyncType = Automatic, the query never returns.

This is most urgent - thanks for your help.

Bruce

Do we have one or two subscriptions to publication one that contains the stored proc as a merge article in the publication?

|||

There are 2 subscriptions - one for each publication - but the proc is only in one publication

regards
Bruce

|||

1. Back to your original question - could the query (modifying the SP) be locked by another process so that it won't return?

2. Let us try to isolate this problem. If you can - colon the publication database, this publication (no second one), and subscribing database; does the same problem still occur?

Thanks.

|||

1. I doubt it..

2. 'colon the publication database' - what do you mean ?

thanks

|||

Create another publication database and create the same set of user tables/views/SPs. Just create one publication which includes the SP and other merge articles. See if you still can repro this issue with a single publication.

Thanks.

|||

We will try this tomorrow - but it's worth pointing out the following

a) the publication with SyncType = none -- has a few hundred stored procs - I can change any of these no worries

b) the publication with SyncType = automatic - initially just has 1 stored proc - so we can create the publication! - it's the procs we've added to this which we can't subsequently change.

Regards
Bruce

|||

Ok

I stripped it right back.

a. created a new database

b. added one proc

c. created the merge publication , snapshot etc

d. initalized ok

e. The query never returns....

Thanks
Bruce

|||

Can you share the proc prototype/definition (also I assume you use "ALTER PROCEDURE" to modify it)? I want to repro this case in house.

Thanks.

|||

Absolutely - it's a mindnumbing place-holder - it's just there so we can create the publication..

Don't laugh.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

--

-- This is the proc for the 'extras' subscription

--

--

ALTER PROCEDURE [dbo].[aaaaPlaceHolder]

AS

BEGIN

SET NOCOUNT ON;

SELECT 'fish', 'cow', 'dog', 'elephant', 'pig', 'moo', 's', 't', 'dog', 'sheep'

END

|||

Bruce,

Thanks for your help. I have repro-ed this scenario on my machine. I need to work with my peers to diagnoise the real cause of it. Should get back to you once I have the answer.

Regards,

Leo

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

As a temp solution - a dummy table can be created to join the SP in the same publication so that SP can be modified and replicated to the subscriber.

Leo

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi

I'll try it - has this been logged as a bug ? If so, how do I keep track of it ?

Thanks

|||

The bug was definitely filed and once I heard the decision/status I post it immediately.

Thanks

|||

This issue/bug should be addressed in Yukon Service Pack 2.

Leo

This posting is provided AS IS with no warranties, and confers no rights

can't modify stored proc which is in a publication

Hi

SQL 2005 sp1 - merge replication - HTTPS.

We have 2 publications for the database - one which has subscription.SyncType = SubscriptionSyncType.Automatic
and another with SyncType = SubscriptionSyncType.None.

The first publication is there so we can add new stored procs etc, the second contains the initial schema and the data.

When we try to modify a proc which is in the publication with SyncType = Automatic, the query never returns.

This is most urgent - thanks for your help.

Bruce

Do we have one or two subscriptions to publication one that contains the stored proc as a merge article in the publication?

|||

There are 2 subscriptions - one for each publication - but the proc is only in one publication

regards
Bruce

|||

1. Back to your original question - could the query (modifying the SP) be locked by another process so that it won't return?

2. Let us try to isolate this problem. If you can - colon the publication database, this publication (no second one), and subscribing database; does the same problem still occur?

Thanks.

|||

1. I doubt it..

2. 'colon the publication database' - what do you mean ?

thanks

|||

Create another publication database and create the same set of user tables/views/SPs. Just create one publication which includes the SP and other merge articles. See if you still can repro this issue with a single publication.

Thanks.

|||

We will try this tomorrow - but it's worth pointing out the following

a) the publication with SyncType = none -- has a few hundred stored procs - I can change any of these no worries

b) the publication with SyncType = automatic - initially just has 1 stored proc - so we can create the publication! - it's the procs we've added to this which we can't subsequently change.

Regards
Bruce

|||

Ok

I stripped it right back.

a. created a new database

b. added one proc

c. created the merge publication , snapshot etc

d. initalized ok

e. The query never returns....

Thanks
Bruce

|||

Can you share the proc prototype/definition (also I assume you use "ALTER PROCEDURE" to modify it)? I want to repro this case in house.

Thanks.

|||

Absolutely - it's a mindnumbing place-holder - it's just there so we can create the publication..

Don't laugh.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

--

-- This is the proc for the 'extras' subscription

--

--

ALTER PROCEDURE [dbo].[aaaaPlaceHolder]

AS

BEGIN

SET NOCOUNT ON;

SELECT 'fish', 'cow', 'dog', 'elephant', 'pig', 'moo', 's', 't', 'dog', 'sheep'

END

|||

Bruce,

Thanks for your help. I have repro-ed this scenario on my machine. I need to work with my peers to diagnoise the real cause of it. Should get back to you once I have the answer.

Regards,

Leo

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

As a temp solution - a dummy table can be created to join the SP in the same publication so that SP can be modified and replicated to the subscriber.

Leo

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi

I'll try it - has this been logged as a bug ? If so, how do I keep track of it ?

Thanks

|||

The bug was definitely filed and once I heard the decision/status I post it immediately.

Thanks

|||

This issue/bug should be addressed in Yukon Service Pack 2.

Leo

This posting is provided AS IS with no warranties, and confers no rights

Cant modify sp_admessage - Internals Mystery!!! Please Help!!!

Due to legacy issues we want to modify sp_addmessage. I am able to open it it up change the code I want to add but it wont compile.

All I am doing is changing:

-- Must be ServerAdmin to manage messages
if is_srvrolemember('serveradmin') = 0
begin
raiserror(15247,-1,-1)
return (1)
end

To this code:

if (not (is_srvrolemember('sysadmin') = 1)) and ((@.severity > 18) or
(rtrim(upper(@.with_log)) = 'TRUE'))
begin
raiserror(15042,-1,-1)
return (1)
end

When we recompile I get:

Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 99
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 131
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 135
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 136
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 152
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 156
Incorrect syntax near '%'.

When we goto that line I see:

EXEC %%ErrorMessage(ID = @.msgnum).Lock(Exclusive = 1)

What is the "%%" doing - Does this make it impossible to modify?

Thanks,

Andy

In SQL SERVER 2005, majority of the system stored procedures including sp_addmessage exist in the read only resource database. You cannot modify them.

If you are creating your own procedure you cannot the "%%" syntax. This syntax is a hook to call functions inside the sqlservr.exe binary and is not available for user procedures.

Are you trying to restrict the roles that can call sp_addmessage ?

|||

Hi,

I have a similar problem, but want to solve it different.

Problem: sp_addmessage can only be called by sysadmin or serveradmin. But also normal users (with ddladmin/security/read/writer-role) should be able to call sp_addmessage.

Possible solution: I wrote a wrapper-procedure sp__addmessage which calls sp_addmessage with more rights:

create procedure sp__addmessage

(@.msgnum int, @.severity smallint, @.msgtext nvarchar(510),

@.lang sysname )

WITH execute as OWNER

AS

select 'I am ' + suser_name(), user_name()

exec sp_addmessage @.msgnum, @.severity, @.msgtext, @.lang

go

I create this procedure (sa__addmessage) as sa and grant execution to a user. When the user executes this function it is displayed, 'I am sa dbo' bit the execution of sp_addmessage is not permitted:

Msg 15247, Level 16, State 1, Procedure sp_addmessage, Line 18

User does not have permission to perform this action.

Has anybody an idea how to give the user permissions to execute sp_addmessage?

Cheers, Manuel

Cant modify or delete an existing job

Folks, i have rebuilt my old server machine and i've restored the MSDB over the new machine. Now when i try to edit or delete any of the jobs that come from the RESTORE; i get the following error message:

I never setup multiserver administration.
However i can create and delete new jobs.
How do i remove these older jobs.

Howdy!This happened to me once, when a machine was renamed. I think it is as simple as updating the sysjobs table to reflect the new server name (originating_server column). Make sure you have a good backup, before you do this, however.|||sorry: the error message is:
error 14274: Cannot add, update, or delete a job that originated from anMSX server.

Thanx, MCrowley. I would try this when the users are off the machine.

Howdy.|||My experience was the exact same as MCrowley's; I think I went in and hand-edited the jobs in the msdb database (through EM; it was a long time ago). You may need to set the switch to allow updating of system tables, but I don't recall that I had to do that.

Regards,

hmscott

sorry: the error message is:
error 14274: Cannot add, update, or delete a job that originated from anMSX server.

Thanx, MCrowley. I would try this when the users are off the machine.

Howdy.|||i am squared. thanx.

USE MASTER
GO
sp_configure 'allow updates' ,1
GO
RECONFIGURE WITH OVERRIDE
GO
USE MSDB
go
update sysjobs set originating_server='myserver'
GO
USE MASTER
GO
sp_configure 'allow updates' ,0
GO
RECONFIGURE WITH OVERRIDE

--HOWDY!|||Just had the same problem on a rebuilt server - the above script sorted it out.

thanks,
Paul.

Cant insert the value NULL into column

I'm trying to modify the data type in a field on a table. I rec'd this message:
Unable to modify tblship_sched
Can't insert the value NULL into column mfg_qty table qc.dbo.Tmp_tblship_sched; Column doesn't allow nulls.
Insert fails.

What is or where is the Tmp table?

Thank you.I am not sure what you mean when you ask where the table is.

SELECT * FROM SYSOBJECTS WHERE NAME = 'Tmp_tblship_sched'

If the table exists then I suggest you check the column constraint for the table in question. Looks like it has a NOT NULL constraint.

You can disable it for your insert.

Good Luck!