Amour22015
asked on
SQL Server 2008 R2 - How to track parms and Exec
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
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Lets call the SP = StoredProcedure1
So I did this:
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_I D) LIKE 'StoredProcedure1'
But that came up with nothing
Is this correct???
So I did this:
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_I
But that came up with nothing
Is this correct???
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.
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.
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_I D) LIKE '%StoredProcedure1%'
the % is a wildcard variable.
a% = anything starting with a
%s = anything ending with s
%z% = anything with z anywhere.
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_I
the % is a wildcard variable.
a% = anything starting with a
%s = anything ending with s
%z% = anything with z anywhere.
ASKER
Ok,
I did this:
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_I D) 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'.
I did this:
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_I
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'.
ASKER
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???
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.
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.
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
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
ASKER
Thanks this was very helpful
Assuming that you don't have access/premission to see the script of the proc in question, one way is to use information_schema.PARAMET
select * from information_schema.PARAMET
where SPECIFIC_SCHEMA = 'yourprocschema'
AND SPECIFIC_NAME = 'yourprocname'
order by ORDINAL_POSITION