How simple... how elegant... I never knew you could do this... nicely done.
This is pretty cool... we have several complex, multi-step store procedures and some folks have put some pretty complex logging procedures in them just so they can query a table to see "how it's doing". With just a little forethought, you can add these simple lines to the proc and let the SysProcesses table take care of it…
With a bit more forethought, you could make a function that you pass the SPID to and it will automatically get the Context_Info from SysProcesses and decode it for readability.
If you "word" it correctly, you could even put a timestamp in the Context_Info and have a view decode when the step started, how long the step has been running, the proc name (can save some space by passing the ID of the proc contained in sysobjects), etc. Then, just select from the view... use a WHERE for spid if you want. Combine that with the other available columns such as Host_Name, etc, and you have some pretty good info.
Thanks, Yousef... nice tip.
Very Nice usage of CONTEXT_INFO. thanks for sharing.
Have used CONTEXT_INFO for the Multilingual solution, where the clients call can come in for any language Data. Since the Connection pool is used to serve the data, before executing the SP we set the CONTEXT and within the SP, query the Sysprocesses to figure out the Language of the current SPID and serve the appropriate data.
Regarding using this technique in an ASP environment, I would perform some additional testing. I'm not sure how Context_info applies when using connection pooling, but either way would appear to have problems.
Even still, thanks for the article, Yousef. Especially for the first intended purpose, this appears to the an elegant solution to your problem of restricting updates.
Yep, its neat, and I wasn't aware of it. Does anyone on here know if there is a way (other than using this), to extract the calling stored procedure in a trigger? What would be nice if there was a way in 2000 or 2005 to be able to determine in a trigger the procedure name (or just if it was a batch) that invoked that trigger. Would be great for auditing purposes.
Tim
Intresting article. Thank you Yousef.
I have one concern using this in SQL 2000. If you need to make sure you reset the Context_Info at the end of the procedure and an error occurs before it reaches that line in the stored procedure, won't it fail to execute the reset command?
