c# - 在 asp.net 中执行存储过程时出错

Question

我试图在后面的代码中执行 asp.net 中的存储过程。我试图传递的参数是 strErrorMessage，它包含一个值 "The transport failed to connect to the server.; "。

执行查询时的错误消息是:传入的表格数据流 (TDS) 远程过程调用 (RPC) 协议(protocol)流不正确。参数 1(“@errMessage”):数据类型 0xE7 的数据长度或元数据长度无效。

用代码更新

    try
    {
        ...
        ...
        ...
    }
        catch (Exception ex)
        {
            ClientScript.RegisterStartupScript(GetType(), "alert", "alert('Email was not sent - " + ex.Message + "');", true);

            string strMessage = ex.Message;
            string strStackTrace = ex.StackTrace;

            strMessage = strMessage.Replace("\r\n", "; ");
            strMessage = strMessage.Replace("   ", "");

            strStackTrace = strStackTrace.Replace("\r\n", "; ");
            strStackTrace = strStackTrace.Replace("   ", "");
            AppErrorLog(strMessage, strStackTrace);
            return false;
        }


    protected void AppErrorLog(string strErrorMessage, string strErrorStackTrace)
    {
        SqlConnection conErrLog = new SqlConnection(strConn);
        string sql = "usp_AppErrorLog_AddRecord";
        SqlCommand cmdErrLog = new SqlCommand(sql, conErrLog);
        conErrLog.Open();
        try
        {
            cmdErrLog.CommandType = CommandType.StoredProcedure;

            cmdErrLog.Parameters.Add(new SqlParameter("@errMessage", SqlDbType.NVarChar, 8000));
            cmdErrLog.Parameters["@errMessage"].Value = strErrorMessage;

            cmdErrLog.Parameters.Add(new SqlParameter("@errStackTrace", SqlDbType.NVarChar, 8000));
            cmdErrLog.Parameters["@errStackTrace"].Value = strErrorStackTrace;

            cmdErrLog.Parameters.Add(new SqlParameter("@userID", SqlDbType.VarChar, 12));
            cmdErrLog.Parameters["@userID"].Value = User.Identity.Name;

            SqlDataAdapter ada = new SqlDataAdapter(cmdErrLog);
            cmdErrLog.ExecuteNonQuery();
        }
        catch(Exception e)
        {
            ClientScript.RegisterStartupScript(GetType(), "alert", "alert('AppErrorLog - " + e.Message + "');", true);
        }
        finally
        {
            conErrLog.Close();
        }
    }

表中的列数据类型为 nvarchar(MAX)。
有什么解决办法吗？

Answer 1

这部分“数据类型 0xE7 的数据长度无效”使我相信参数 strErrorMessage 被指定为具有比 SQL 参数 DataType 可以处理的更多数据长度。

Here是一篇可能有所帮助的 Microsoft 支持文章。

根据文章

When you specify an NVarChar parameter with SqlParameter.Size between 4001 and 8000, SqlClient will throw the following exception.

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter ("@"): Data type 0xE7 has an invalid data length or metadata length.

To work around this issue, use one of the following options:

· Set Sqlparamter.size property to -1 to ensure that you are getting the entire data from the backend without truncation.

· When working with String DbTypes whose sizes are greater than 4000, explicitly map them to another SqlDBType like NText instead of using NVarchar(which also is the default SqlDBType for strings).

· Use a value that is not between 4001 and 8000 for Sqlparameter.size.