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

What does this large string look like? Is it perhaps something that could be better returned through an additional record set, or is it just note text?

Answer 2

Have you tried not specifying the size?
Could you return a TEXT instead of a VARCHAR(MAX)?

Answer 3

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.

Answer 4

Have you tried specifying:

objOutParam1.Size = Int32.MaxValue;

Answer 5

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 6

The short answer is use TEXT instead of VARCHAR(max). 8K is the maximum size of a database page, where all your data columns should fit in except BLOB and TEXT. Meaning, your available capacity is less than 8k because of your other columns.

BLOB and TEXT is so Web 1.0. Bigger rows mean bigger database replication time, and bigger file I/O. I suggest you maintain a separate file server with an HTTP interface for that.

And, for the previous column

DataUrl VARCHAR(255) NOT NULL,

When inserting a new row, first compute the MD5 checksum of the data. Second, upload the data to the file server with the checksum as the filename. Third, INSERT INTO ...(...,DataUrl) VALUES(..., "http://fileserver/get?id=" . md5_checksum_data)

With this design, your database will stay calm even if the average data size becomes 1000x.

Answer 7

Just use int.MaxValue for the parameter size. The byte[] out of the sproc will be of the correct length. (I'm acutally using varbinary but the results will be the same).

 param.Size = int.MaxValue;
 param.SqlDbType = SqlDbType.VarBinary;

Answer 8

Have you tried with "OleDbType.LongVarChar", this type maps to Text in SQL server 2K, and lets you retrieve more than 8K characters.

Answer 9

I think using -1 for the size would work. At least it should with ADO.NET. Like this:

objOutParam1 = objCommand.Parameters.Add("@RStr", OleDbType.varchar, -1)

This is a long article, but it shows using -1 in the last example:

http://msdn.microsoft.com/en-us/library/bb399384.aspx

Answer 10

The -1 option works pretty well. I use it in several cases where I have a varchar(max) return from a stored proc.