Not able to locate stored proc. but it is running in job step

Question

Not able to locate stored proc. but it is running in job step

Viewing 15 posts - 16 through 30 (of 33 total)

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

I'll just bet the phantom proc is stored in the Master database

Agreed.

I have exhausted every other solution. The only place the stored procedure can be and still function properly is the master database. You can use the query below to print a list of all databases in the instance that have that stored procedure. This query should validate Matt and Jeff's assumptions.

EXEC master..sp_MSForeachdb '

USE [?]

IF EXISTS(select * from sys.procedures WHERE name = ''sp_test'')

BEGIN

PRINT CONVERT(VARCHAR(50),''PROCEDURE IN DATABASE: '' + ''?'')

END

'

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

suma (1/14/2008)
i tryed sys.all_object, sys.procedures but no luck.

Sorry, didn't see that... if you can't find it there, then I don't know where it might be, especially if you're logged in with "SA" privs...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ianT SSCrazy Points: 2956 More actions · Answer 3

ianT

SSCrazy

Points: 2956

January 16, 2008 at 7:33 am

#768648

what happens if you run profiler on the job?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Jeff Moden (1/14/2008)
suma (1/14/2008)
i tryed sys.all_object, sys.procedures but no luck.
Sorry, didn't see that... if you can't find it there, then I don't know where it might be, especially if you're logged in with "SA" privs...

Remember - the sys.* views only look at the current database context, even from within Master. So - not being in the right DB - you would still see nothing. Meaning - if you're not in master at the time - you won't see it in sys.all_objects (even if it's a sp_* procedure you can EXECUTE from the user DB.)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

Haven't tried it, Matt, but is that true even when you're logged in as a System Admin?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

From what I can tell - yes.

If I log in to the DB as SA and go to a user DB, then query for an object I know is in master - no joy.

Same in reverse - go to Master and look for an object that lives in a userDB - also no joy.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

By the way - another trick if you still can't find this thing:

Dust off a copy of SQL2000's Query analyzer, and use its Object Search ability to find this thing (since it will query against all DB's).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

bjiang SSC Enthusiast Points: 158 More actions · Answer 8

Search in the Master database.

If you create a stored procedure in the Master database and give it name starting with 'sp_', you can call it in any database.

Bob Fazio SSChampion Points: 10794 More actions · Answer 9

This will check all databases

EXEC sp_MSforeachdb 'USE ?;SELECT ''?'' as dbname,* FROM sys.all_objects WHERE NAME LIKE ''%cycle%'''

Bob Fazio SSChampion Points: 10794 More actions · Answer 10

And one reason that it might appear to be phantom is the schema. If it is owned by something other than dbo, it might not show up if just searching with sys.objects.

Stuart Folk SSCrazy Points: 2424 More actions · Answer 11

Stuart Folk

SSCrazy

Points: 2424

January 16, 2008 at 6:21 pm

#769028

Have you researched the system databases?

max-594311 Old Hand Points: 339 More actions · Answer 12

thanks for all your replies

yes stored proc is in master database.

Steve T SSCommitted Points: 1994 More actions · Answer 13

max (1/14/2008)
hi,
i recently joined a company as dba. there is a job step which executes a storedprocedure for database maintenance purpose. but when i went into database and tried sp_helptext spname. but i got error say the object not exists. but the job runs fine every week. i am wondering how can i find this procedure?
thanks

Sounds like you don't have permission to that particular store procedure using your current login. You may be using an account either NT or SQL that dosen't have enough rights.

If you don't have enough access to see the dbo.storeproc, you wouldn't have enough rights to see the "SELECT * FROM sys.procedures" You will only results of stored procs that you are owner or have permissions to.

You can test this by login in as "sa" and then doing sp_helptext "storedproc". Don't forget as someone mentioned, make sure you're in the right database. Also if you logged in as SA and then doing a SELECT * FROM sys.procedures, you should get a result set.

Hope this helps.

Shane Revell-305822 Old Hand Points: 301 More actions · Answer 14

Hi Max

Check whether the owner of the object is dbo - it may not be. Even if you have sysadmin privileges, if you are connecting to Query Analyzer using your own credentials, you will not be able to run sp_helptext against any object that is owned by something other than dbo.

If it is the case that the object owner is non-dbo you would need to connect to Query Analyzer as the SQL user who owns the object. Then sp_helptext should work.

Shane Revell-305822 Old Hand Points: 301 More actions · Answer 15

Sorry Max

I missed the part in your earlier post where you said the job step contains "exec dbo.sp_abc". If that is the case, ignore my previous post.