Context_info() data

Question

Context_info() data

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 1

Steve Jones - SSC Editor - Tuesday, February 13, 2018 9:18 AM
TUellner - Tuesday, February 13, 2018 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.
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.

Steve, the fact that you can pass in an INT and not have it error doesn't mean that it is working as expected and that people can / should do this in their code. You can also pass in characters into a VARCHAR column that don't exist in the code page of the column's Collation and it also won't error, but that doesn't mean that it is working as expected.

Also, how is this version specific? I have tested on SQL Server 2012, 2014, 2016, and 2017 and all behave exactly the same.

And, you can pass in UNIQUEIDENTIFIER:

DECLARE @GUID UNIQUEIDENTIFIER = NEWID(); SET CONTEXT_INFO @GUID;

SELECT CONVERT(UNIQUEIDENTIFIER, CONTEXT_INFO()) AS [BackToUniqueIdentifier];

That actually works as expected, without needing a second conversion. Interesting 🙂
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

drew.allen SSC Guru Points: 76944 More actions · Answer 2

Steve Jones - SSC Editor - Tuesday, February 13, 2018 9:18 AM
TUellner - Tuesday, February 13, 2018 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.
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.

But the explanation says "SET CONTEXT_INFO needs varbinary(128) data passed in." In other words, you can only pass in binary data (a). Other data types may be converted BEFORE it is passed in.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 3

drew.allen - Tuesday, February 13, 2018 9:39 AM
But the explanation says "SET CONTEXT_INFO needs varbinary(128) data passed in." In other words, you can only pass in binary data (a). Other data types may be converted BEFORE it is passed in.
Drew

Ah, but I can pass it in. the question isn't what does SQL Server require, but what can I send as a parameter.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Steve Jones - SSC Editor - Tuesday, February 13, 2018 10:20 AM
drew.allen - Tuesday, February 13, 2018 9:39 AM
But the explanation says "SET CONTEXT_INFO needs varbinary(128) data passed in." In other words, you can only pass in binary data (a). Other data types may be converted BEFORE it is passed in.
Drew
Ah, but I can pass it in. the question isn't what does SQL Server require, but what can I send as a parameter.

Been on the other side of this argument before, it is semantics. I really don't care about my points, but context_info is storing/using binary data, not numeric data.

drew.allen SSC Guru Points: 76944 More actions · Answer 5

Steve Jones - SSC Editor - Tuesday, February 13, 2018 10:20 AM
drew.allen - Tuesday, February 13, 2018 9:39 AM
But the explanation says "SET CONTEXT_INFO needs varbinary(128) data passed in." In other words, you can only pass in binary data (a). Other data types may be converted BEFORE it is passed in.
Drew
Ah, but I can pass it in. the question isn't what does SQL Server require, but what can I send as a parameter.

It depends on the definition of "pass in." With 59% of respondents saying "varbinary only" it seems that most people disagree with your interpretation.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 6

OK, it's too semantic. Reworded the question and awarded points back.