Careful with Session_Context()–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

A quick note, which is more of a reminder to myself. I find writing things down helps me remember, and I need to remember this.

I was working with session context, specifically the SESSION_CONTEXT() function. When using this function, you give it a key that contains a value, like this:

2019-01-15 13_45_34-RLS_Testing.sql - Plato_SQL2016.WideWorldImporters (PLATO_Steve (61))_ - Microso

I get a value back that I can use. Everything is great.  I was using this to allow one process to set a value and another to get it, and I was happy.

Until things stopped working. While trying to debug this, I ran this code:

2019-01-15 13_46_46-RLS_Testing.sql - Plato_SQL2016.WideWorldImporters (PLATO_Steve (61))_ - Microso

Notice a difference? In the first query, I have SupplierID, but the second is SupplierId, with a lower case “d”. These keys are determined when you use sp_set_session_context, which takes a sysname value for the key. These are going to be case sensitive, as each one is a different identifier.

It’s not likely that this will cause lots of problems, but when you are setting keys, be careful and ensure you use the same value for writing and reading.

SQLNewBlogger

This was a quick mistake I made and it took me 5 minutes to write up. It’s helpful to get me to remember to avoid this, but this also shows I can fix my mistakes.

What’s a simple thing you learned that makes you write better code? Write your own SQLNewBlogger post today.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

8 Responses to Careful with Session_Context()–#SQLNewBlogger

  1. Pingback: SESSION_CONTEXT Is Case-Sensitive – Curated SQL

  2. David Dubois says:

    There’s more to this than meets the eye!
    EXEC sp_set_session_context ‘user_id’, 4;

    SELECT SESSION_CONTEXT(N’user_id’),
    SESSION_CONTEXT(N’USER_ID’),
    SESSION_CONTEXT(N’User_ID’),
    SESSION_CONTEXT(N’User_id’);
    Result is:
    4 NULL NULL 4

    Why do ‘user_id’ and ‘User_id’ return the same value, but ‘USER_ID’ and ‘User_ID’ do not?

  3. way0utwest says:

    Not sure. There is some real strangeness here, and I’m trying to determine the extent of this. If you use “us” and then try combinations, you get strange results. MS must be doing something weird here.

  4. neal8900 says:

    Hypothesis: Only the last letter is case-sensitive. I just ran a test where I assigned a value for every possible combination of case of ‘user_id’. Then I read the results back. All the ones that end in lowercase D came back with the same value. All the values with uppercase D came back with the same value. As a further test I randomized the order and ran several trials.
    I hesitate to post this conclusion because it makes no sense. There must be something wrong with my test.

  5. neal8900 says:

    Further tests show that hypothesis is wrong. But I still can’t figure out what the rule is.

  6. way0utwest says:

    Agree. This is part of my testing:
    EXEC sp_set_session_context N’us’, 4;

    SELECT
    SESSION_CONTEXT(N’Us’),
    SESSION_CONTEXT(N’Us’),
    SESSION_CONTEXT(N’uS’),
    SESSION_CONTEXT(N’US’)

    SQL 2016, CI_AS : 4,4,NULL, NULL
    SQL 2016, CS_AS : 4,4,NULL, NULL
    SQL 2017, CI_AS : 4,4,NULL, NULL
    SQL 2017, CS_AS : 4,4,NULL, NULL
    SQL 2019, CI_AS : 4, 4, NULL, NULL
    SQL 2019, CS_AS : NULL,NULL,NULL, NULL

  7. neal8900 says:

    Another test. I generate a random string of letters. I randomize the case of the letters. I create a session context. I randomize the case again, and create another session context. I load them back to see if they are the same or different. The result is that they are the same about 25% of the time. I’ve run many thousands of trials.

  8. neal8900 says:

    My tests are using SQL 2017 express. Perhaps there was a bug in earlier versions that was fixed in 2019.

Comments are closed.