Solved

What is causing stored procedure to execute?

Posted on 2015-01-21
8
107 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

615 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