Error executing saved process in asp.net

Question

I am trying to execute a saved proc in asp.net in the code behind. The parameter I'm trying to pass is strErrorMessage , which contains the value "The transport failed to connect to the server.; ".

Error message when executing the request: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@errMessage"): Data type 0xE7 has an invalid data length or metadata length.

Code Update

    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();
        }
    }

The type of column in the table is nvarchar (MAX).
Any ideas how to solve this?

Answer 1

This part, "Data type 0xE7 has an invalid data length," leads me to conclude that the strErrorMessage parameter is specified as having more data length than the DataType SQL Parameter can handle.

Microsoft, .

NVarChar SqlParameter.Size 4001 8000, SqlClient .

(TDS) (RPC) . ( "@" ): 0xE7 .

, :

· Sqlparamter.size -1, , .

· DbTypes, , 4000, SqlDBType NText NVarchar ( SqlDBType ).

. , 4001 8000 Sqlparameter.size.

Answer 2

EDIT: , 8000, NVARCHAR 4000 . , .

---

, , , ...

- , ASP.NET. , ... , .

? , SQL Server 2008?

Answer 3

cmdErrLog.Parameters.Add(new SqlParameter("@errMessage", SqlDbType.NVarChar, 8000));

( ), , , .

, .

Answer 4

:

http://support.microsoft.com/kb/970519

This may be due to the fact that your parameter size is from 4001 to 8000.

Answer 5

SqlException.Number was 8004.

I installed CommandType in DbCommand in CommandType.StoredProcedure.

When changing in CommandType.CommandType.Text, the problem is fixed.