OdbcParameter error - size of 0 for varchar(MAX) output parameter

Question

I'm using:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)

I have a stored procedure which has a varchar(MAX) output parameter.

I'm calling it via an OdbcCommand and I've created a VarChar output parameter and added it to the OdbcCommand Parameters list:

new OdbcParameter("@MaxField", OdbcType.VarChar)
    { Direction = ParameterDirection.Output, Size = -1 })

While debugging I can see that the Size field on the parameter is still -1 but when I run the query using OdbcCommand.ExecuteNonQuery() I get the following exception:

System.InvalidOperationException was unhandled
HResult=-2146233079
Message=String[2]: the Size property has an invalid size of 0.
Source=System.Data
StackTrace:
at System.Data.Odbc.OdbcParameter.GetParameterSize(Object value, Int32 offset, Int32 ordinal)
at System.Data.Odbc.OdbcParameter.PrepareForBind(OdbcCommand command, Int16 ordinal, Int32& parameterBufferSize)
at System.Data.Odbc.OdbcParameterCollection.CalcParameterBufferSize(OdbcCommand command)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()

I tried using the OdbcCommandBuilder.DeriveParameters(odbcCommand); to examine the parameter information derived from the stored procedure and it said that the parameter had a Size of 2147483647 - however, if I try and use that value I get a different exception telling me

System.Data.Odbc.OdbcException was unhandled
ErrorCode=-2146232009
HResult=-2146232009
Message=ERROR [42000] [Microsoft][ODBC SQLServer Driver][SQL Server]Invalid parameter 2 (''): Data type 0x23 is a deprecated large object, or LOB, but is marked as output parameter. Deprecated types are not supported as output parameters. Use current large object types instead. Source=SQLSRV32.DLL StackTrace:
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()

How should I configure my OdbcCommand output parameter to receive this output from the stored procedure?

This is on an existing, in-production database so trying to change the datatype of the field is sadly the absolute last resort. The field that is being selected is varchar(max).

Edit: If the actual answer is "You just can't" then that would be acceptable - at least I would know for sure.

You need to use a reasonable size for your output parameter. 2147483647 is too large. What value do you expect your stored procedure to return? — Racil Hilan, Nov 13 '17 at 14:10
The field is defined as `varchar(max)` - it will be able to hold any value that fits. The value of 2147483647 comes **from the OdbcCommandBuilder**. — Matt Hogan-Jones, Nov 13 '17 at 14:11
@RacilHilan This question - https://stackoverflow.com/questions/973260/what-size-do-you-use-for-varcharmax-in-your-parameter-declaration - has the accepted answer stating **-1** is the `size` to use for `varchar(max)` but that is on an input parameter. I'm trying to work out how to get a `varchar(max)` output parameter working. — Matt Hogan-Jones, Nov 13 '17 at 14:15
I understand, but that's a terrible design even if you get it to work. Check what value is returned and use a reasonable size accordingly. — Racil Hilan, Nov 13 '17 at 14:15
@RacilHilan Let me put it this way - in a varchar(max) field, how large do **you** think it would be? — Matt Hogan-Jones, Nov 13 '17 at 14:16
If you read the message that you posted in your question carefully, it clearly says: *"Deprecated types are not supported as output parameters"*, so the other question used it as input parameter, but you cannot use it as output. Both cases are terrible design. — Racil Hilan, Nov 13 '17 at 14:18
No, let me put it in another way. I don't care how big `varchar(max)` is, it shouldn't be used as parameter (neither input nor output). Use a reasonable size like 250. — Racil Hilan, Nov 13 '17 at 14:20
@RacilHilan thanks for the comments, but it's not getting me any closer to a solution. As I said, this is on an existing, in-production database so trying to change the datatype of the field is sadly the absolute last resort. And I can tell you now that the data is going be bigger than 250. — Matt Hogan-Jones, Nov 13 '17 at 14:21
The 250 was just an example. Use another value if you like. What solution do you wan, it is telling you clearly that you cannot use it for output parameter, so what solution do you want if you refuse to fix the broken stored procedure? I understand your situation and you may have limited control over the existing db, but I don't see how that can be fixed in another way. You can use the maximum acceptable (non-deprecated) value if you like, does that work for you? — Racil Hilan, Nov 13 '17 at 14:27
@MattJones - Just curious: Given that you are working in C#, is there a particular reason why you are using `System.Data.Odbc` instead of `System.Data.SqlClient`? — Gord Thompson, Nov 13 '17 at 14:37
@GordThompson I'm extending some existing code that is currently written using the `Odbc` classes - if this turns out to be a complete non-starter then I will definitely look into the `SqlClient` as an alternative. — Matt Hogan-Jones, Nov 13 '17 at 14:40
@RacilHilan saying that a `varchar(MAX)` output parameter is just not going to be possible is an acceptable answer. The purpose of this question is to find that out. — Matt Hogan-Jones, Nov 13 '17 at 14:42

score 2 · Accepted Answer · answered Nov 13 '17 at 15:24

If you want to stick with System.Data.Odbc then the following workaround might suffice. For the stored procedure

CREATE PROCEDURE [dbo].[HodorSpeaks] 
    @repeat int = 1, 
    @response varchar(max) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @whatHeSaid varchar(max);
    SET @whatHeSaid = 'HODOR! ';
    SELECT @response = REPLICATE(@whatHeSaid, @repeat);
END

the C# code

using (var cmd = new OdbcCommand())
{
    cmd.Connection = conn;
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.CommandText = 
        "SET NOCOUNT ON; " +
        "DECLARE @out varchar(max); " +
        "EXEC dbo.HodorSpeaks @repeat=?, @response=@out OUTPUT; " +
        "SELECT @out;";
    cmd.Parameters.Add("?", OdbcType.Int).Value = 10000;
    string resp = cmd.ExecuteScalar().ToString();
    Console.WriteLine("{0} characters were returned", resp.Length);

}

tells me that

70000 characters were returned

Very interesting, and nicely sneaky. But I think I am going to have to accept that trying to get this to work "elegantly" is a forlorn hope. — Matt Hogan-Jones, Nov 13 '17 at 15:32
Nice trick to avoid declaring an output parameter. Inelegant, but simple and works. +1 — Racil Hilan, Nov 13 '17 at 17:55

Racil Hilan · Answer 2 · 2017-11-13T17:43:59.463

varchar(max) was introduced in SQL Server 2005 to replace the text type. When the value is less than 8000, it is stored on page like a standard varchar(n), while values above 8000 are stored off page like text.

The use of text or varcha(max) as a parameter is not recommended, and as the message is telling you, it is even not supported for output parameters:

Data type 0x23 is a deprecated large object, or LOB, but is marked as output parameter. Deprecated types are not supported as output parameters. Use current large object types instead.

The best solution is to fix the stored procedure and change the output parameter from varchar(max) to varchar(n). Otherwise, you can use varchar(8000) in your code for the parameter and hope that the stored procedure will never return a value more than 8000. The value 8000 is the maximum for the varchar(n) type.

Also, you may get around it by using a different (older) version of the ODBC driver that doesn't complain about the use of varchar(max) as an output paramter, but that obviously is not the best way to go.

Thanks for the extended answer based on your comments - in an ideal world I would prefer to rewrite the underlying database completely! — Matt Hogan-Jones, Nov 13 '17 at 15:30
Yes, it is often better to rewrite badly coded applications, but sadly we're not in an ideal world and sometimes we have to live with what we've got, so I understand your situation. — Racil Hilan, Nov 13 '17 at 17:49

OdbcParameter error - size of 0 for varchar(MAX) output parameter

2 Answers2