February 2, 2010 at 3:22 am
How to capture/retrieve session dependent variables during the execution of procedures.
i.e. temporary local variables, input parameters in nested procedures.
Thanks,
Kishore KK.
February 2, 2010 at 4:17 am
I hope that I understood what you need. If you have Procedure A that activates Procedure B and you want procedure B to know the value of variables in Procedure A, then you can send it as parameter or use a temporary table and insert the values into it (then run select statement in procedure B to get those values). If you meant something else such as which login is using the session? what application is being used? Which host initiated this session? Then you can use build in functions such as suser_sname(), app_name(), host_name(), etc’.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 2, 2010 at 5:14 am
Thanks Adi,
What i am looking exactly is,
We have found few deadlocks during the application is in use. To identify these deadlocks i have written even handler to find procedure name, spid, and deadlock statements.
In this case i need to capture local and input variables(temporary/session dependent variables) values to investigate and avoid deadlocks.
At this level i want to capture variable values before session close.
Help me,
Thanks,
Kishore KK.
February 2, 2010 at 5:42 am
In that case you can use the build in tools in SQL Server to get the deadlocks's information. You can set trace flag 1222 to send information of all deadlocks to SQL Server’s errorlog. You can also use the profiler (or server side trace) and use the event deadlock graph to see the deadlock’s information. Would be much better then writing your own code in order to get this information.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 2, 2010 at 5:55 am
I cannot start Trace or sql profiler because it is production.
when i test same in test environment no issues.
we are getting issue during the users(thousands) are using system.
So, i need to capture through scripts and log value in audit tables to investigate issue at working hours.
So, in this case can you help me.
Thanks,
KK.
February 2, 2010 at 7:02 am
Setting up trace flag 1222 doesn’t cost anything and it won’t be noticed on the production environment. The same can be said about server side trace that catches the event – deadlock graph, so those would be the steps that I’d fallow in order to get more information about the deadlock. If you insist of doing it from your own code, then I guess that you’ll have to modify the procedures so they’ll pass and receive parameters with the variables’ values. As I wrote before I strongly recommend using the build in tools that we already have.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply