Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

What is causing stored procedure to execute?

Posted on 2015-01-21
8
Medium Priority
?
110 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
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

916 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