In sys.objects I found the object_id for the name 'adhoc_spDailyAccession_inf' a stored procedure.
When I look in sql_modules or syscomments using this object_id I see the code for a different stored procedure
If I go into ssms and left click/modify the 'adhoc_spDailyAccession_inf' I get
The stored procdure rpt_spDailyAccession_inf can not be found in ssms.
When I do a search for '%adhoc_spDailyAccession%' in sql_modules I don't come up with anything.
This is in a sql 2008 db and the sql 2008 R2 db that it was restored to. Both dbs and serves have been cycled several times. dbcc checkdb comes up clean. I found this when writing code to do mass changes of the stored procedures. I tried to run the alter proc using the sql_modules definition column and got
because there is no sys.object entry. This process worked for > 100 other stored procedures.
I'm ok with a glitch where the definition and object_id can get mismatched.
And no this is not a finger check or misread. I checked this about 20X and it is really impacting my positive outlook. Any help would be appreciated.
This is because the procedure has been renamed - check out my blog post about SQL modules.
answered Jun 16 '10 at 07:01 AM
Matt Whitfield ♦♦
The other place where you can look besides sql_modules or syscomments is the master.sys.syscomments view. Also, try to drop both procs and then re-create instead of altering.
answered Jun 15 '10 at 11:55 PM
Thanks Matt your blog answered the missmatch question really well. It also answered the other part of the question where when you click modify in ssms the alter proc code is created using the name in the objects table. So, the stored procedure name you see when clicking the modify did not match the coded found in sql_modules. Thanks again.
answered Jun 16 '10 at 01:52 PM