Missing Stored Procedure...

Question

Post reply

Missing Stored Procedure...

Yan Kleber

Old Hand

Points: 307
More actions
January 18, 2010 at 12:26 pm

#218297

I have created a simple SP called 'CatchError' that I use within a TRY..CATCH block to dump error (if applicable) to a table.
The case is that this SP is kind of missing. I can execute it and SQL Management Studio tells me that the SP is there, but I cannot find it anywhere.
By running the following query:
SELECT DISTINCT SO.NAME
FROM SYSOBJECTS SO (NOLOCK)
WHERE SO.TYPE = 'P'
AND SO.NAME LIKE '%CatchError%'
ORDER BY SO.Name
It lists the SP, but doesn't tell me WHERE it is. I need to make a few changes in the SP, but I cannot edit it because I cannot find it.
I have an 'ocean' of SPs along over 20 databases, and I went through one by one looking for it manually, but no success... Any clues about how to find a missing SP?
Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

What do you mean by 'where it is'?

Are you looking for it in the Object Explorer of Management studio?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Yan Kleber Old Hand Points: 307 More actions · Answer 2

Well, normally I find my SPs by looking at the left column (like in the Windows Explorer).

I locate the database name and click in its [+], then click the [+] Programmability sub-option, then click the [+] Stored Procedures sub-option and then look for the SP in the list (alphabetical order). Then I right-click it, choose 'Modify' and edit it.

Know what I am talking about? Maybe this is not the right way to edit a SP????

Anyway, it isn't showing up in any SP list...

PS: I am using SQL-Server Management Studio - I hate it, but it is what I have.

Any idea?

homebrew01 SSC Guru Points: 55454 More actions · Answer 3

Is it owned by a schema other than dbo ? If so, it might be at the bottom of the list.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

yankleber (1/18/2010)
Well, normally I find my SPs by looking at the left column (like in the Windows Explorer).

That's called Object Explorer

Anyway, it isn't showing up in any SP list...

Have you refreshed object explorer since creating it?

Is Object explorer connected to the correct server (it doesn't have to be connected to the same server as the query window is)

Are you looking in the correct database?

Are you looking under the correct schema?

This will give you the name and schema. The database that you're running in when it shows up is the database you should be looking under.

SELECT s.name as SchemaName, p.name as ProcedureName

FROM sys.procedures p

inner join sys.schemas s on p.schema_id = s.schema_id

WHERE p.name LIKE '%CatchError%'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Yan Kleber Old Hand Points: 307 More actions · Answer 5

Hey all!

I had a severe Internet problem so I couldnt login and answer earlier.

Anyway, I am feeling super-dumb... The problem about the missing SP is that I just forgot to refresh my SP list, so it wasn't showing up. I dont know what happened to me. I think I am too tired...

Thanks for helping!

Panks-913490 SSCrazy Points: 2131 More actions · Answer 6

too much work and no play makes jack a dull boy. 🙂

holtshousens SSC Enthusiast Points: 128 More actions · Answer 7

Glad you found the SP. Im sure you will never forget to refresh the SP list in future 😉

homebrew01 SSC Guru Points: 55454 More actions · Answer 8

Sean-1112851 (1/21/2010)
Glad you found the SP. Im sure you will never forget to refresh the SP list in future 😉

Same with tables, views ... becomes 2nd nature after a bit.

Yan Kleber Old Hand Points: 307 More actions · Answer 9

Actually this is not the first time it happen to me. I work with MSSQL for years, and from times to times I just forget about that stupid needing on refresh the list!

I think that it have to do with the good old history about Microsoft that do not care about keep consistence among their own tools. Since SQL Management Studio follows the very same visual schema as Windows Explorer, and since Windows Explorer always update objects automatically maybe a short-circuit in my brain made me to suppose for a moment that it just had refreshed by itself - and forgot to refresh myself.

Anyway, it wouldnt hurt if it automatically refreshed the list after a creation/removal operation as WE does. Actually I don't know why they didn't do that this way. Lazy coders, probably!

😛