Parameter Value Logging

Question

Parameter Value Logging

Zeid Hadi

SSC Rookie

Points: 29
More actions
January 31, 2008 at 11:38 am

#202870

I wonder if anybody can help.
I would like to log the parameter values of running stored procedures when they error. All my stored procedures have try catch handling blocks. So if possible I would like one statement that can capture the runtime values of the input parameters of the stored procedure when it errored - rather than separately accessing them one by one and then formatting and logging the results. ie.
error: Get codes, description and run time values of the input parameters.
I need to trace some issues - and I can not use profiler
I only need to log on an "error"
and more importantly I have hundreds of stored procedures to trace this way (hence why I really do not want to do this manually)
Ideally is there not someway you can get a list of parameter values that entered the stored procedure in an XML format ?
I am guessing someone else has come across this requirement...Perhaps I can use profiling but again I would like only to log on specific errors. (this is a live production servers that is used for a highly transactional application)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 1

The one approach that occurs to me is to use SMO to walk your procedures, identify the parameters, and alter the CATCH statements with an additional insert to a log table. I'm not aware of any other trick inside TSQL that identifies the parameters except referring to the parameters themselves.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Zeid Hadi SSC Rookie Points: 29 More actions · Answer 2

Thanks - this is what I had throught of at first - but the problem lies in the fact that you can not reference a parameter through its "name". So once you have the name of the paramter through SMO you cant dynamcialy set another variable to its value without as you say naming it directly. IE there is no GetValuebyName('name of paramter') functionality that I know of.

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 3

But if you were using SMO as a code generation utility, it wouldn't matter that you had to use the name would it? I mean if we assume that you did something along the lines of creating an audit table that had a procedure name column and an XML column for values along with other record keeping stuff like run date, whatever. Then, using SMO, you walk all the procedures in the system, get the list of parameters in each procedure and then generate an insert statement concating those parameters into a proper XML syntax, using the names. Finally you append that to the error trapping within the procedures, using SMO again... It'd take a week or so to code, but then you'd be done & you could run it automatically as needed. Not easy to do, but hardly impossible.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Zeid Hadi SSC Rookie Points: 29 More actions · Answer 4

Ah I got you now - so you mean use SMO as a code generator so each stored prcoedure will have its own code that wil need to be placed within the error handling routine...

Thanks for idea...!

Zeid