March 14, 2011 at 9:51 am
I am having a stored procedure,and i want to find which job is executing that particular SP....plz any suggestions
March 14, 2011 at 10:50 am
USE msdb
GO
DECLARE @proc_name SYSNAME = 'stored_procedure_name' ;
SELECT *
FROM dbo.sysjobsteps
WHERE command LIKE '%' + @proc_name + '%' ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2011 at 11:01 am
select s.name, j.command from msdb.dbo.sysjobsteps j
inner join msdb.dbo.sysjobs s
on s.job_id=j.job_id
where command like '%sqlbackup%'
March 14, 2011 at 11:01 am
The above will work if it's in the command of the jobstep. If it's buried in SSIS, you're SoL. You can try a trace to track when it's being called to help narrow the possibilities down.
Also, you'll need to run this on any machine that has a linked server to your machine, as you could call it foreignly.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 14, 2011 at 11:42 am
Craig Farrell (3/14/2011)
The above will work if it's in the command of the jobstep. If it's buried in SSIS, you're SoL. You can try a trace to track when it's being called to help narrow the possibilities down.Also, you'll need to run this on any machine that has a linked server to your machine, as you could call it foreignly.
You can import the SSIS package into BIDS and save it to your file system. It saves as a dstx file, which is an XML file. You can then search it for the name of the stored procedure.
Good point about the linked server. I think I would only go that route after other searches didn't bear out.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply