Usages of CONTEXT_INFO
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/yEkhtiari/2765.asp
-
How simple... how elegant... I never knew you could do this... nicely done.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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…
- SET @MyStatus = CAST('someprocname is at Step X' AS VARBINARY(128))
- SET CONTEXT_INFO = @MyStatus
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.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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.
- This may destroy the ability to use connection pooling. Then each user will have there own connections, increasing resources on the both the db server and web (or app) server. Scalability and performance of your site will likely decrease.
- Or, if connection pooling still works when using context_info, then you will run the risk of having the Context_info reset on the connection, or being used by the wrong user. In an ASP app, there is no guarantee that you will get the same connection each time. And, other users will share the same connection.
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.
Mark -
Does anyone know if the CONTEXT_INFO can be tracked in Profiler traces?
-
Wow, that's a pretty useful tip, something I never knew about. As you said in your article, I don't really need to use it right now but I'll definitely keep it in the back of my mind for the future. Thanks for sharing!
-
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
-
Hello
It is really useful..
since long i was in search of read only tables.. I think this is one of the better way.
I followed all the steps that you have mentioned in your article, but i did not create any trigger.
as i dont want to use triggers.
what should be result according to you?
Is there any other way without using triggers
-
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?
-
We are using the technique with connection pooling (although a WinForms app) for user/application context information required for logging purposes (used in triggers). By always setting the CONTEXT_INFO as part of our connection logic, we do not have to worry about "leftovers" from previous users. But in any case, I believe the connection reset that takes place when reusing a connection in the pool clears the previous user's CONTEXT_INFO (but you should verify this yourself).
Regards,Tore Bostrup
-
>>As long as the trigger is enabled you can make sure that the logic will work.
Maybe could also create a database-level trigger using the same technique, to make sure that the table wasn't altered or dropped outside of an approved way, to prevent this?
Great article, thank you...
>L<
-
I thought maybe we could do this with EVENTINFO() in a trigger, but the calling proc isn't included in the schema (would be nice!).
Looking at the docs, it seems maybe you could use context info more pervasively in a system and then do something like this:
SELECT context_info AS MyCtxInfo
FROM
sys.dm_exec_requests
WHERE
session_id = @@SPID
AND request_id = CURRENT_REQUEST_ID();
... ? but I am not sure, I don't understand how CURRENT_REQUEST_ID() works or whether you will ever see more than one result from the above code.
Anyone??
>L<
-
Very Good Article, I was not aware of it...
-
Very useful article ! I don't know about this until now.
Thank you. David N Nguyen.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply