Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Usages of CONTEXT_INFO Expand / Collapse
Author
Message
Posted Sunday, December 17, 2006 10:02 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 22, 2014 4:35 AM
Points: 553, Visits: 26
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/yEkhtiari/2765.asp


Post #331105
Posted Wednesday, December 20, 2006 10:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #332025
Posted Wednesday, December 20, 2006 10:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #332029
Posted Thursday, December 21, 2006 3:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 26, 2011 10:51 AM
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.

Post #332073
Posted Thursday, December 21, 2006 5:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, October 19, 2014 4:48 AM
Points: 1,327, Visits: 156

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
Post #332123
Posted Thursday, December 21, 2006 7:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 12:02 PM
Points: 169, Visits: 101
Does anyone know if the CONTEXT_INFO can be tracked in Profiler traces?
Post #332153
Posted Thursday, December 21, 2006 7:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 5:29 AM
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!
Post #332157
Posted Thursday, December 21, 2006 7:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 12, 2009 7:37 AM
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

Post #332166
Posted Thursday, December 21, 2006 8:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 3, 2010 3:56 AM
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
Post #332186
Posted Thursday, December 21, 2006 9:04 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 10:10 PM
Points: 824, Visits: 109

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?




Post #332219
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse