将CONTEXT_INFO转换为varchar和生成的长度。

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

我试图使用CONTEXT_INFO将存储过程中的用户代码传递到删除触发器中，以便进行表审计。

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(@userCode)= 8

len(@temp_userCode) = 50

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?

为什么@temp_userCode变量返回的长度为50，如何将其调整回原来的长度以正确存储它?

Further Information:

进一步的信息:

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

运行SQL Server 2005，但是解决方案需要在2005年以后的所有版本中工作。

4 个解决方案

#1

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

当分配给CONTEXT_INFO时，它会以空字节0x00填充到128字节的长度，变成0x5465737455736572000000…

You can use

您可以使用

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

#2

It gets padded with CHAR(0). Try:

它用CHAR(0)填充。试一试:

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.)

(编辑:添加了一个明确的核对条款，虽然现在我觉得我在偷马丁的东西。)

#3

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

试试这个，它适用于我的SQL Server 2005:

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

No messy collations to consider :-)

没有杂乱的排序考虑:)

#4

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

除非您指定了collation:否则，Replace将在不同的SQL server安装上随机失败。

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:

SQL server有两个不同的行为，具体取决于它的安装方式:

Replacement is successful when SQL collation is used.
当使用SQL排序时，替换是成功的。
Replacement is unsuccessful when Windows collation is used.
当使用Windows排序规则时，替换失败。

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

这种行为在近7年前被提交给微软:

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

问:当尝试用replace()替换NUL字符时，这是可行的，因为该值具有SQL排序规则，而不是Windows排序规则。

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.

答:这是因为0x0000是Windows排序中的一个未定义字符。在比较、排序和模式匹配过程中，所有未定义字符都被忽略。所以搜索'a' + char(0)实际上是在搜索'a'，而搜索char(0)等同于空字符串。

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.

处理未定义字符的方式有点混乱，但这是Windows定义的对它们进行排序的方式，SQL Server符合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.

在SQL排序中，没有未定义字符的概念。每个代码点都被分配了一个权重，这就是为什么我们没有看到问题的原因。

#1