c# - OdbcParameter 错误 - varchar(MAX) 输出参数的大小为 0

Question

我正在使用:

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)

我有一个存储过程，它有一个 varchar(MAX) 输出参数。

我通过 OdbcCommand 调用它，我创建了一个 VarChar 输出参数并将其添加到 OdbcCommand 参数列表:

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

调试时我可以看到参数上的 Size 字段仍然是 -1 但是当我使用 OdbcCommand.ExecuteNonQuery() 运行查询时我得到以下异常:

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()

我尝试使用 OdbcCommandBuilder.DeriveParameters(odbcCommand); 检查从存储过程派生的参数信息，它说参数的大小为 2147483647 -但是，如果我尝试使用该值，我会得到一个不同的异常告诉我

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()

我应该如何配置我的 OdbcCommand 输出参数以从存储过程接收此输出？

这是在现有的生产数据库中，因此尝试更改字段的数据类型是绝对的最后手段。正在选择的字段是 varchar(max)。

编辑:如果实际答案是“你就是做不到”，那是可以接受的——至少我可以肯定地知道。

Answer 1

如果您想坚持使用 System.Data.Odbc，那么以下解决方法可能就足够了。对于存储过程

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

C#代码

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);

}

告诉我

70000 characters were returned