problem with context info

microsoft.public.sqlserver.programming

1 Jul 2005 3:05 PM

Ram

Hi
I have a problem with reading value from context info.

I am setting the context info using the following code.
CREATE PROCEDURE dbo.SPOC_SETCONTEXTINFO
( @INFO nvarchar(20) )
AS
SET NOCOUNT ON
DECLARE @BinVar VARbinary(20)
SET @BinVar = CAST(RTRIM(@INFO) AS VARBINARY(20))
SET CONTEXT_INFO @BinVar
RETURN
GO

CREATE TABLE [dbo].[OC_AUDITTRAIL_ITEMS] (
[ID] [uniqueidentifier] NOT NULL ,
[USERID] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[EVENTDATE] [datetime] NULL ,
[ITEMTYPE] [int] NULL ,
[EVENT] [int] NULL ,
[CATEGORY] [int] NULL ,
[TEXTID] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

And using the following code I am inserting the data into
OC_AUDITTRAIL_ITEMS table.
exec SPOC_SETCONTEXTINFO 'ANADMIN'
DECLARE @USERID NVARCHAR(20)
SELECT @USERID = CONVERT(NVARCHAR(20),CONTEXT_INFO) FROM
MASTER.dbo.SYSPROCESSES WHERE SPID = @@SPID
INSERT INTO OC_AUDITTRAIL_ITEMS (USERID, EVENTDATE, TEXTID) VALUES(@USERID,
GETDATE(), 'PEOPLE')

I was able to insert data into OC_AUDITTRAIL_ITEMS table but the userid has
some junk characters at the end. This reveals only if we click that value
in enterprise manager otherwise it looks normal. In our .NET code we are
reading value in this column and we are getting error.
Any way of solving this issue?

Thanks

Ram

[attached file: Problem.JPG]

1 Jul 2005 3:15 PM

David Gugick

Ram wrote:

> Hi
> I have a problem with reading value from context info.
>

Seems to work for me on SQL Server 2000 SP3a. What SP are you running.
Here's the code I used to test (gathered from your post). I changes the
varbinary to 128 from 20, but it worked in both cases.

Declare @INFO nvarchar(20)
DECLARE @BinVar VARbinary(128)
SET @INFO = N'David'
SET @BinVar = CAST(RTRIM(@INFO) AS VARBINARY(20))
select @BinVar

SET CONTEXT_INFO @BinVar

DECLARE @USERID NVARCHAR(20)
SELECT @USERID = CONVERT(NVARCHAR(20),CONTEXT_INFO)
FROM MASTER.dbo.SYSPROCESSES
WHERE SPID = @@SPID

Select @USERID

------------------------------------------
0x44006100760069006400

--------------------
David

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

4 Jul 2005 6:07 AM

Ram

Hi David
I am using SP3. Include len(@userid) in your select statement and it gives
20 but supposed to give only 5 because len('david') is 5.

Show quote

"David Gugick" <david.gugick-nospam@quest.com> wrote in message
news:Oh0ey%23kfFHA.2268@TK2MSFTNGP15.phx.gbl...
> Ram wrote:
>> Hi
>> I have a problem with reading value from context info.
>>
>
> Seems to work for me on SQL Server 2000 SP3a. What SP are you running.
> Here's the code I used to test (gathered from your post). I changes the
> varbinary to 128 from 20, but it worked in both cases.
>
> Declare @INFO nvarchar(20)
> DECLARE @BinVar VARbinary(128)
> SET @INFO = N'David'
> SET @BinVar = CAST(RTRIM(@INFO) AS VARBINARY(20))
> select @BinVar
>
> SET CONTEXT_INFO @BinVar
>
> DECLARE @USERID NVARCHAR(20)
> SELECT @USERID = CONVERT(NVARCHAR(20),CONTEXT_INFO)
> FROM MASTER.dbo.SYSPROCESSES
> WHERE SPID = @@SPID
>
> Select @USERID
>
>
> ------------------------------------------
> 0x44006100760069006400
>
>
> --------------------
> David
>
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

1 Jul 2005 3:20 PM

Jacco Schalkwijk

I don't see the purpose of this code. You have a string, you convert it into
binary, put it into contextinfo, than convert it back from binary to a
string. Why not use the string directly?

In any case, it is a lot easier just to use a permanent table like:

CREATE TABLE spid_users ([spid] INT NOT NULL, [userid] NVARCHAR(20) NOT
NULL,
CONSTRAINT PK_spid_users PRIMARY KEY([spid])

and keep track of which user is using which spid there.

--
Jacco Schalkwijk
SQL Server MVP

Show quote

"Ram" <Ramakrishna.pothuga***@vizual.co.in> wrote in message
news:uns3X1kfFHA.1416@TK2MSFTNGP09.phx.gbl...
> Hi
> I have a problem with reading value from context info.
>
> I am setting the context info using the following code.
> CREATE PROCEDURE dbo.SPOC_SETCONTEXTINFO
> ( @INFO nvarchar(20) )
> AS
> SET NOCOUNT ON
> DECLARE @BinVar VARbinary(20)
> SET @BinVar = CAST(RTRIM(@INFO) AS VARBINARY(20))
> SET CONTEXT_INFO @BinVar
> RETURN
> GO
>
> CREATE TABLE [dbo].[OC_AUDITTRAIL_ITEMS] (
> [ID] [uniqueidentifier] NOT NULL ,
> [USERID] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
> [EVENTDATE] [datetime] NULL ,
> [ITEMTYPE] [int] NULL ,
> [EVENT] [int] NULL ,
> [CATEGORY] [int] NULL ,
> [TEXTID] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
>
> And using the following code I am inserting the data into
> OC_AUDITTRAIL_ITEMS table.
> exec SPOC_SETCONTEXTINFO 'ANADMIN'
> DECLARE @USERID NVARCHAR(20)
> SELECT @USERID = CONVERT(NVARCHAR(20),CONTEXT_INFO) FROM
> MASTER.dbo.SYSPROCESSES WHERE SPID = @@SPID
> INSERT INTO OC_AUDITTRAIL_ITEMS (USERID, EVENTDATE, TEXTID)
> VALUES(@USERID, GETDATE(), 'PEOPLE')
>
> I was able to insert data into OC_AUDITTRAIL_ITEMS table but the userid
> has some junk characters at the end. This reveals only if we click that
> value in enterprise manager otherwise it looks normal. In our .NET code
> we are reading value in this column and we are getting error.
> Any way of solving this issue?
>
>
> Thanks
>
> Ram
>
>
>

4 Jul 2005 1:56 AM

Brian Selzer

NCHAR and NVARCHAR take two bytes per character, so the correct number of
bytes in context_info is 40, not 20.

Use NCHAR(20) instead of NVARCHAR(20) for the parameter @INFO so that blanks
will fill out the field in context_info. (By the way, for those of you who
are going to harrass me for using the term field, I believe this is a
correct instance, because it is the first 40 bytes of a 128 byte column.)

Use SUBSTR to extract 40 bytes from sysprocesses.context_info then convert
that to NCHAR(20) to put into @USERID
Finally use RTRIM(@USERID) in the VALUES clause of the INSERT.

I think BOL is wrong, sysprocesses.context_info is not varbinary(128), it is
a binary(128).

Show quote

"Ram" <Ramakrishna.pothuga***@vizual.co.in> wrote in message
news:uns3X1kfFHA.1416@TK2MSFTNGP09.phx.gbl...
> Hi
> I have a problem with reading value from context info.
>
> I am setting the context info using the following code.
> CREATE PROCEDURE dbo.SPOC_SETCONTEXTINFO
> ( @INFO nvarchar(20) )
> AS
> SET NOCOUNT ON
> DECLARE @BinVar VARbinary(20)
> SET @BinVar = CAST(RTRIM(@INFO) AS VARBINARY(20))
> SET CONTEXT_INFO @BinVar
> RETURN
> GO
>
> CREATE TABLE [dbo].[OC_AUDITTRAIL_ITEMS] (
> [ID] [uniqueidentifier] NOT NULL ,
> [USERID] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
> [EVENTDATE] [datetime] NULL ,
> [ITEMTYPE] [int] NULL ,
> [EVENT] [int] NULL ,
> [CATEGORY] [int] NULL ,
> [TEXTID] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
>
> And using the following code I am inserting the data into
> OC_AUDITTRAIL_ITEMS table.
> exec SPOC_SETCONTEXTINFO 'ANADMIN'
> DECLARE @USERID NVARCHAR(20)
> SELECT @USERID = CONVERT(NVARCHAR(20),CONTEXT_INFO) FROM
> MASTER.dbo.SYSPROCESSES WHERE SPID = @@SPID
> INSERT INTO OC_AUDITTRAIL_ITEMS (USERID, EVENTDATE, TEXTID)
VALUES(@USERID,
> GETDATE(), 'PEOPLE')
>
> I was able to insert data into OC_AUDITTRAIL_ITEMS table but the userid
has
> some junk characters at the end. This reveals only if we click that value
> in enterprise manager otherwise it looks normal. In our .NET code we are
> reading value in this column and we are getting error.
> Any way of solving this issue?
>
>
> Thanks
>
> Ram
>
>
>

4 Jul 2005 7:21 AM

Ram

Hi Friends
Thanks for your help. None of the solution give here did't worked. By
using string manipulation I removed the trail junk characters. All the
trail junk characters ascii value is 0 and I removed all those characters
from output varialble. This solved my issue. The main problem is,
context_info is binary datatype.
Once again thanks for your help.
Regards
Ram

Show quote

"Brian Selzer" <br***@selzer-software.com> wrote in message
news:eO4dhuDgFHA.3232@TK2MSFTNGP15.phx.gbl...
> NCHAR and NVARCHAR take two bytes per character, so the correct number of
> bytes in context_info is 40, not 20.
>
> Use NCHAR(20) instead of NVARCHAR(20) for the parameter @INFO so that
> blanks
> will fill out the field in context_info. (By the way, for those of you
> who
> are going to harrass me for using the term field, I believe this is a
> correct instance, because it is the first 40 bytes of a 128 byte column.)
>
> Use SUBSTR to extract 40 bytes from sysprocesses.context_info then convert
> that to NCHAR(20) to put into @USERID
> Finally use RTRIM(@USERID) in the VALUES clause of the INSERT.
>
> I think BOL is wrong, sysprocesses.context_info is not varbinary(128), it
> is
> a binary(128).
>
> "Ram" <Ramakrishna.pothuga***@vizual.co.in> wrote in message
> news:uns3X1kfFHA.1416@TK2MSFTNGP09.phx.gbl...
>> Hi
>> I have a problem with reading value from context info.
>>
>> I am setting the context info using the following code.
>> CREATE PROCEDURE dbo.SPOC_SETCONTEXTINFO
>> ( @INFO nvarchar(20) )
>> AS
>> SET NOCOUNT ON
>> DECLARE @BinVar VARbinary(20)
>> SET @BinVar = CAST(RTRIM(@INFO) AS VARBINARY(20))
>> SET CONTEXT_INFO @BinVar
>> RETURN
>> GO
>>
>> CREATE TABLE [dbo].[OC_AUDITTRAIL_ITEMS] (
>> [ID] [uniqueidentifier] NOT NULL ,
>> [USERID] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
>> [EVENTDATE] [datetime] NULL ,
>> [ITEMTYPE] [int] NULL ,
>> [EVENT] [int] NULL ,
>> [CATEGORY] [int] NULL ,
>> [TEXTID] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
>> ) ON [PRIMARY]
>> GO
>>
>>
>> And using the following code I am inserting the data into
>> OC_AUDITTRAIL_ITEMS table.
>> exec SPOC_SETCONTEXTINFO 'ANADMIN'
>> DECLARE @USERID NVARCHAR(20)
>> SELECT @USERID = CONVERT(NVARCHAR(20),CONTEXT_INFO) FROM
>> MASTER.dbo.SYSPROCESSES WHERE SPID = @@SPID
>> INSERT INTO OC_AUDITTRAIL_ITEMS (USERID, EVENTDATE, TEXTID)
> VALUES(@USERID,
>> GETDATE(), 'PEOPLE')
>>
>> I was able to insert data into OC_AUDITTRAIL_ITEMS table but the userid
> has
>> some junk characters at the end. This reveals only if we click that
>> value
>> in enterprise manager otherwise it looks normal. In our .NET code we are
>> reading value in this column and we are getting error.
>> Any way of solving this issue?
>>
>>
>> Thanks
>>
>> Ram
>>
>>
>>
>
>