Casting CONTEXT_INFO to varchar and the resulting length

Question

I'm trying to use CONTEXT_INFO to pass a usercode from a stored procedure into a DELETE trigger for table auditing purposes.

It all works fine, however I noticed that the length of the usercode saved in the audit table was not correct.

Take this script as an example...

declare @userCode varchar(50)
set @userCode = 'TestUser'

declare @binary_userCode varbinary(128)
set @binary_userCode = cast(@userCode as varbinary(128))
set CONTEXT_INFO @binary_userCode

declare @temp_userCode varchar(50)
set @temp_userCode = (select cast(CONTEXT_INFO() as varchar(50)))

--set @temp_userCode = rtrim(ltrim(@temp_userCode))

select @userCode, len(@userCode), @temp_userCode, len(@temp_userCode)

set CONTEXT_INFO 0x

Results:

len(@userCode) = 8

len(@temp_userCode) = 50

Why is the @temp_userCode variable coming back with a length of 50, and how can I trim it back to it's original length to store it correctly?

Further Information:

Running SQL Server 2005, however the solution needs to work in all versions 2005 onwards.

score 9 · Accepted Answer · edited Jul 24 '14 at 09:33

9

When assigned to CONTEXT_INFO it gets padded out with null bytes 0x00 to 128 bytes in length and becomes 0x5465737455736572000000...

You can use

REPLACE(CAST(CONTEXT_INFO() AS varchar(128)) COLLATE Latin1_General_100_BIN , 
        0x00, 
        '')

edited Jul 24 '14 at 09:33

Contango

76,540
58
260
305

answered Feb 24 '12 at 16:56

Martin Smith

438,706
87
741
845

1

This works however I had to change the collation to SQL_Latin1_General_CP437_BIN. This was based on trial and error however which doesn't leave me with much confidence. How can I clarify which collation I should be using? – Brett Postin Feb 24 '12 at 17:14
@Poz - You didn't state SQL Server version in your question. If previous to 2008 the `100` collations won't be available. – Martin Smith Feb 24 '12 at 17:15
Apologies. I'm running 2005, however it needs to be suitable for all versions above that too. – Brett Postin Feb 24 '12 at 17:18
@Poz - Well I'd just use whatever the `BIN` version is of your current default collation. BTW: What is your database's current default collation? Wondering if that is why Aaron's answer doesn't work for you? – Martin Smith Feb 24 '12 at 17:23
1

So `Latin1_General_BIN` would be the `BIN` version of that. – Martin Smith Feb 24 '12 at 17:36

Aaron Bertrand · Answer 2 · 2012-02-24T17:33:18.740

4

It gets padded with CHAR(0). Try:

set @temp_userCode = REPLACE(@temp_userCode COLLATE Latin1_General_BIN, CHAR(0), '');

(EDIT: added an explicit COLLATE clause, though now I feel like I'm stealing from Martin.)

edited Feb 24 '12 at 17:33

answered Feb 24 '12 at 17:02

Aaron Bertrand

272,866
37
466
490

+1 I use an explicit cast and collate clause in my answer [because of this issue](https://connect.microsoft.com/SQLServer/feedback/details/708179/indefinite-hang-with-replace-statement-on-varbinary-max) but it only appeared for `varbinary(max)` anyway. – Martin Smith Feb 24 '12 at 17:12
This solution doesn't work for me, the length is still 50. Due to SQL 2005 perhaps? – Brett Postin Feb 24 '12 at 17:21
Actually I found two - one running 9.00.3042, the other 9.00.4211, and it worked - both lengths show 8. – Aaron Bertrand Feb 24 '12 at 17:21
I'm running 9.00.5000.00 with collation Latin1_General_CI_AS. My script is the same as in my question with your line added directly under the "set @temp_userCode" statement. – Brett Postin Feb 24 '12 at 17:28
1

Try adding an explicit `COLLATE`. When I use `Latin1_General_CI_AS` explicitly it doesn't work (my test database is `SQL_Latin1_General_CP1_CI_AS`). – Aaron Bertrand Feb 24 '12 at 17:34

score 2 · Answer 3 · answered Sep 26 '14 at 11:03

2

Try this, it works for me on SQL Server 2005:

select cast(substring(CONTEXT_INFO(), 1, charindex(0x00, CONTEXT_INFO())-1) as varchar(128));

No messy collations to consider :-)

answered Sep 26 '14 at 11:03

eoxaal

21
1

score 0 · Answer 4 · edited Aug 06 '14 at 15:28

Replace will randomly fail on different installations of SQL server unless you specify the collation:

REPLACE(CAST(CONTEXT_INFO() AS varchar(128)) COLLATE Latin1_General_100_BIN , 0x00, '')

SQL server has two different behaviors, depending on how it is installed:

Replacement is successful when SQL collation is used.
Replacement is unsuccessful when Windows collation is used.

This behaviour was submitted to Microsoft nearly over 7 years ago:

Q: When trying a replace a NUL character with replace(), this works is the value has an SQL collation, but not a Windows collation.

A: This is due to the fact that 0x0000 is an undefined character in Windows collations. All undefined characters are ignored during comparison, sort, and pattern matching. So searching for 'a' + char(0) is really searching for ‘a’, and searching for char(0) is equivalent to empty string.

The way to handle undefined character is a bit confusing, but this is the way that Windows defined to sort them, and SQL Server conforms with the general Windows API.

In SQL collation, there is no notion of undefined character. Each code point is assigned a weight, that's why we don't see a problem there.

Casting CONTEXT_INFO to varchar and the resulting length

4 Answers4