Cannot alter the definition of a stored procedure.

[Hans Sixhöj]

Hi!

I cannot change a stored procedure definition in an SQL
7.0 SP1 database. If I use the Enterprise Manager and edit
the procedure definition, change it to include another SQL
command and apply the changes and open the definition of
the stored procedure again, it has not changed.

I try to change the stored procedure with the ALTER
PROCEDURE sql command in Query Analyzer and execute the
command, it returns a result as follows:

(0 row(s) affected)

If I check the stored procedure definition with the
sp_helptext stored procedure, the change has not been
applied. Neither if i check the definition in Enterprise
Manager.

Does anyone know how this happened, I have to change a
stored procedure in the database, and it cannot be
changed. Can you by anyway change the srored procedure so
that it is read-only or similar so that this behaviour
occurs.

Regards,
/Hans Sixhöj
Microsoft Certified Solution Developer
Systems Developer
Framfab

[Tibor Karaszi]

I've never seen this. Sounds like some kind of corruption. Just so I understand:

You execute something similar to:
ALTER PROC x
SELECT

And then you execute the proc and it is the same old proc. and you look in syscomments (or
sp_helptext) and it is the old?

Could it be an object owner thing? Who is the owner of the proc? What user name are you
operating under? You might have two proc's now...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver

"Hans Sixhöj" <hans....@framfab.se> wrote in message
news:c11001c2139f$17fd28b0$95e62ecf@tkmsftngxs02...

[Hans Sixhöj]

Hi Tibor!

I have use the ALTER PROCEDURE statement but the procedure still remains
the same and o rows are affected in the query analyzer. I have checked
the definition in Enterprise Manager and with sp_helptext after the
attempted change of the procedure. The procedure is still as it was
before I executed the change.

The owner of all user objects in the database is DBO and I am logged in
as DBO, the permission chain can therfore not be the problem (I think
:-)).

Do you know how this could have happened, and if I can fix it.

It worked fine on the database before. The database has been transfered
with DTS from a SQL 2000 server to an SQL 7.0 SP1 server, with 70
compatibility.

Regards
/Hans Sixhöj
MCSD

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Keith Kratochvil]

In addition, could the "old" procedure also exist within the master
database? If it does, and if it is owned by dbo, you could be calling this
stored procedure (without knowing it) from within your user database.

--
Keith, SQL Server MVP

"Tibor Karaszi" <tibor.please_reply_to...@cornerstone.se>
wrote in message news:ehdk1#5ECHA.2392@tkmsftngp04...

[Simon Su (MS)]

Dear Hans Sixhoj,

I think you can follow the steps given below and see whether it works for
you,

1) Run the following script to make sure your stored procedure was saved in
syscomments table,

/*********************/
use <your_db>
select id,ctext from tempdb.dbo.syscomments where
id=object_id('your_db.dbo.your_sp_name')
/*********************/

This script should return one row if your stored procedure is in
syscomments table.

2) run sp_helptext your_sp_name and save the stored procedure text for
later use.

3) now drop your procedure,

/*******************/
use <your_db>
drop procedure your_sp_name
/**********************/

4) make sure the procedure is really dropped,

/*******************/
use <your_db>
execute your_db.dbo.your_sp_name
/**********************/
the above script should fail because the procedure has been dropped.

5) Now you can create your procedure and try to alter it if needed.
/*******************/
use <your_db>
create procedure xxx
as
xxx

/**********************/

Can you create and alter it now?

Regards,
Simon Su (MS)
-----------------------------
This posting is provided ¡°AS IS¡± with no warranties, and confers no rights

[Tibor Karaszi]

Hans,

I'm not sure what you mean by "0 rows affected". When I execute below (SQL2K, with QA2K), I
don't get "0 rows affected":
create proc p1 as select 1
go
alter proc p1 as select 2

If it isn't a user thing, I must assume it is a database corruption. But it is difficult to say
without seeing it. I would still verify that it isn't multiple users problem, by doing below
(assuming proc name is "p1":
SELECT * FROM syscomments where id = object_id('p1')

If you suspect some database corruption, check using
DBCC CHECKDB(dbname) WITH NO_INFOMSGS
DBCC CHECKALLOC(dbname) WITH NO_INFOMSGS

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver

"Hans Sixhöj" <hans....@framfab.se> wrote in message news:e9n3kO6ECHA.1780@tkmsftngp04...