Jay Carax
asked on
What is causing stored procedure to execute?
Hello
We have a stored procedure that updates an SSRS report that details our disk space consumption. This used to work fine, however a few months ago, my boss migrated a few SQL instances and now the report has stopped working for those migrated instances.
We'd like to find out more and I figured tracking which SQL agent job (if indeed it was that, it could also be ActiveBatch) was triggering this SP to execute would be a logical place to start. How can I find what causes the execution? Also, is there a way to find out what procedures are executed when a SQL agent job is running? Would a server side trace be the only way to go with this or are there alternatives?
Our environment consists of hundreds of SQL instances and it's my third day here so I am not at all familiar with the infrastructure yet. What I do know is that the instance on which the proc runs is SQL Server 2005
Any help would be appreciated.
We have a stored procedure that updates an SSRS report that details our disk space consumption. This used to work fine, however a few months ago, my boss migrated a few SQL instances and now the report has stopped working for those migrated instances.
We'd like to find out more and I figured tracking which SQL agent job (if indeed it was that, it could also be ActiveBatch) was triggering this SP to execute would be a logical place to start. How can I find what causes the execution? Also, is there a way to find out what procedures are executed when a SQL agent job is running? Would a server side trace be the only way to go with this or are there alternatives?
Our environment consists of hundreds of SQL instances and it's my third day here so I am not at all familiar with the infrastructure yet. What I do know is that the instance on which the proc runs is SQL Server 2005
Any help would be appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
so you know the stored procedure and ssrs report which is affected...
and you know the sql instances affected...
have you looked at the procedure code itself to see what it expects?
Have you asked your boss to view the support documentation for the project ...?
and you know the sql instances affected...
have you looked at the procedure code itself to see what it expects?
Have you asked your boss to view the support documentation for the project ...?
Check the connection on the report to the Instances that you need. If the store procedure runs fine then must be the connection to the instances inside the report that is failing.
This tool is free and will help you to find the Info
http://www.apexsql.com/sql_tools_search.aspx
http://www.apexsql.com/sql_tools_search.aspx
ASKER
Ran a trace and the host/account it was using was part of SCOM - something was missed during a migration...
use the DMV sys.dm_sql_referencing_entities on it. It will tell you what other objects touch it.
BTW sys.dm_sql_referenced_entities shows what objects the proc touches.
BTW sys.dm_sql_referenced_entities shows what objects the proc touches.
ASKER
Thanks Hec - I'll bear that in mind for future reference
SQL Profiler will also be the tool to use for further inspection. It is HIGHLY recommended that you first get familiar with the entire environment as soon as possible.