Rename of stored proc in EM/Manage Studio does not work as expecte

I normally rename stored procedures through script, so I never noticed this
before, but if you use EM (2000) or Server Management Studio (2005) to renam
e
a stored procedure the name changes in the GUI, but the actually name used t
o
execute it does not. So, if I start with a stored procedure named 'a' it
executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
still works and EXEC b fails. If you open the stored procedure for editing
you can see that in the code the stored procedure name has not been changed.
Whereas if you rename a view of function the name within the code is actuall
y
changed.
Can anyone explain this behavior?> So, if I start with a stored procedure named 'a' it
> executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
> still works and EXEC b fails.
This is not what I am seeing. I created a proc named aa in tempdb. I renamed
it to a using EM. EM
submitted the following SQL:
EXEC sp_rename N'[dbo].[aa]', N'a', N'object'
I can now execute a. If I try to execute aa, I get "could not find stored pr
ocedure".
My guess is that you have procs with same name but different owners. Check s
ysobjects.
Also, be aware that sp_rename does not change the source code for the proced
ure (see syscomments),
the original name will be in the source code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Byron" <Byron@.discussions.microsoft.com> wrote in message
news:3102CE8C-BA55-49C2-AB12-65BF91CDF87E@.microsoft.com...
>I normally rename stored procedures through script, so I never noticed this
> before, but if you use EM (2000) or Server Management Studio (2005) to ren
ame
> a stored procedure the name changes in the GUI, but the actually name used
to
> execute it does not. So, if I start with a stored procedure named 'a' it
> executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
> still works and EXEC b fails. If you open the stored procedure for editin
g
> you can see that in the code the stored procedure name has not been change
d.
> Whereas if you rename a view of function the name within the code is actua
lly
> changed.
> Can anyone explain this behavior?sql

Rename of stored proc in EM/Manage Studio does not work as expecte

I normally rename stored procedures through script, so I never noticed this
before, but if you use EM (2000) or Server Management Studio (2005) to rename
a stored procedure the name changes in the GUI, but the actually name used to
execute it does not. So, if I start with a stored procedure named 'a' it
executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
still works and EXEC b fails. If you open the stored procedure for editing
you can see that in the code the stored procedure name has not been changed.
Whereas if you rename a view of function the name within the code is actually
changed.
Can anyone explain this behavior?> So, if I start with a stored procedure named 'a' it
> executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
> still works and EXEC b fails.
This is not what I am seeing. I created a proc named aa in tempdb. I renamed it to a using EM. EM
submitted the following SQL:
EXEC sp_rename N'[dbo].[aa]', N'a', N'object'
I can now execute a. If I try to execute aa, I get "could not find stored procedure".
My guess is that you have procs with same name but different owners. Check sysobjects.
Also, be aware that sp_rename does not change the source code for the procedure (see syscomments),
the original name will be in the source code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Byron" <Byron@.discussions.microsoft.com> wrote in message
news:3102CE8C-BA55-49C2-AB12-65BF91CDF87E@.microsoft.com...
>I normally rename stored procedures through script, so I never noticed this
> before, but if you use EM (2000) or Server Management Studio (2005) to rename
> a stored procedure the name changes in the GUI, but the actually name used to
> execute it does not. So, if I start with a stored procedure named 'a' it
> executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
> still works and EXEC b fails. If you open the stored procedure for editing
> you can see that in the code the stored procedure name has not been changed.
> Whereas if you rename a view of function the name within the code is actually
> changed.
> Can anyone explain this behavior?