SET CONTEXT_INFO语句需要varbinary（128）NOT NULL参数。 [英] SET CONTEXT_INFO statement requires varbinary (128) NOT NULL parameter.

问题描述

首先，在visual studio上本地测试应用程序时不会出现此错误。它只出现在制作中。最重要的是它不会出现任何时候。如果发生该错误，我们重新运行应用程序并重复相同的步骤，则不会再次发生错误。它是随机发生的。一天之内。



这是我的存储过程，我收到错误：

First of all, this error does not appear while testing the application locally on visual studio. It only appears in the production. And most importantly "IT DOES NOT APPEAR EVERYTIME". If that error occurs and we re-run the application and repeat the same steps then the error won't occur again. It occurs randomly..once in a day.

Here is my stored procedure where i get the error:

"SET CONTEXT_INFO statement requires varbinary (128) NOT NULL parameter."

CREATE PROCEDURE [dbo].[SetATUser]
@loginName AS VARCHAR(100)
AS
BEGIN 
DECLARE @userId INT
DECLARE @ctx    VARBINARY(128)

SELECT @userId = UserId
FROM Users
WHERE LoginName = @loginName

SET @ctx = CAST(@userId AS VARBINARY(128))

SET CONTEXT_INFO @ctx
END

现在我认为是loginName那个传递可能是空白的，最终不会为userId取任何行并给出此错误。

这导致找到获取空白loginName的原因。
loginName被赋值在Application_PostAuthet中像这样的global.asax的icate事件：

Now what i think is that the loginName that is passed might be blank ultimately fetching no rows for userId and giving this error.
That leads to finding the reason for getting a blank loginName.
The loginName is assigned the value in Application_PostAutheticate event of global.asax like this:

protected void Application_PostAuthenticateRequest(object sender, EventArgs e)
{
    AUSORD.DataAccess.Common.LoginName = HttpContext.Current.User.Identity.Name;
}

无论何时调用任何存储过程，此登录名都会传递给写在顶部的SetAtUser SP。

And this login name is further passed to SetAtUser SP written on top whenever any stored procedure is called.

public static SqlConnection GetConnectionAT(string loginName)
{
    SqlConnection cnn = new SqlConnection(Common.ConnectionString);

    SqlCommand cmd;

    cmd = new SqlCommand();
    cmd.CommandText = "SetATUser";
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.Connection = cnn;

    cmd.Parameters.AddWithValue("@loginName", loginName);
    cnn.Open();
    cmd.ExecuteNonQuery();

    return cnn;
}

无法弄清楚导致错误的原因。需要帮忙。问题是每次都不会出现错误。它随机发生。在某些日子，应用程序正常工作。

Can't figure out what is causing the error. Need help. The problem is the error does not appear every time. It occurs randomly. On some days the application works fine.

推荐答案

我认为你应该在你的代码中添加一些控制语句并抛出异常和/或写入日志文件amiss。



您可以在多个地方执行此操作，以缩小错误来源。



1.检查代码中其他任何位置是否更改了属性AUSORD.DataAccess.Common.LoginName。



2.方法 Application_PostAuthenticateRequest

I think you should add some control statements in your code and throw exceptions and/or write to a log file if something is amiss.

You can do this in several places in order to narrow down where your error origins.

1. Check if the property AUSORD.DataAccess.Common.LoginName is changed anywhere else in the code.

2. In the method Application_PostAuthenticateRequest

protected void Application_PostAuthenticateRequest(object sender, EventArgs e)
{
    if (String.IsNullOrEmpty(HttpContext.Current.User.Identity.Name))
        throw new ArgumentNullException(); // Or what ever you want to do here

    AUSORD.DataAccess.Common.LoginName = HttpContext.Current.User.Identity.Name;
}

3.方法 GetConnectionAT

3. In the method GetConnectionAT

public static SqlConnection GetConnectionAT(string loginName)
{
    if (String.IsNullOrEmpty(loginName))
        throw new ArgumentNullException(); // Or what ever you want to do here
}

4.在存储过程中

4. In the stored procedure

CREATE PROCEDURE [dbo].[SetATUser]
@loginName AS VARCHAR(100)
AS
BEGIN 
    DECLARE @userId INT
    DECLARE @ctx    VARBINARY(128)
     
    IF (ISNULL(@loginName) OR @loginName = '') THEN
    -- Raise an exception for loginName
    END IF
        
    SELECT @userId = UserId
    FROM Users
    WHERE LoginName = @loginName

    IF (ISNULL(@userId) OR @userId = '') THEN
    -- Raise an exception for userId
    END IF
     
    SET @ctx = CAST(@userId AS VARBINARY(128))
     
    SET CONTEXT_INFO @ctx
END

（非常抱歉） MySQL的语法，但我对此最为熟悉。）



在很多不同的地方做支票似乎有点过分，但你可能会改变稍后代码并从代码的其他部分调用。



如果您决定将错误信息写入日志文件，我建议您添加时间戳，也许sys tem信息，如CPU负载，内存使用情况和活动​​进程列表。

此信息有助于识别模式，例如错误仅发生在一天的特定时间或内存中开始达到最大值或特定进程占用的CPU速度比正常情况高，例如病毒防护。



您没有说您是否在许多计算机上运行相同的软件。

如果是这样，它可能对检查所有计算机上是否发生错误，或只发生一次或只有一次。在这种情况下，工作和不工作的PC有什么区别？

做一个硬件和软件清单，看看你是否能发现任何潜在的罪魁祸首。



我知道这不是你问题的直接解决方案，但也许你会发现这些技巧很有用。

(Very sorry for the MySQL syntax, but I'm most familiar with that.)

It might seem to be overkill to do checks in so many different places, but you might change the code later on and make calls from other parts of the code.

If you decide to write error info to a log file, I suggest that you add a time stamp and perhaps system information such as CPU load, memory usage and a list of active processes.
This info can be helpful to identify patterns, such as the error only occurs at a specific time of the day, or the memory starts to reach the maximum or a specific process is taking more CPU speed than normal, e.g. virus protection.

You don't say if you are running the same software on many computers.
If so it can be useful to check if the error occurs on all machines or just a few or maybe only one. In that case, what is the difference between a working and non working PC?
Do a list of both HW and SW to see if you can spot any potential culprit.

I know this is not a direct solution to your problem, but maybe you can find these tips useful.

这篇关于SET CONTEXT_INFO语句需要varbinary（128）NOT NULL参数。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！