SQL Server, CONTEXT_INFO(), and varchar size

Question

In table users I have a column username of datatype varchar(50). The table has no records. I insert a new record with A for the username. The following returns what I would expect:

SELECT username, LEN(username) 
FROM users 
WHERE id = 1 -- returns: A, 1

So far so good.

Now I update table users from a trigger on another table, using the value from CONTEXT_INFO():

set @context = cast('B' as varbinary(128))
set CONTEXT_INFO @context

update some_other_table 
set x = 'y' 
where id = 97

In the trigger for some_other_table I do:

DECLARE @context VARCHAR(128)

SELECT 
    @context = CAST(CONTEXT_INFO() AS VARCHAR(128)) 
FROM 
    master.dbo.SYSPROCESSES 
WHERE 
    SPID = @@SPID

DECLARE @user VARCHAR(50) = LEFT(@context, 50)

UPDATE users 
SET username = LTRIM(RTRIM(@user)) 
WHERE id = 1

The username is correctly set to "B", but the following now returns 50:

SELECT 
    username, LEN(username) 
FROM 
    users 
WHERE 
    id = 1 -- returns: B, 50

The solution, when populating the context, is to do:

set @context = cast('B' + replicate(' ', 126) as varbinary(128))

But why do I need to do this?

When I don't pad the CONTEXT_INFO with spaces what is happening that updating using its value will cause the resulting length to be 50 (even if I ltrim and rtrim the single character value before updating)?

And why must I pad my CONTEXT_INFO to 127 bytes total, not 128? For every character over 127, 1 character is truncated from the value originally set on CONTEXT_INFO

Note: ANSI_PADDING is enabled

Tim Biegeleisen · Answer 1 · 2016-01-22T01:59:27.070

1

In your code:

declare @user varchar(50) = left(@context, 50)
UPDATE users set username = ltrim(rtrim(@user)) WHERE id = 1

you defined @user to be 50 characters. Since the CONTEXT_INFO itself is 128 bytes, the contents of @user will be the letter B padded by 49 null CHAR(0) characters. LTRIM() and RTRIM() will not remove null characters, which are not whitespace, so they have no effect on @user.

If you want to remove the NULL character you can try this (assuming you are using SQL Server 2005 or later):

UPDATE users SET username = REPLACE(@user, char(0), '')

edited Jan 22 '16 at 01:59

answered Jan 22 '16 at 01:36

Tim Biegeleisen

502,043
27
286
360

Short of casting the value to a type that doesn't allow the NULL byte, is there an easy way to remove all trailing NULL bytes? – Developer Webs Jan 22 '16 at 01:45
If you select 50 bytes from a 128 byte null-padded `CONTEXT_INFO` then what would you like to have as padding instead of `NULL`? If you don't want padding then just select the exact length. – Tim Biegeleisen Jan 22 '16 at 01:50
But how can I know how many non-NULL characters were passed into the CONTEXT_INFO? In my example the passed value is hard coded, but in live code the CONTEXT_INFO is populated using dynamic data. – Developer Webs Jan 22 '16 at 01:53
Because my trigger can't know if there is 1 or 127 bytes in CONTEXT_INFO (in my example above its hard coded, but in production it will be dynamic) I ended up doing the following: set `@start = charindex(char(0), `@context) if `@start > 0 set `@context = left(`@context, `@start) This is likely better than calculating how many space characters to append to context_info in every place that might set a value on the context_info. – Developer Webs Jan 22 '16 at 02:36

score 0 · Answer 2 · answered Feb 15 '21 at 09:38

I know this question is old, but there are two ways of retreiving CONTEXT_INFO: either directly with CONTEXT_INFO(), or from sys.dm_exec_sessions. The latter is not padded with '0' characters.

declare @c varbinary(128)
select  @c = convert(varbinary(128), 'Test context')
set CONTEXT_INFO @c
go

-- option 1: padded with '0'
declare @value varchar(128)
select  @value = convert(varchar(128), CONTEXT_INFO())
select  @value context, len(@value) length
go

-- option 2: NOT padded with '0'
declare @value varchar(128)
select  @value = convert(varchar(128), context_info)
from    sys.dm_exec_sessions
where   session_id = @@SPID
select  @value context, len(@value) length
go

Query result.

context         length
--------------- -----------
Test context    128

(1 row affected)

context         length
--------------- -----------
Test context    12

(1 row affected)

Alternatively, as of SQL Server 2016 you can also use SESSION_CONTEXT(), which allows you to specify key-value pairs, instead of a single binary blob.

SQL Server, CONTEXT_INFO(), and varchar size

2 Answers2