Error when executing a stored proc in asp.net

Question

I am attempting to execute a stored proc in asp.net in the code behind. The parameter I am trying to pass is strErrorMessage that contains a value of "The transport failed to connect to the server.; ".

The error message when the query gets executed is: 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.

Update with code

    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 column datatype in the table is nvarchar(MAX).
Any ideas how to resolve this?

Answer 1

EDIT: You've declared your parameter size to be 8000, but NVARCHAR only supports up to 4000 characters. I strongly suspect that's the problem.

---

Well it would help if you'd post some code, first...

I would advise you to try to diagnose this via a console app - it'll be easier to iterate that way than via ASP.NET. I suspect you've got the wrong type for your first parameter... but it does sound a slightly odd message to get.

What does your connection string look like? Perhaps it's trying to use SQL Server 2008 features?

Answer 2

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

Here is a Microsoft Support article that may help.

According to the article

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.

Answer 3

You set the type and lenght of your first parameter here

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

If the lenght of the parameter is different (larger or smaller) than the lenght specified in the database you will get an error.

Hope this helps.

Answer 4

See this:

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

This may be because your parameter size is between 4001 and 8000.