Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 114
  • Last Modified:

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.
0
Jay Carax
Asked:
Jay Carax
1 Solution
 
Lee SavidgeCommented:
I'd use SQL Profiler and limit it to the server that you think it is running on.
0
 
OriNetworksCommented:
Looking at the sql server agent error logs may give some detail as to which step of any job is failing. Looking at the properties of the job you may be able to determine connection details or looks at the details for each step to see what it is attempting to run.

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.
0
 
LowfatspreadCommented:
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 ...?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Jesus RodriguezIT ManagerCommented:
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.
0
 
Jesus RodriguezIT ManagerCommented:
This tool is free and will help you to find the Info
http://www.apexsql.com/sql_tools_search.aspx
0
 
Jay CaraxAuthor Commented:
Ran a trace and the host/account it was using was part of SCOM - something was missed during a migration...
0
 
Hec RamseyCommented:
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.
1
 
Jay CaraxAuthor Commented:
Thanks Hec - I'll bear that in mind for future reference
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now