WHY THIS IS not working? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

WHY THIS IS not working?

Author

Topic

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-20 : 12:59:02

declare @handle int

BEGIN
SET NOCOUNT ON;

--Set the context info to RecordSharing
IF CAST(CONTEXT_INFO() AS VARCHAR(100)) != 'RecordSharing'
OR
CAST(CONTEXT_INFO() AS VARCHAR(100)) IS NULL
BEGIN
RAISERROR('Procedure can only run under RecordSharing context',16,1);
RETURN;
END

--Needs to run under valid transaction
IF XACT_STATE() != 1
RAISERROR('Procedure can only run under valid transaction',16,1);
RETURN;

IF EXISTS ( SELECT 1 FROM OPENXML(@handle,'/ROOT/TeamMember.ProviderTeam',1))
UPDATE [TeamMember].[ProviderTeam]

SET [ProviderTeamID] = [ixml].[ProviderTeamID]
,[DoctorID]= [ixml].[DoctorID]
,[NurseID] =[ixml].[NurseID]
,[ReceptionistID] =[ixml].[ReceptionistID]
,[CreateDate] = [ixml].[CreateDate]
,[CreateWorkstationID] = [ixml].[CreateWorkstationID]
,[ChangeDate] = [ixml].[ChangeDate]
,[ChangeWorkstationID] = [ixml].[ChangeWorkstationID]

FROM OPENXML(@handle,'/ROOT/TeamMember.ProviderTeam',1)
WITH (
[ProviderTeamID] uniqueidentifier
,[DoctorID] uniqueidentifier
,[NurseID] uniqueidentifier
,[ReceptionistID] uniqueidentifier
,[CreateDate] datetime
,[CreateWorkstationID] varchar(15)
,[ChangeDate] datetime
,[ChangeWorkstationID] varchar(15)
) AS ixml
WHERE NOT EXISTS (SELECT * FROM [TeamMember].[ProviderTeam]
WHERE [ixml].[ProviderTeamID] = [ProviderTeam].[ProviderTeamID]
);
SET NOCOUNT OFF

END

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-20 : 13:00:02

getting some weird error
Msg 50000, Level 16, State 1, Line 14
Procedure can only run under RecordSharing context

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 13:08:42

thats the custom error message given by you. check value of this

CAST(CONTEXT_INFO() AS VARCHAR(100))

and see if its NULL or anything other than 'RecordSharing' so that before IF becomes true

IF CAST(CONTEXT_INFO() AS VARCHAR(100)) != 'RecordSharing'
OR
CAST(CONTEXT_INFO() AS VARCHAR(100)) IS NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-20 : 13:42:41

Funketekun/gonxia649/escalaroyal/bluepaperbag, where do CONTEXT_INFO get populated with a scalar value?

E 12°55'05.25"
N 56°04'39.16"

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-20 : 13:58:30

PERSO, I don know i didn't write the code. I'm trying to read and undestand it. Thats my first priorty

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-20 : 14:14:05

PERSO - the sixth Marx brother?

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-20 : 14:22:05

quote:
Originally posted by lamujerdetuhermano10
...I'm trying to read and undestand it...

Some things are just not meant to be.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-20 : 16:40:09

It took me about 8 seconds to Google CONTEXT_INFO() and learn about the binary value.
http://msdn.microsoft.com/en-us/library/ms180125(SQL.90).aspx

With binary value you have to beware of collation settings, upper and lower case, unicode and so on....

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-20 : 16:56:59

quote:
Originally posted by TG

PERSO - the sixth Marx brother?

Better than PERVO, the unknown 8th dwarf...

E 12°55'05.25"
N 56°04'39.16"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-20 : 17:07:29

Just as a bit of side info re context_info in case anyone is interested. I actually have some production code (just an internal product) that uses context_info.

We have a table which doesn't contain any columns for createdBy or updatedBy but that table has an associated audit table which is populated by a trigger AFTER IUD. The Insert/Update SP for the main table takes a non-nullable @userid int parameter (passed by the calling application). The SP converts it to binary(128) and SETs CONTEXT_INFO to that value. The trigger retrieves the binary value from sysprocesses where spid=@@spid, converts it back to int and applies it to the audit table as the createdBy, updatedBy values. I know I could have just added those columns to the main table but I had been looking for an opportunity to use context_info

This occasion is the only time I've ever implented anything using context_info but it has been working fine for over a year now.

EDIT:
>>Better than PERVO, the unknown 8th dwarf...

sounds like an uncle of mine :) (my family is pretty short with perversion tendencies)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources