Problem extracting value from CONTEXT_INFO() to NVARCHAR field

• chris.king

  SSCrazy

  Points: 2007

  More actions

  June 8, 2011 at 8:59 am

  #252456

  Hi All,

  I am using the session variable context_info to log changes made to data in an audit database.

  I add a users name to the context_info variable and then extract the value via a trigger to log the users name against the audit record.

  I set the context info as follows, please note @LoginName is a parameter derived from a VARCHAR(128) field in the db:

  DECLARE @SYSUSER VARBINARY(128)

  SET @SYSUSER = CAST(CAST(@LoginName AS VARCHAR(128)) AS VARBINARY(128))

  SET CONTEXT_INFO @SYSUSER

  and extract it in my triggers as follows:

  CAST(CONTEXT_INFO() AS VARCHAR(128))

  This worked fine.

  However, I now need to support user names in languages other than English including Arabic so I changed the audit table fieldfrom VARCHAR(128) to NVARCHAR(128).

  So I'm now setting context_info as follows, please note that the field from which @LoginName is derived is now also NVARCHAR(128):

  DECLARE @SYSUSER VARBINARY(128)

  SET @SYSUSER = CAST(CAST(@LoginName AS VARCHAR(128)) AS VARBINARY(128))

  SET CONTEXT_INFO @SYSUSER

  and I extract it as follows:

  CAST(CONTEXT_INFO() AS NVARCHAR(128))

  I am now seeing what looks like chinese characters appearing in my audit tables. Here is a sample:

  呎䄠呕佈䥒奔乜呅佗䭒匠剅䥖䕃

  呎䄠呕佈䥒奔乜呅佗䭒匠剅䥖䕃

  桃楲䭳湩g

  Even stranger, this behaviour seems different from server to server, sometimes I get the correct username and sometimes the chinese characters??

  Any advice on why this worked for VARCHAR but not NVARCHAR and why the behaviour is not consistent across servers would be greatly appreciated.

  TIA,

  Chris

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 8, 2011 at 9:17 am

  #1335728

  as far as I know, the data type and sizes should always be varvbinary 128 and nvarchar(64), right?

  wouldn't an implcit comversion form nvarchar to varchar mess up the data?

  or is the issue the colaltion from the end user, who might use something arabic, looks like chinse characters int eh servers default collation?

  /*

  (No column name)(No column name)

  ??????????????NT AUTHORITY\NETWORK SERVICE

  */

  DECLARE @var VARBINARY(128)

  SET @var = CAST(N'??????????????' AS VARBINARY(128))

  SET CONTEXT_INFO @var

  GO

  -- Select the context information

  DECLARE @sess VARBINARY(128), @var NVARCHAR(64)

  SET @sess = (SELECT context_info FROM master.dbo.sysprocesses

  WHERE spid = @@spid)

  SET @var = CAST(@sess AS NVARCHAR(64))

  --print @var

  SELECT CAST(context_info AS NVARCHAR(64)),

  CAST(context_info AS VARCHAR(64))

  FROM master.dbo.sysprocesses

  WHERE spid = @@spid

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• chris.king

  SSCrazy

  Points: 2007

  More actions

  June 8, 2011 at 9:37 am

  #1335748

  Hi Lowell,

  Thanks for the reply.

  As far as I am aware, no implicit conversation from varchar to nvarchar is taking place. The source value (@LoginName) is nvarchar(128).

  I am converting this to varbinary(128) to insert it into context_info.

  I am then extracting it back out as nvarchar(128) for insertion in the the audit db field, which is also nvarchar(128).

  This is when I see the chinese data.

  What I don't understand is that when I run the following in isolation (i.e. not inserting to the audit db table), it works and I get back the expected user name:

  DECLARE @LoginName NVARCHAR(128)

  SET @LoginName = (SELECT LoginName FROM dbo.Login WHERE LoginID = 29)

  -- Set context info for audit db triggers

  DECLARE @SYSUSER VARBINARY(128)

  SET @SYSUSER = CAST(@LoginName AS VARBINARY(128))

  SET CONTEXT_INFO @SYSUSER

  GO

  SELECT CAST(CONTEXT_INFO() AS NVARCHAR(128))

  I can't figure out where the corruption of the data is taking place.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 8, 2011 at 9:49 am

  #1335764

  ok, i can duplicate some of what you are seeing...

  try this, which is barely modified form what you posted....

  i get chinese characters from my loginnmae due to the conversion:

  what i suspect is that somewhere, ther eis a varchar column that should be nvarchar...not sure if it's occuring when assigning a value to the context info or where, but this duplicates at least a slice of the wierdness you are seeing:

  /*

  ????????e

  */

  DECLARE @LoginName VARCHAR(128)

  SET @LoginName = convert(nVARCHAR(128),suser_name())

  -- Set context info for audit db triggers

  DECLARE @SYSUSER VARBINARY(128)

  SET @SYSUSER = CAST(@LoginName AS VARBINARY(128))

  SET CONTEXT_INFO @SYSUSER

  GO

  SELECT CAST(CONTEXT_INFO() AS NVARCHAR(128))

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  June 8, 2011 at 9:49 am

  #1335767

  chris.king (6/8/2011)

  ---

  So I'm now setting context_info as follows, please note that the field from which @LoginName is derived is now also NVARCHAR(128):

  DECLARE @SYSUSER VARBINARY(128)

  SET @SYSUSER = CAST(CAST(@LoginName AS VARCHAR(128)) AS VARBINARY(128))

  SET CONTEXT_INFO @SYSUSER

  You have shown two different pieces of code, the first one (quoted above) is converting the login name to VARCHAR(128) before VARBINARY... was this the problem?

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]