AXISHK
asked on
Find a location of stored procedure
A SQL Server job has called a stored procedure but is there a quick way to check where does this stored procedure located ?
Tks
EXEC @return_value = [dbo].[CleanDBLog]
Tks
EXEC @return_value = [dbo].[CleanDBLog]
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try this
SELECT job_id, [name] FROM msdb.dbo.sysjobsASKER
Run with error, any idea ? Tks
TSQL.png
TSQL.png
ASKER
Is It possible to list the active SQL job only ?
Verify the quotation. Because it's inside a string you need to duplicate quotations.
Just check with my example. There's two '' before and after the stored procedure name. Then one more for close the string.
Just check with my example. There's two '' before and after the stored procedure name. Then one more for close the string.
ASKER
Still not correct,..
EXECUTE sp_MSForEachDB
'USE ?;
SELECT DB_NAME();
SELECT * FROM sys.procedures WHERE name = ''CleanSysDatabaseLog''
EXECUTE sp_MSForEachDB
'USE ?;
SELECT DB_NAME();
SELECT * FROM sys.procedures WHERE name = ''CleanSysDatabaseLog''
If you want to know which jobs are running at the moment just run the following query:
select job.name, act.start_execution_date, act.last_executed_step_date, act.last_executed_step_id, act.stop_execution_date
from msdb.dbo.sysjobs job
inner join msdb.dbo.sysjobactivity act
on job.job_id = act.job_id
where act.start_execution_date is not null
and act.stop_execution_date is nullSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>is there a quick way to check where does this stored procedure located ?
Just to state the obvious, it's not a good programming practice to tell SQL Server to 'go fish' for an object.
You'll want to explicitly state where it's at.
Just to state the obvious, it's not a good programming practice to tell SQL Server to 'go fish' for an object.
You'll want to explicitly state where it's at.
ASKER
Tks
ASKER