SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CONTEXT_INFO() and the SQL Calling Stack


CONTEXT_INFO() and the SQL Calling Stack

Author
Message
deepforest
deepforest
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 621
Comments posted to this topic are about the item CONTEXT_INFO() and the SQL Calling Stack
R.P.Rozema
R.P.Rozema
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1087 Visits: 1685
Nicely done! I hadn't seen @@procid before and I see, next to this one some great usages for it. So thanks a lot for the pointer.

There is a potential caveat in 2 of your functions: since you're appending integers into the binary string, there can be any sort of "characters" in the string. i.e. you should not use len() on that binary string, instead you should use datalength(). For the rest, good trick to add to our "tool kits"!



Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9770 Visits: 13349
Nice article!
I've been using this trick for years and I must say it works quite well. The only thing I don't like about it is the need to add custom code to handle the call stack, which is something that has to be done in every single procedure to consider it reliable.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
klini
klini
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 241
In line with that issue, are there tools out there to write Apect Oriented Procedures?
Misha_SQL
Misha_SQL
SSC Eights!
SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)

Group: General Forum Members
Points: 954 Visits: 1003
Thank you for the article. Could you expalin how this line works:

set context_info @b

Why is there no equal sign?



deepforest
deepforest
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 621
'set context_info' is a statement, different from 'set' in setting a local variable or updating a column, the syntax is without the equal sign
deepforest
deepforest
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 621
Gianluca Sartori (8/29/2011)
Nice article!
I've been using this trick for years and I must say it works quite well. The only thing I don't like about it is the need to add custom code to handle the call stack, which is something that has to be done in every single procedure to consider it reliable.


I agree, but so far I couldn't find anything simple enough without writing/reading the data to/from tables.
deepforest
deepforest
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 621
R.P.Rozema (8/29/2011)
Nicely done! I hadn't seen @@procid before and I see, next to this one some great usages for it. So thanks a lot for the pointer.

There is a potential caveat in 2 of your functions: since you're appending integers into the binary string, there can be any sort of "characters" in the string. i.e. you should not use len() on that binary string, instead you should use datalength(). For the rest, good trick to add to our "tool kits"!

Agreed, good catch.
Greg Grater
Greg Grater
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 27
Another great use for CONTEXT_INFO is auditing actions. Most web/windows applications create a helper class that manages opening a connection to the database. You can augment this helper class to store all kinds of session information by simply setting that information immediately after opening the connection.

For example. typically, the database connection is created through integrated security based on the app's security context which does *not* include the actual user's login. The user log's in through a login badge (forms based security) to the web app and the web app connects to the database through a common connection string.

I've used the common connection helper in the web app to inject a simple set of the CONTEXT_INFO session variable to store their Forms Based Security login ID. Then, as each stored procedure is called *with* that connection, I can pull the application login from CONTEXT_INFO and store it with the record that was updated or inserted (or even logically deleted). ...Instant Auditing of which user changed the data without the overhead of passing the information with every call.

Essentially, if you create a common connection helper class in your application, you can capture all kinds of session information in Context_INFO and use that information on each subsequent call to the database through that connection. (Think IP Address, Browser info, etc.)

Great post about a little known, but very powerful SQL Server session variable.
Mauve
Mauve
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1736 Visits: 2054
Greg Grater (8/29/2011)
Another great use for CONTEXT_INFO is auditing actions. Most web/windows applications create a helper class that manages opening a connection to the database. You can augment this helper class to store all kinds of session information by simply setting that information immediately after opening the connection.

For example. typically, the database connection is created through integrated security based on the app's security context which does *not* include the actual user's login. The user log's in through a login badge (forms based security) to the web app and the web app connects to the database through a common connection string.

I've used the common connection helper in the web app to inject a simple set of the CONTEXT_INFO session variable to store their Forms Based Security login ID. Then, as each stored procedure is called *with* that connection, I can pull the application login from CONTEXT_INFO and store it with the record that was updated or inserted (or even logically deleted). ...Instant Auditing of which user changed the data without the overhead of passing the information with every call.

Essentially, if you create a common connection helper class in your application, you can capture all kinds of session information in Context_INFO and use that information on each subsequent call to the database through that connection. (Think IP Address, Browser info, etc.)

Great post about a little known, but very powerful SQL Server session variable.

We also use it for auditing actions. In our case we need to know the ID of the User (actor) performing the operation. Note: being a web application all database interaction is via a single Windows account. Our auditing, limited to deletions, is via a trigger. So the trigger needs a piece of information (UserID) passed into the stored procedure. The UserID is put into the CONTEXT_INFO so that it can be referenced by the trigger. Since the delete can also cause CASCADE deletes, those triggers also need the information. CONTEXT_INFO is the way to go.

I wish SQL Server had session scoped properties that can be SET and GET like Oracle. Oracle PL/SQL is much more object-oriented than SQL Server.


(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
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