set_session_context error

Question

I am getting the error 'Cannot set key 'USERID' in the session context. The key has been set as read_only for this session.' when I run my stored procedure multiple times by looping through an asp.net GridView. I was wondering if there was a way around this error. For example is there a way to remove the set_session_context setting or something?

Below is my sp code that is failing.

ALTER PROCEDURE [dbo].[kd_updtblSupplementsPerPatient]
    (@PatientID     int,
     @SupplementID  int,
     @SuppleValue   real = 0,
     @UserLoginID   int
    )

AS 

    BEGIN

        --Put login userid into session context for use in trigger(s)
        EXEC sp_set_session_context 'USERID', @UserLoginID, @read_only = 1;

        UPDATE dbo.tblSupplementsPerPatient 
           SET [SuppleValue] = @SuppleValue
        WHERE ([PatientID] = @PatientID)
          AND ([SupplementID] = @SupplementID);

    END

Answer 1

Since the parameter @Rudy Aira _only is set to 1, when you execute sp_set_session_context first time it works. But when you change the value and execute the sp, you will get that error.

-- It works  
EXEC sp_set_session_context 'MyName', 'Test1', @read_only = 1;  
SELECT SESSION_CONTEXT(N'MyName') MyName;  

But when you change the value to 'Test2' and execute it again in the same session, it will fail:

-- It fails  
EXEC sp_set_session_context 'MyName', 'Test2', @read_only = 1;  
SELECT SESSION_CONTEXT(N'MyName') MyName;  

If you set @Rudy Aira _only to 0 or remove that parameter (default 0), it should work.

Answer 2

Hi @David Chase ,

If @Rudy Aira _only = 1, then the value for the specified key cannot be changed again on this logical connection. If 0 (default), then the value can be changed.
Please try:

 ALTER PROCEDURE [dbo].[kd_updtblSupplementsPerPatient]  
     (@PatientID     int,  
      @SupplementID    int,  
      @SuppleValue     real = 0,  
      @UserLoginID    int  
     )  
      
 AS   
      
     BEGIN  
      
         --Put login userid into session context for use in trigger(s)  
         EXEC sp_set_session_context 'USERID', @UserLoginID, @read_only = 0;  
          
         UPDATE dbo.tblSupplementsPerPatient   
            SET [SuppleValue] = @SuppleValue  
         WHERE ([PatientID] = @PatientID)  
           AND ([SupplementID] = @SupplementID);  
          
     END  

For more details, please refer to：sp_set_session_context (Transact-SQL)

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I will try again tomorrow.

Answer 4

I am testing with my asp.net application.

Answer 5

Removed the parameter and it still failed on same error.