?
Solved

What is causing stored procedure to execute?

Posted on 2015-01-21
8
Medium Priority
?
108 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 2000 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

719 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