January 14, 2008 at 10:41 am
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
January 14, 2008 at 11:12 am
Was the job step executed using the TSQL job action? If so check which database the stored proc is using in the dropdown and then check the procedure to see if it is pointing to the same database. The step may be executing the procedure remotely or from another database. Look at the execution statement and go to that database.
e.g.
use mydatabase
go
exec mydatabase2.dbo.mystoredproc
go
This is how you execute a stored proc from inside another database.
January 14, 2008 at 11:25 am
it is tsql step. he selected database
in tsql it is just "exec dbo.sp_abc". i went into the database tryed sp_helptext no use. he used this procedure for every database maintainance. but he created individual step for each database.
January 14, 2008 at 11:32 am
Check for a synonym for the stored procedure
January 14, 2008 at 11:41 am
no luck there are no synonyms
January 14, 2008 at 11:44 am
hmm. this is interesting.
try to do a search on sys.procedures.
select *
from sys.procedures
where name like '%dbo.sp_abc%'
January 14, 2008 at 11:56 am
Also, just an FYI you should not named stored procedures sp_Name because this is the Microsoft standard. SQL Server will first look in the system stored procedure list and can also becoming confusing for those looking at the system.
January 14, 2008 at 12:01 pm
i tryed sys.all_object, sys.procedures but no luck.
January 14, 2008 at 12:05 pm
What if the stored procedure is encrypted? I don't think sp_helptext will display anything if it's encrypted in syscomments.
Greg
Greg
January 14, 2008 at 12:14 pm
sp_help will work on encrypted procs. when u use sp_helptext it says it has encrypted text.
January 14, 2008 at 12:15 pm
The only other thing it could be is you do not have rights to see the procedure. Confirm that you have the appropriate credentials.
What if the stored procedure is encrypted? I don't think sp_helptext will display anything if it's encrypted in syscomments
Greg if it is encrypted he still should be able to see it in sys.procedures.
January 14, 2008 at 12:18 pm
Are you sure you're in the correct database? Perhaps you got lucky and your predecessor actually DID store this thing in Master (where the sp_ stuff should live).
Have you checked Master for it?
----------------------------------------------------------------------------------
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?
January 14, 2008 at 12:19 pm
If it is encrypted, it should return 'The text for object 'sp_abc' is encrypted.' It should not return 'Object does not exist'
make sure the database you are looking is the right one...
January 14, 2008 at 1:35 pm
Just as additional info to Matts post:
http://www.sqlservercentral.com/articles/Performance+Tuning/sp_performance/850/
Best Regards,
Chris Büttner
January 14, 2008 at 4:59 pm
I'll just bet the phantom proc is stored in the Master database 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply