Solved

What is causing stored procedure to execute?

Posted on 2015-01-21
8
106 Views
Last Modified: 2015-07-17
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
Comment
Question by:Jay Carax
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 500 total points
ID: 40561899
I'd use SQL Profiler and limit it to the server that you think it is running on.
0
 
LVL 17

Expert Comment

by:OriNetworks
ID: 40562077
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 40562104
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 40564823
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
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 40564829
This tool is free and will help you to find the Info
http://www.apexsql.com/sql_tools_search.aspx
0
 

Author Comment

by:Jay Carax
ID: 40568319
Ran a trace and the host/account it was using was part of SCOM - something was missed during a migration...
0
 

Expert Comment

by:Hec Ramsey
ID: 40876857
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
 

Author Comment

by:Jay Carax
ID: 40887115
Thanks Hec - I'll bear that in mind for future reference
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question