locked
SET CONTEXT_INFO statement requires varbinary(128) NOT NULL Parameter RRS feed

  • Question

  • An error occurs in my program that says: 

    Invalid SQL Statement.
    SET CONTEXT_INFO statement requires varbinary(128) NOT NULL Parameter.

    I am trying to generate a report, my SQL code has been doing fine until it produced this error. How can I fix this? What is wrong from the code?

    DECLARE @Br NVARCHAR(6)
    SET @Br = (select BR from T_SAF where MachineName=HOST_NAME()) 
    DECLARE @Context NVARCHAR (100) 
    SET @Context = @Br
    DECLARE @BinVar varbinary(128) 
    SET @BinVar = CONVERT (Varbinary(128),@Context)
    IF (@BinVar IS NOT NULL) BEGIN
    SET CONTEXT_INFO @BinVar
    END;

    • Changed type rizadawn Thursday, December 4, 2014 8:12 AM
    • Changed type Kalman Toth Thursday, December 4, 2014 8:21 AM
    Thursday, December 4, 2014 7:57 AM

Answers

  • i have  tested your statement on sql2012 sp1
    i used the value 2333655 just for test instead of your  subquery ,it looks work fine

    --------------------------------------------------------
    DECLARE @Br NVARCHAR(6)
    SET @Br = '2333655'
    DECLARE @Context NVARCHAR(100)
    SET @Context = @Br
    DECLARE @BinVar VARBINARY(128)
    SET @BinVar = CONVERT (VARBINARY(128), @Context)
    IF ( @BinVar IS NOT NULL )
        BEGIN
            SET CONTEXT_INFO @BinVar
      PRINT CONTEXT_INFO()
        END

    --0x3200330033003300360035000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000


    -----------------------------------------------------
    depend on the message:“SET CONTEXT_INFO statement requires varbinary(128) NOT NULL Parameter.”

    maybe the @Br parameter  not to set appropriate value

    you can try it out again or take a look at :http://msdn.microsoft.com/en-us/library/ms180125.aspx

    good luck o(∩_∩)o


    • Edited by Steven.桦仔 Thursday, December 4, 2014 1:33 PM edit
    • Marked as answer by Charlie Liao Monday, January 5, 2015 2:25 AM
    Thursday, December 4, 2014 1:32 PM

All replies

  • SQL Server 2014 execution OK:

    DECLARE @Br NVARCHAR(6)
     SET @Br = (select LEFT(ProductNumber,6) from Production.Product where ProductID=800) 
     DECLARE @Context NVARCHAR (100) 
     SET @Context = @Br
     DECLARE @BinVar varbinary(128) 
     SET @BinVar = CONVERT (Varbinary(128),@Context)
     IF (@BinVar IS NOT NULL) BEGIN
     SET CONTEXT_INFO @BinVar
     END;
     -- Command(s) completed successfully.
    




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    Thursday, December 4, 2014 8:01 AM
  • Sir, is my code okay? Then why does it restrict me to preview and generate my report?
    Thursday, December 4, 2014 8:14 AM
  • Whats the value of @BR for the below?

    DECLARE @Br NVARCHAR(6)
    SET @Br = (select BR from T_SAF where MachineName=HOST_NAME()) 

    If @Br is NULL, then it would give the error message exactly what you recieved.

    Try the below to simulate:

    DECLARE @BinVar varbinary(128) = NULL
    SET CONTEXT_INFO @BinVar

    Thursday, December 4, 2014 8:19 AM
  • That code looks OK. Or rather: it looks waterproof. So maybe you are looking in the wrong place, and there is a SET CONTEXT_INFO somewhere else?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 4, 2014 8:19 AM
  • If @Br is NULL, then it would give the error message exactly what you recieved.

    No, because rizadwan has

    IF (@BinVar IS NOT NULL) BEGIN
       SET CONTEXT_INFO @BinVar
    END;

    So if @Br is NULL, the SET CONTEXT_INFO command is never executed.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 4, 2014 11:41 AM
  • i have  tested your statement on sql2012 sp1
    i used the value 2333655 just for test instead of your  subquery ,it looks work fine

    --------------------------------------------------------
    DECLARE @Br NVARCHAR(6)
    SET @Br = '2333655'
    DECLARE @Context NVARCHAR(100)
    SET @Context = @Br
    DECLARE @BinVar VARBINARY(128)
    SET @BinVar = CONVERT (VARBINARY(128), @Context)
    IF ( @BinVar IS NOT NULL )
        BEGIN
            SET CONTEXT_INFO @BinVar
      PRINT CONTEXT_INFO()
        END

    --0x3200330033003300360035000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000


    -----------------------------------------------------
    depend on the message:“SET CONTEXT_INFO statement requires varbinary(128) NOT NULL Parameter.”

    maybe the @Br parameter  not to set appropriate value

    you can try it out again or take a look at :http://msdn.microsoft.com/en-us/library/ms180125.aspx

    good luck o(∩_∩)o


    • Edited by Steven.桦仔 Thursday, December 4, 2014 1:33 PM edit
    • Marked as answer by Charlie Liao Monday, January 5, 2015 2:25 AM
    Thursday, December 4, 2014 1:32 PM