August 28, 2011 at 10:02 pm
Comments posted to this topic are about the item CONTEXT_INFO() and the SQL Calling Stack
August 29, 2011 at 4:26 am
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"!
August 29, 2011 at 6:19 am
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
August 29, 2011 at 6:30 am
In line with that issue, are there tools out there to write Apect Oriented Procedures?
August 29, 2011 at 11:16 am
'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
August 29, 2011 at 11:25 am
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.
August 29, 2011 at 11:27 am
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.
August 29, 2011 at 12:57 pm
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.
August 30, 2011 at 8:42 am
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.
August 30, 2011 at 9:39 am
Great information. Thanks for putting it together.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 2, 2011 at 12:04 pm
context_info() is a good way to fake a global variable which is not available in sql. (something that will survive "go" and "use" statements).
August 31, 2023 at 11:41 am
context_info is session global variable, so any object could have its own use, can modify it, etc. Besides that, you have to set it at the beginning and reset it at the end. Better solution would be to have an additional parameter that has call stack.
create procedure P1(@callstack varchar(128), @X1 varchar(10))
as begin
set @callstack=@callstack+'/'+object_name(@@procid)
print @callstack
end
go
create procedure P2(@callstack varchar(128), @X1 varchar(10))
as begin
set @callstack=@callstack+'/'+object_name(@@procid)
print @callstack
exec P1 @callstack, @X1
print @callstack
end
go
exec P2 'top','XX '
go
drop procedure P2
drop procedure P1
Result:
top/P2
top/P2/P1
top/P2
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply