0
DECLARE @Ctx varbinary(128)
DECLARE @username varchar(30)
SET @Username='ibica'
SELECT @Ctx = CONVERT(varbinary(128), @Username)
SET CONTEXT_INFO @Ctx

    SET @username = CONVERT(VarChar(128), CONTEXT_INFO());
    PRINT @username
    DECLARE @ID_User int
    SET @ID_User = ( SELECT Users.ID_User
                       FROM Users
                         WHERE Users.Username=@username )
    PRINT @ID_User

I'm setting the context_info correctly, because PRINT @username returns the right value, but I get an empty line on the second print. If i put a value instead of @username in the where clause above, it also prints the right value. What am I missing? 

DECLARE @username varchar(30)
    SET @username = 'ibica';
    PRINT @username
    DECLARE @ID_User int
    SET @ID_User = ( SELECT Users.ID_User
                       FROM Users
                         WHERE Users.Username=@username )
    PRINT 'smth'
    PRINT @ID_User PRINT 'smth'

I removed the CONTEXT_INFO and now my output is correct. So it has to have something to do with CONTEXT_INFO.

Tanatos Daniel
  • 558
  • 2
  • 9
  • 27
  • I'm trying to set and retrieve the value of context_info, then use it in order to retreve some other info from my database.. It's for the first time I'm using context_info, and although I believe my empty line appears from other reasons, I can't exclude a possibly missusing of it. – Tanatos Daniel Apr 05 '14 at 18:48
  • 1
    You're converting varchar(30) -> varbinary(128) then varbinary(128) -> varchar(128). What happens when you stick to the same data type (meaning, convert back to varchar(30), or whatever length is defined in the table)? – Aaron Bertrand Apr 05 '14 at 18:53
  • I changed 30 with 128 and nothing changed.. – Tanatos Daniel Apr 05 '14 at 18:55
  • ok, never mind me (if you read my previous comments) – Lasse V. Karlsen Apr 05 '14 at 18:58
  • 1
    CONTEXT_INFO is really messy for this kind of thing. You either need to (a) convert the column to VARBINARY to match when comparing (and this will force a scan), (b) `WHERE username = LEFT(CONVERT(VARCHAR(30), CONTEXT_INFO()), LEN(username));` – Aaron Bertrand Apr 05 '14 at 19:07
  • @AaronBertrand, you just saved me. I only tried (b), and worked. Thank you so much! – Tanatos Daniel Apr 05 '14 at 19:15
  • I updated my trigger with the correct sequence. I have a Windows Forms Application and I'm setting CONTEXT_INFO using a stored procedure when I log in. The trouble is that the trigger on update fails to get correct information, so I'm guessing I have to set it somewhere else. Where would that be? – Tanatos Daniel Apr 05 '14 at 19:42
  • Found out here http://www.experts-exchange.com/Database/MS-SQL-Server/SQL-Server-2005/Q_27142314.html that I have to set CONTEXT_INFO in the UPDATE procedure for the trigger to retrieve it correctly. – Tanatos Daniel Apr 05 '14 at 20:19

0 Answers0