April 8, 2018 at 12:22 am
Comments posted to this topic are about the item What's going on?
April 8, 2018 at 12:22 am
Good question, thanks Steve.
...
April 9, 2018 at 1:11 am
My thought is that using substring to grab the relevant bytes is more appropriate than implicitly doing so via convert.
😎
Thanks for the question Steve.
Here is a spanner in the works
DECLARE @CI BIGINT = 1234567890
SET CONTEXT_INFO @CI
GO
April 9, 2018 at 2:11 am
Nice one to start the week on, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
April 9, 2018 at 8:01 am
Interesting is specified this question, a long time I decided to answer.
If I run the code given in the Explanation, the result is number 12 and it looks like the answer that is mentioned in the possible answers.The result of this code:DECLARE @BinRepre BINARY (128) = 12;
SET CONTEXT_INFO @BinRepre;
SELECT CONVERT (INT, CONTEXT_INFO ()) AS MyContextInfo;
GO
is also number 12, so this may be considered as a second different possible answer.
I was lucky, I made the right choice...😉
April 9, 2018 at 9:38 am
If that one is correct, then the first option is also correct 😉
The short lesson for all of these context_info questions is surely not to rely on any default behaviour but to explicitly set and read the values in binary format
April 9, 2018 at 12:01 pm
George Vobr - Monday, April 9, 2018 8:01 AMThe result of this code:DECLARE @BinRepre BINARY (128) = 12;
SET CONTEXT_INFO @BinRepre;
SELECT CONVERT (INT, CONTEXT_INFO ()) AS MyContextInfo;
GO
is also number 12, so this may be considered as a second different possible answer.
Hi George. Yes, what you are seeing in your example is correct.
I was going to recommend to Steve to update the explanation to be a little clearer as to the root cause of the behavior. However, as I was typing up what I had thought would be a brief explanation, I realized that it wasn't going to be quick and easy. The problem is really a combination of 3 different things, one of which is definitely the CONTEXT_INFO() built-in function and how it always returns 128 bytes, even though the returned datatype is still VARBINARY (and not BINARY).
So, I just wrote it up as a blog post, which also made it easier to format the code, etc:
Why Doesn’t CONTEXT_INFO() Return the Exact Value Set by SET CONTEXT_INFO?
I originally had mentioned at the top of that post that the idea came from this question, with a link back to the question, but given that it came out today I didn't want to appear like I was trying to give some folks the answer. Hopefully that was the appropriate way to handle this. Eventually I will update it to include the link back to this question.
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
April 9, 2018 at 5:20 pm
Solomon Rutzky - Monday, April 9, 2018 12:01 PMGeorge Vobr - Monday, April 9, 2018 8:01 AMThe result of this code:DECLARE @BinRepre BINARY (128) = 12;
SET CONTEXT_INFO @BinRepre;
SELECT CONVERT (INT, CONTEXT_INFO ()) AS MyContextInfo;
GO
is also number 12, so this may be considered as a second different possible answer.Hi George. Yes, what you are seeing in your example is correct.
I was going to recommend to Steve to update the explanation to be a little clearer as to the root cause of the behavior. However, as I was typing up what I had thought would be a brief explanation, I realized that it wasn't going to be quick and easy. The problem is really a combination of 3 different things, one of which is definitely the CONTEXT_INFO() built-in function and how it always returns 128 bytes, even though the returned datatype is still VARBINARY (and not BINARY).
So, I just wrote it up as a blog post, which also made it easier to format the code, etc:
Why Doesn’t CONTEXT_INFO() Return the Exact Value Set by SET CONTEXT_INFO?
I originally had mentioned at the top of that post that the idea came from this question, with a link back to the question, but given that it came out today I didn't want to appear like I was trying to give some folks the answer. Hopefully that was the appropriate way to handle this. Eventually I will update it to include the link back to this question.
Take care,
Solomon...
Thanks Solomon for a very good detailed explanation. Today, I also very much appreciated your notes and examples of data conversion in a QotD-discussion on a similar issue on February 13, 2018.
Thank you again.
G. V.
April 10, 2018 at 11:16 am
George Vobr - Monday, April 9, 2018 5:20 PMThanks Solomon for a very good detailed explanation. Today, I also very much appreciated your notes and examples of data conversion in a QotD-discussion on a similar issue on February 13, 2018.Thank you again.
G. V.
Hi George. You are quite welcome. And I had completely forgotten about that other QotD, thanks for mentioning it. I incorporated part of one of my comments from that other QotD into my blog post. Now the post includes two cases when this problem does not happen 🙂 . I also added some explanation towards the end to show step-by-step by the initial outcome is `0`.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply