Usages of CONTEXT_INFO

  • yousef Ekhtiari

    Ten Centuries

    Points: 1283

    Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/yEkhtiari/2765.asp

  • Jeff Moden

    SSC Guru

    Points: 996862

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996862

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Rohit D

    SSC Enthusiast

    Points: 152

    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.

  • Mark Harr

    SSCrazy Eights

    Points: 9760

    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

  • Chris Roesener

    SSC Veteran

    Points: 235

    Does anyone know if the CONTEXT_INFO can be tracked in Profiler traces?

  • Timothy-313907

    Ten Centuries

    Points: 1226

    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!

  • Tim Chapman-218780

    Ten Centuries

    Points: 1051

    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

  • Deepa Gheewala

    SSC-Addicted

    Points: 432

    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

  • Ed Thompson

    SSCommitted

    Points: 1660

    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?

  • Tore Bostrup-382308

    Old Hand

    Points: 368

    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

  • Lisa Slater Nicholls

    Hall of Fame

    Points: 3311

    >>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<

  • Lisa Slater Nicholls

    Hall of Fame

    Points: 3311

    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<

  • Rafiuddin Syed

    Valued Member

    Points: 59

    Very Good Article, I was not aware of it...

  • David NienDuy Nguyen

    SSC Enthusiast

    Points: 136

    Very useful article ! I don't know about this until now.

    Thank you. David N Nguyen.

Viewing 15 posts - 1 through 15 (of 27 total)

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