Solved

SQL Server 2008 R2 - How to track parms and Exec

Posted on 2014-04-16
11
265 Views
Last Modified: 2014-04-16
Hi and Thanks,

I have a Stored procedure

I would like to find out how to track were the parmetors are coming from and how the stored procedure gets executed???

Is there a way to do this???

If so can you please direct me in the right direction???

Thanks
0
Comment
Question by:Amour22015
  • 4
  • 4
  • 3
11 Comments
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40004244
There are different ways to do this...
Assuming that you don't have access/premission to see the script of the proc in question, one way is to use information_schema.PARAMETERS as in the following:
select * from information_schema.PARAMETERS
where SPECIFIC_SCHEMA = 'yourprocschema'
AND  SPECIFIC_NAME = 'yourprocname'
order by ORDINAL_POSITION
0
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40004255
If you have access to the proc in Management Studio, you can also right click on the proc and select: 'Script stored procedure as' > 'Execute to' > ... and see how you can execute the proc and what the parameters are and their datatypes, etc.
0
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 250 total points
ID: 40004264
you can setup a table in your database temporarly.

When you call the stored procedure you can insert those params right into the table.

So.

Create Table MyProcParams
param1 <type>,
param2 <type>,
executeTime datetime,
executingUser varchar(500),
Context varchar(500)


in your storedproc add a default variable for context

alter sp
...params,
@Context varchar(500) = 'Unknown'

insert into MyProcParams
@param1, @param2, getdate(), system_user, @context


And that way you can update your calls to this stored proc to find out where it's coming from.  You know you're done..


You can also search your database to find out if any other stored procs call the stored proc you're interested in:



Search SP:
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%SP_NAME%'

Open in new window


Search Jobs:
SELECT j.name 
  FROM msdb.dbo.sysjobs AS j
  WHERE EXISTS 
  (
    SELECT 1 FROM msdb.dbo.sysjobsteps AS s
      WHERE s.job_id = j.job_id
      AND s.command LIKE '%SPNAME%'
  );

Open in new window


and as a last resort you can also use sql profiler.  Run that and search for the stored proc in question.
0
 

Author Comment

by:Amour22015
ID: 40004286
Lets call the SP = StoredProcedure1

So I did this:

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE 'StoredProcedure1'

But that came up with nothing

Is this correct???
0
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40004288
I seem to have misunderstood your question. As Kyle has suggested if you can modify the proc you can make it to trace itself as in the previous post.
However if you can't (or don't want to) change the proc, you can set up a trace using SQL Profiler where you can see which login is calling the proc and what parameters are pass to the it.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 40004297
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%StoredProcedure1%'

the % is a wildcard variable.  

a% = anything starting with a
%s = anything ending with s
%z% = anything with z anywhere.
0
 

Author Comment

by:Amour22015
ID: 40004319
Ok,

I did this:
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%StoredProcedure1%'


But the only thing that came up was:
StoredProcedure1

So I am guessing that StoredProcedure1 is the only Stored Procedure within the database call executes: StoredProcedure1

Would that be correct???


AND when I tryed:
SELECT j.name
  FROM msdb.dbo.sysjobs AS j
  WHERE EXISTS
  (
    SELECT 1 FROM msdb.dbo.sysjobsteps AS s
      WHERE s.job_id = j.job_id
      AND s.command LIKE '%StoredProcedure1%'
  );


I got:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'sysjobsteps', database 'msdb', schema 'dbo'.
0
 

Author Comment

by:Amour22015
ID: 40004331
Could this Stored Procedure be executed from a SSIS Package??? and if so is there a way to find out which SSIS Package is exec ing the stored procedure without me having to go though every package???
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 40004332
For the sp search:
that's correct.

And when you get the error that means you don't have permissions to search all of the jobs.  Do you have SQL Server agent running and are any jobs configured?  If the answer to that question is no you can ignore the 2nd query.
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 40004351
If the packages are stored on your local machine then they're just XML and you can search them like you would any windows file.

If they're stored in the database then you're going to need to export them first:
http://sqlblog.com/blogs/jamie_thomson/archive/2011/02/02/export-all-ssis-packages-from-msdb-using-powershell.aspx
0
 

Author Closing Comment

by:Amour22015
ID: 40004364
Thanks this was very helpful
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now