Can I return a varchar(max) from a stored procedure?

Question

VB.net web system with a SQL Server 2005 backend. I've got a stored procedure that returns a varchar, and we're finally getting values that won't fit in a varchar(8000).

I've changed the return parameter to a varchar(max), but how do I tell the OleDbParameter.Size Property to accept any amount of text?

As a concrete example, the VB code that got the return parameter from the stored procedure used to look like:

objOutParam1 = objCommand.Parameters.Add("@RStr", OleDbType.varchar)
objOutParam1.Size = 8000
objOutParam1.Direction = ParameterDirection.Output

What can I make .Size to work with a (max)?

Update:

To answer some questions:

For all intents and purposes, this text all needs to come out as one chunk. (Changing that would take more structural work than I want to do - or am authorized for, really.)

If I don't set a size, I get an error reading "String[6]: the Size property has an invalid size of 0."

Answer 1

Upvoted Ed Altofer. (He answered first, so if you like my answer vote his too).

OleDb is your problem. It's a generic database connection that needs to talk to more than just SQL Server, and as a result you have a lowest common denominator situation where only the weakest composite feature set can be fully supported. One of the lost features is varchar(max) support.

You're using SQL Server 2005 and VB.Net. What's stopping your from using System.Data.SqlClient rather than System.Data.OleDb?

Edit
I found the documentation on the issue. See here:
http://msdn.microsoft.com/en-us/library/ms131035.aspx

The relevant portion:

Return values of data type varchar(max), nvarchar(max), varbinary(max), xml, udt, or other large object types can not be returned to client versions earlier than SQL Server 2005. If you wish to use these types as return values, you must use SQL Server Native Client.

Answer 2

Can you use ADO.NET?

Edit: To clarify, I am just suggesting that you might want to consider ADO.NET since you're working with VB.NET 2005 and SQL Server 2005--OLEDB was the pre-.NET way of accessing databases, so you may find more flexibility by using ADO.NET instead.

You shouldn't return VARCHARs from a stored procedure. I'm not even sure you can.

However, if you use an OUT parameter, you shouldn't have to specify it by size. For example:

SqlParameter p = new SqlParameter("@RStr", SqlDbType.VarChar);
p.Direction = ParameterDirection.Output;

Not sure whether this will suit your needs, but it should work just fine.