Error in SQL-Server 2005 with varchar 8000

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

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

|||

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

|||

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.

|||

Hi,

I am using .net 2.0 but I do not get

DbType.nvarchar or .varchar
Any thoughts please?

Error in SQL-Server 2005 with varchar 8000

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

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

|||

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

|||

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.

|||

Hi,

I am using .net 2.0 but I do not get

DbType.nvarchar or .varchar
Any thoughts please?

error importing from AS00

I am trying to transfer with dts a table of As400 into a db sql-server.

But i receave un error (2147217865)

With Access i can import the table with the same ODBC.

You have some suggestion ?

thk by Logan bye bye[posted and mailed, please reply in public]

Logan (logan1577@.hotmail.com) writes:
> I am trying to transfer with dts a table of As400 into a db sql-server.
> But i receave un error (2147217865)
> With Access i can import the table with the same ODBC.
> You have some suggestion ?

There wasn't an text with the error message? You are terribly short on
details, but since I don't use DTS nor know anything about connecting to
AS400, I would not be able to say that much more.

However, I did look up the error code in a book I have, and if the error
comes from the OLE DB provider, the error is "The specified table does not
exist". Whether that refers to the table on the AS400 side or on the
SQL Server side, I don't know.

You probably need to add some deubgging to your DTS package. As I
mentioned I don't use DTS myself, so I cannot really help. But you
could check out the group microsoft.public.sqlserver.dts where the
really sharp DTS people hang out.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp