none
Error in SQL-Server 2005 with varchar 8000

    Question

  • Hello,

     

    We have migrated a sql server 2000 database to sql server 2005, this was done through scripts (table, stored procedures and data).

     

    To access this database we are using an ASP.Net 2.0 application which uses the sqlhelper Aplication Block to connect to the database.

     

    Everything works fine except one Stored Procedure which has an OUT varchar(8000) parameter.

     

    We use the following .Net Code to execute the stored procedure this stored procedure:

     

    aParams[2] = sSerDatos.GetParameter("@DominiosMenu", DbType.String, 8000);

    aParams[2].Direction = ParameterDirection.Output;

     

    sSerDatos.ExecuteNonQuery("VM_SDominiosMenu", aParams)

     

    When we invoque the sqlcommand we get this sqlexception:

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

    If we change the DbType.String Size to 4000 in the .Net code everything works, this same procedure work correctly in SQL Server 2000 with the same .Net code.

    Any help would be appreciated.

    Thanks,

    Sam

    Monday, June 05, 2006 3:07 PM

Answers

  • Hello Sam,

    You are seeing this error because DbType.String is always in Unicode format, so its maximum allowed size is 4000 characters (8000 bytes). If you specify size 8000, this would mean 8000 characters, which would be 16,000 bytes, which is not a valid data length.

    You have 2 options - either use size up to 4000 for DbType.String, or change DbType.String to DbType.AnsiString which is not Unicode and allows size of 8000 characters (which would also be 8000 bytes).

    I hope you find this information helpful. Please let me know if I can be of any other help.

    Thanks

    Stoyko Kostov

    Thursday, June 08, 2006 3:11 AM
    Moderator

All replies

  • Hello Sam,

    You are seeing this error because DbType.String is always in Unicode format, so its maximum allowed size is 4000 characters (8000 bytes). If you specify size 8000, this would mean 8000 characters, which would be 16,000 bytes, which is not a valid data length.

    You have 2 options - either use size up to 4000 for DbType.String, or change DbType.String to DbType.AnsiString which is not Unicode and allows size of 8000 characters (which would also be 8000 bytes).

    I hope you find this information helpful. Please let me know if I can be of any other help.

    Thanks

    Stoyko Kostov

    Thursday, June 08, 2006 3:11 AM
    Moderator
  • Hello,

    I also met that problem - db field was nvarchar(max), and in application form one text field was changed from 2000 to 5000 characters. And the same error sill occured. This solution helped me exactly.

    Thanks, Arek

    Wednesday, June 14, 2006 1:21 AM
  • If the db field is nvarchar(max), I can also suggest using SqlParameter and SqlDbType instead of DbParameter and DbType. If you need this parameter to be of type nvarchar(max), simply set its SqlDbType to NVarChar and its size to -1. For example,

    System.Data.SqlClient.SqlConnection conn;

    System.Data.SqlClient.SqlCommand cmd;

    System.Data.SqlClient.SqlParameter p1 = cmd.CreateParameter();

    p1.SqlDbType = SqlDbType.NVarChar;

    p1.Size = -1;

    p1.ParameterName = "name";

    or

    System.Data.SqlClient.SqlParameter sp = new System.Data.SqlClient.SqlParameter("name", SqlDbType.VarChar, -1);

    Please let me know if you have any questions about this example.

    Wednesday, June 14, 2006 6:27 PM
    Moderator
  • Hi,

    I am using .net 2.0 but I do not get

    DbType.nvarchar or .varchar
    Any thoughts please?

    Monday, June 25, 2007 9:31 AM
  • Use DbType.String, it should work ok.
    Monday, June 16, 2008 8:19 PM
  • We used .net v1.1 with sql server 2000

    now we changed to .net v1.1 with sql server 2005.

    Some times we are getting issue, when pass decimal value from .net to sql server 2005 stored procedure?

     

    error:

    The incoming tabular data stream (TDS) remote procedure call (PRC) prorotcal stream is incorrect.

     

    so, we are using math.round to fix this issue. It works well. But, we have to change in lot of places. is there any to fix in one place?

     

    from .net we are passing data type SqlDbType.Decimal

    sql server 2k5 input data type decimal(15,6)

     

    is ther any framerwork or sql server patch fix for this??

     

     

     

    Tuesday, July 29, 2008 7:18 PM
  • Hi, I meet the same problem, but the solution is not for me.

    because my parameter and updatecommand are created by commandbuilder automatically.

    yes, I set the selectcommand to "select * from tableA", that including a column which datatype is varchar(max).

    but obviously, the commandbuilder don't catch the datatype correctly, I use commandbuilder.getupdatecommand to check it's parameter, and found that all the parameter size is 0.

     

     

    How could I do, or how to modify the command created by commandbuilder?

     

    Thank you very much!

    yours,

    Ivan

     

     

    Wednesday, August 06, 2008 1:47 AM
  • We used .net v1.1 with sql server 2000

    now we changed to .net v1.1 with sql server 2005

    I meet the same problem,On the network looking for a long time

     

    We use the following .Net Code to execute the stored procedure this stored procedure:

    DbType.AnsiString

    Size:-1

     

    eg:New DBParameter("AiReport_Content", AiReport_Content, -1, DbType.AnsiString, ParameterDirection.Input)

    it should work ok.

    Saturday, November 22, 2008 7:56 AM
  • Stoyko,

    I am having a similar issue.

    .Net 2.0 & SQL Server 2005.  I need to have the parm able to pass unicode since the users are passing extended chars.  In addition, the parm size get large and it varies. 

    Here's what is happening:

    On the SQL server side, the stored proc has the parm defined nvarchar(max).   On .NET as I define the parm, it is set to DBType = AnsiString;  Size = 4148.  If I set SqlDbBType = sqldbtype.varchar, it works but I can't send my unicode values.  If I set SqlDbBType = sqldbtype.nvarchar, I get the error:

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

    I tried changing the size to -1 as you listed above, leaving everything else the same, but with nvarchar it gives me a an error on the length, saying it must be greater than 0.

    Any other suggestions??

    Thanks!

    Friday, June 12, 2009 3:31 PM