March 8, 2018 at 11:04 pm
Comments posted to this topic are about the item Default CONTEXT_INFO()
March 8, 2018 at 11:04 pm
Nice question, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
March 9, 2018 at 12:52 am
This is a little inconsistency, since you can't set CONTEXT_INFO to NULL.DECLARE @ci VARBINARY(128) = CONTEXT_INFO();
--<do some stuff as temporary setting CONTEXT_INFO to something else>
SET CONTEXT_INFO @ci
would end with an error 2743 'SET CONTEXT_INFO option requires varbinary (128) NOT NULL parameter.'
To make it work, you have to use an ISNULL:DECLARE @ci VARBINARY(128) = ISNULL(CONTEXT_INFO(), 0x00)
--<do some stuff as temporary setting CONTEXT_INFO to something else>
SET CONTEXT_INFO @ci
PS: I use CONTEXT_INFO to bypass my triggers e.g. in deployment / manual update scripts. Each trigger starts with an IF CONTEXT_INFO() = 0x1000 RETURN; so that I could just set it to this value and the trigger would be ignored for this specific session (but not for all the other people out there and there is no risk to forgot to reenable the trigger)
God is real, unless declared integer.
March 9, 2018 at 8:26 am
A bigger question is why use CONTEXT_INFO() instead of SESSION_CONTEXT() if you're on SQL Server 2016?
CONTEXT_INFO is pretty neat but you're restricted to one value. You can use individual bits in the value you store to use that one value to store a number of values but that's kind of a pain in the neck.
SQL Server 2016 introduced SESSION_CONTEXT() which gives you 256kb worth of key/value pairs. That gives a lot more flexibility.
March 9, 2018 at 9:47 am
Elementary, dear Watson. 🙂
Thanks, Steve, and have a great weekend.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply