Renaming of stored procs

debeera · February 15, 2006 10:05AM

We renamed a stored proc called "a" to "b" on database 1. Compare detects that stored proc "b" is not in the database 2. The script of stored proc "b" however still shows the stored proc name as being "a" and when you sync, stored proc "a" gets created in database 2. Does anyone know why this is happening, is there a setting that we are missing? Thanks.

Andras B · February 15, 2006 12:26PM

debeera wrote:

We renamed a stored proc called "a" to "b" on database 1. Compare detects that stored proc "b" is not in the database 2. The script of stored proc "b" however still shows the stored proc name as being "a" and when you sync, stored proc "a" gets created in database 2. Does anyone know why this is happening, is there a setting that we are missing? Thanks.

Hi,

In SQL Server stored procedures are stored as text. If you just use sp_rename, the definition of the stored procedure will still contain the original name. This basically means that the database is in an inconsistent state. You can get the stored procedure definition using the syscomments system table. Unfortunately this is a limitation of SQL Server's sp_rename and it does end the database in an inconsistent state.

Regards,

Andras

debeera · February 16, 2006 2:16AM

Thanks Andras,
Surely SQL Compare can use the current stored proc name and not the name in the definition. Currently we have about 50 renamed objects, which make using SQL Compare an absolute nightmare, because all these renamed objects are detected as differences. Al these object have to be copied over manually and whenever SQL Compare is used these objects are picked up as differences because of the different stored proc names in the definition?

Thanks,
Andre

aaron.bertrand · February 16, 2006 3:03PM

You can't just right-click a stored procedure in Enterprise Manager and click rename. I wonder what else would break if the stored procedure needed to be recompiled or if you tried to modify it using other tools.

I suggest you take a different approach to renaming stored procedures, such as using sp_rename. This will keep the CRETAE PROCEDURE definition intact.

(Just one more big dark x against using Enterprise Manager to manage your objects.)

aaron.bertrand · February 16, 2006 3:14PM

I said:

"I suggest you take a different approach to renaming stored procedures, such as using sp_rename. This will keep the CRETAE PROCEDURE definition intact. "

What I meant to say was:

I suggest you take a different approach than using sp_rename. For example, DROP/CREATE. This will keep the CREATE PROCEDURE definition intact.

iposner · March 3, 2006 5:07AM

I think the answer given is very shoddy. If there is a problem with sp_rename not changing the syscomments definition, then it should be escalated to Microsoft to provide a fix.

I'm sure they will listen far more attentively to a major toolset vendor than to individuals.

Having said that, it is not beyond the capability of red gate to provide a cross-checking routine and modify scripts appropriately

Andras B · March 6, 2006 5:49AM

iposner wrote:

I think the answer given is very shoddy. If there is a problem with sp_rename not changing the syscomments definition, then it should be escalated to Microsoft to provide a fix.

I'm sure they will listen far more attentively to a major toolset vendor than to individuals.

Having said that, it is not beyond the capability of red gate to provide a cross-checking routine and modify scripts appropriately

Hi,
Microsoft is aware of the problem, but since the stored procedures work, Microsoft, AFAIK, is not planning any changes to sp_rename.
Concerning us modifying the scripts to ensure their consistency is something I now added to my to do list, but I'm afraid it will not be in our next release. Many thanks for the suggestion,
Regards,
Andras

Renaming of stored procs

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk