x

object_id mismatch and ssms

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 CREATE procedure [dbo].[rpt_spDailyAccession_inf]. Which is a little weird.

If I go into ssms and left click/modify the 'adhoc_spDailyAccession_inf' I get ALTER procedure [dbo].[adhoc_spDailyAccession_inf] Which is as it should be, so it is weirder.

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

Msg 208, Level 16, State 6, Procedure rpt_spDailyAccession_inf, Line 17 Invalid object name 'dbo.rpt_spDailyAccession_inf'

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.
But where is sql hiding the stored procedure definition so that when I go through ssms I come up with the correct code? Any ideas? Do I have the tables I should be using wrong?

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.

more ▼

asked Jun 15, 2010 at 10:45 PM in Default

user-2004 (google) gravatar image

user-2004 (google)
37 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

This is because the procedure has been renamed - check out my blog post about SQL modules.

From that:

definition
This is the actual text that represents the module at the time that it was created. Note that this may not be the same as the text needed to re-create an object, because name changes effected with sp_rename are not reflected in this definition. Client side code, whether in SSMS, our very own sql editor or any other editor is responsible for replacing the old object name with the new. Some (SSMS included) fail to re-create the object correctly under various circumstances (for example, if comments are placed between the schema and object names), so tread with care if using this definition to recreate objects. This column will show as NULL if the module was created using WITH ENCRYPTION keyword.

more ▼

answered Jun 16, 2010 at 07:01 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

@Matt: This answer really rocks! I hope there would be some way to accept the answer like this in case if the person who asked it does not want to bother marking it as such.
Jun 16, 2010 at 11:44 AM Oleg
@Oleg - thanks! Nearly on your 1k now - keep it up - your answers have been consistently high quality...
Jun 16, 2010 at 11:53 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

Oleg

more ▼

answered Jun 15, 2010 at 11:55 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

+1 : drop and recreate
Jun 16, 2010 at 05:36 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jun 16, 2010 at 01:52 PM

user-2004 (google) gravatar image

user-2004 (google)
37 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x7
x7

asked: Jun 15, 2010 at 10:45 PM

Seen: 1275 times

Last Updated: Jun 16, 2010 at 04:38 AM