SQL Migration - Varchar 8000 in SQL 2005

[Guest]

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 works correctly in SQL Server 2000 with the same
..Net code.

Any help would be appreciated.
Thanks,
Sam

 

[Cowboy \(Gregory A. Beamer\)]

Probably a Unicode issue? To check see what happens if you change it to 7999
or even text. If 7999 works, I would shave off the one character and use it.
If not, text might be an option, although it is a pain.

The other option is to move away from out parameters and work with dataset
returns, but this may require tooling the application, so leave as a last
resort. On the other hand, in very general architecture terms, unless you
are always using OUT params for returns, out params are better served for
base data types (value types in .NET languages).

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************