CODEWITHSUNDEEP
c#asp.netsql-server-2005.net-3.5
user279521
user279521

Reputation: 4807

Error when executing a stored proc in asp.net

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?

Upvotes: 3

Views: 7315

Answers (6)

RonaldPaguay
RonaldPaguay

Reputation: 335

I ended up here looking for a solution for this error in TypeOrm.

The problem was that a column value was too long for its length.

The way I could spot the problem was by enabling logging: true in ormconfig.json.

Upvotes: 0

JJS
JJS

Reputation: 6678

SqlException.Number was 8004.

I had set the CommandType on the DbCommand to CommandType.StoredProcedure.

Changing to CommandType.CommandType.Text fixed the problem.

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1502546

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?

Upvotes: 2

MusiGenesis
MusiGenesis

Reputation: 75356

See this:

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

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

Upvotes: 2

Constantin
Constantin

Reputation: 465

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.

Upvotes: 2

etoisarobot
etoisarobot

Reputation: 7814

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.

Upvotes: 8

Related Questions