Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Usages of CONTEXT_INFO


Usages of CONTEXT_INFO

Author
Message
yousef Ekhtiari
yousef Ekhtiari
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 48
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/yEkhtiari/2765.asp



Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45390 Visits: 39941

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45390 Visits: 39941

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Rohit D
Rohit D
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 7

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
Mark Harr
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 191

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
Chris Roesener
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 101
Does anyone know if the CONTEXT_INFO can be tracked in Profiler traces?
Timothy-313907
Timothy-313907
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 721
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
Tim Chapman-218780
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 115

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
Deepa Gheewala
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 91
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
Ed Thompson
SSC Eights!
SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)

Group: General Forum Members
Points: 826 Visits: 122

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?





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search