February 12, 2018 at 11:58 pm
Comments posted to this topic are about the item Context_info() data
February 13, 2018 at 12:04 am
Interesting question, thanks Steve
Never used this before, so definitely learned something new
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
February 13, 2018 at 1:08 am
The MSDN article to which you link and which you cite in support of your (incorrect) answer states "Associates up to 128 bytes of binary information with the current session or connection." So the correct answer is a.
February 13, 2018 at 1:25 am
You can only pass in a binary variable. The fact you're converting other datatypes to binary is irrelevant 😉
Agree with the above that the answer should be a).
February 13, 2018 at 1:52 am
Binary? 57% think so at the moment.
February 13, 2018 at 2:09 am
Yup, should definitely be Binary Data, the fact that some other data types get implicitly converted is irrelevant.
February 13, 2018 at 8:27 am
Answer A is the correct answer. Conversion of data to a type means it became that type.
February 13, 2018 at 8:57 am
As others have mentioned, the correct answer is actually (a) for "binary data" only. Implicit conversions, while they work most of the time, shouldn't be relied upon, and in this case, it isn't doing what you think it is. You can specify an INT value when executing SET CONTEXT_INFO, but that isn't exactly what is getting "passed in", as the following test demonstrates.
Executing the following:
SET CONTEXT_INFO 12;
DECLARE @ImplicitConvert VARBINARY(128) = 12;
SELECT CONTEXT_INFO() AS [FullValue],
CONVERT(INT, CONTEXT_INFO()) AS [BackToInt],
CONVERT(INT, CONVERT(BINARY(4), CONTEXT_INFO())) AS [BackToIntViaBinary(4)],
CONVERT(VARBINARY(128), 12) AS [ExplicitConvert],
@ImplicitConvert AS [ImplicitConvert];
Returns:
FullValue BackToInt BackToIntViaBinary(4) ExplicitConvert ImplicitConvert
0x0000000C00000....0000 0 12 0x0000000C 0x0000000C
I partially truncated the "FullValue" value as we don't need to see the remaining (out of 128) bytes worth of 0's. But as you can see, an INT was passed in, but you cannot get the INT back out unless you truncate the value down to the 4 bytes of an INT. While the "12" is correctly (implicitly) converted to 0x0C, it seems a bit misleading to say that this "works". But along those lines, even just saying "binary" data isn't enough since explicitly converting to VARBINARY(128) has the same effect as when passing in just "12".
The only reliable datatype to set CONTEXT_INFO to, where you get out exactly what you put in, is BINARY(128), as you can see below:
DECLARE @Bin128 BINARY(128) = 23;
SET CONTEXT_INFO @Bin128;
SELECT CONVERT(INT, CONTEXT_INFO()) AS [BackToInt];
-- 23
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
February 13, 2018 at 9:02 am
Can I have my point(s) now please?
February 13, 2018 at 9:09 am
According to the Microsoft docs you can pass binary or a constant that is implicitly convertible to binary making Steve's answer correct ( but not popular 🙂 )
binary_str
Is a binary constant, or a constant that is implicitly convertible to binary, to associate with the current session or connection.+
@ binary_var
Is a varbinary or binary variable holding a context value to associate with the current session or connection.
February 13, 2018 at 9:10 am
I thought that it was only Binary data. It has been a while since I had to work with it and without going back and finding the code I wrote I took a guess and was told I chose wrong when I selected A as my answer.
February 13, 2018 at 9:18 am
TUellner - Tuesday, February 13, 2018 9:09 AMAccording to the Microsoft docs you can pass binary or a constant that is implicitly convertible to binary making Steve's answer correct ( but not popular 🙂 )binary_str
Is a binary constant, or a constant that is implicitly convertible to binary, to associate with the current session or connection.+@ binary_var
Is a varbinary or binary variable holding a context value to associate with the current session or connection.
I think Tom sees the question as I do. The question is (emphasis mine):
On SQL Server 2016, what type of data type can I pass into SET CONTEXT_INFO?
For me, the question says what can I pass into the command, not what data is stored for context and returned by CONTEXT_INFO(). I can pass in binary or numeric data that converts to binary. The system will make the conversion, and since this is version specific, that is correct.
The intention isn't to trick you, but show that you can pass in these two types of data. You can't pass in a string, a date, or other types.
February 13, 2018 at 9:20 am
Tom and Lynn, please see my reply here on Page 1: https://www.sqlservercentral.com/Forums/FindPost1923283.aspx
Working with CONTEXT_INFO can be a little tricky, especially because the documentation is a bit misleading. While it is correct more often than not, the documentation isn't necessarily gospel. This is a case where it should probably be revised. Just because one can technically set CONTEXT_INFO to an INT (or even UNIQUEIDENTIFIER), it is of little practical value if one cannot easily get the same value back out. Yes, an extra CONVERT can be thrown in there, but how often do people know to do that? Really, it should just say that CONTEXT_INFO is BINARY(128) (not even VARBINARY(128) ) and have it work as most people are expecting it to. When I have time I will try to make that change to the documentation.
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
February 13, 2018 at 9:27 am
Steve Jones - SSC Editor - Tuesday, February 13, 2018 9:18 AMTUellner - Tuesday, February 13, 2018 9:09 AMAccording to the Microsoft docs you can pass binary or a constant that is implicitly convertible to binary making Steve's answer correct ( but not popular 🙂 )binary_str
Is a binary constant, or a constant that is implicitly convertible to binary, to associate with the current session or connection.+@ binary_var
Is a varbinary or binary variable holding a context value to associate with the current session or connection.I think Tom sees the question as I do. The question is (emphasis mine):
On SQL Server 2016, what type of data type can I pass into SET CONTEXT_INFO?For me, the question says what can I pass into the command, not what data is stored for context and returned by CONTEXT_INFO(). I can pass in binary or numeric data that converts to binary. The system will make the conversion, and since this is version specific, that is correct.
The intention isn't to trick you, but show that you can pass in these two types of data. You can't pass in a string, a date, or other types.
Understand, just that when I was working with it I was working with binary data, not numeric data that was being implicitly converted. So this is what I was basing my choice when answering the question without looking anything up in Books Online.
February 13, 2018 at 9:30 am
Solomon Rutzky - Tuesday, February 13, 2018 9:20 AMTom and Lynn, please see my reply here on Page 1: https://www.sqlservercentral.com/Forums/FindPost1923283.aspxWorking with CONTEXT_INFO can be a little tricky, especially because the documentation is a bit misleading. While it is correct more often than not, the documentation isn't necessarily gospel. This is a case where it should probably be revised. Just because one can technically set CONTEXT_INFO to an INT (or even UNIQUEIDENTIFIER), it is of little practical value if one cannot easily get the same value back out. Yes, an extra CONVERT can be thrown in there, but how often do people know to do that? Really, it should just say that CONTEXT_INFO is BINARY(128) (not even VARBINARY(128) ) and have it work as most people are expecting it to. When I have time I will try to make that change to the documentation.
Take care,
Solomon..
Solomon,
I completely understand your point. Just because you can do something doesn't make it a good idea. But in the context of the question you can, as Steve pointed out, pass numeric types. Good idea? Probably not. Possible? Yes.
-Tom
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply