Stored Procedure definition not updating, causing DTS copy error

I've got a weird one here. I'm running a DTS package on SQL Server
2005. It copies a bunch of stored procedures. I renamed them on the
originating server and ran the DTS again.

The came over with the old name and code!

I deleted the DTS and built it from scratch, and the same thing
happened.
I ran SELECT * FROM sys.objects where type = 'P' on the source server
and the names were correct
I'm explicitly checking which sp to copy rather than using Copy all. I
can see the sp names
I've deleted and recreated the sp on the source server using scripts
I've checked the source server name
I've Refreshed everywhere
Nothing works

Why is up_Department_GetAllBySchool trying to be be pulled over when
it doesn't exist?
Why is up_Department_GetBySchool not being pulled over when it does
exist?

I've heard that SQL 2005 pre-SP2 has a problem where renaming an
object that has a text definition (like sprocs, functions, triggers,
views) doesn't update the definition. So if you pull that object
definition and run it into your new database, it will use the original
script, which has the original name.

I ran
sp_helptext 'up_Department_GetBySchool'
and checked the CREATE statement at the top. Sure enough, it had the
old text

I asked our NetAdmin to install SP2 on our server. Then I ran
sp_refreshsqlmodule 'up_Department_GetForSchool'
and got this error:
Invalid object name 'up_Department_GetAllForSchool'.

So even the code which was supposed to fix it, doesn't. Has anyone
else had this problem, and managed to fix it?

--John Hunter

Re: Stored Procedure definition not updating, causing DTS copy error

This is a byproduct of sp_rename, as described in the Books On Line:

"Renaming a stored procedure, view or trigger will not change the name
of the corresponding object name in the syscomments table. This may
result in problems generating a script for the object as the old name
will be inserted from the syscomments table into the CREATE statement.
For best results, do not rename these object types. Instead, drop and
re-create the object by its new name."

So sp_rename changes the object name in sysobjects, but does not
change the CREATE PROCEDURE text in syscomments, which still has the
old name.

Roy Harvey
Beacon Falls, CT

On Wed, 27 Jun 2007 01:49:57 -0000, "jshunter@waikato.ac.nz"
wrote:

>I've got a weird one here. I'm running a DTS package on SQL Server
>2005. It copies a bunch of stored procedures. I renamed them on the
>originating server and ran the DTS again.
>
>The came over with the old name and code!
>
>I deleted the DTS and built it from scratch, and the same thing
>happened.
>I ran SELECT * FROM sys.objects where type = 'P' on the source server
>and the names were correct
>I'm explicitly checking which sp to copy rather than using Copy all. I
>can see the sp names
>I've deleted and recreated the sp on the source server using scripts
>I've checked the source server name
>I've Refreshed everywhere
>Nothing works
>
>Why is up_Department_GetAllBySchool trying to be be pulled over when
>it doesn't exist?
>Why is up_Department_GetBySchool not being pulled over when it does
>exist?
>
>I've heard that SQL 2005 pre-SP2 has a problem where renaming an
>object that has a text definition (like sprocs, functions, triggers,
>views) doesn't update the definition. So if you pull that object
>definition and run it into your new database, it will use the original
>script, which has the original name.
>
>I ran
>sp_helptext 'up_Department_GetBySchool'
>and checked the CREATE statement at the top. Sure enough, it had the
>old text
>
>I asked our NetAdmin to install SP2 on our server. Then I ran
>sp_refreshsqlmodule 'up_Department_GetForSchool'
>and got this error:
>Invalid object name 'up_Department_GetAllForSchool'.
>
>So even the code which was supposed to fix it, doesn't. Has anyone
>else had this problem, and managed to fix it?
>
>--John Hunter