Truncated strings from Stored Procedure with varchar(max) output parameters

[MarkSonghurst]

Is anyone having issues with receiving stored procedure output parameters that are varchar(max) when using gofreetds?
For me they get truncated to 255 characters. However, if my DBA changes the type of the parameter to varchar(4000) (for example), I get up to the full 4000 characters returned.

According to FreeTDS, varchar(max) support should be in TDS protocol version 7.2:
http://www.freetds.org/userguide/choosingtdsprotocol.htm
(gofreetds forces use of TDS 7.2)

Yet I can’t get varchar(max) to work in gofreetds.
We're using SQL Server 2012, FreeTDS 0.95.19 CentOS 7 x86_64

Please can anyone offer any assistance?
Many thanks in advance.

[daliborfilus]

For me, varchar(max) crashes, because gofreetds tries to allocate size(...)+1 bytes, which is 1 larger than maximum allowed byte array size.

[ianic]

Hope that the ca00c05 will fix this.

[MarkSonghurst]

Many thanks for the quick fix, I can now get up to 8k varchar(max) values.

However, as mentioned in #26 8k is not ideal, when up to 2GB can be used.
I have tried #28 but I think that fix is for Results Sets, not output parameters.

Still struggling with this... :-(

[ianic]

Seams that this is limitation of freetds library.
When executing stored procedures parameters are created by calling freetds C function dbrpcparam.
Didn't find a way to pass string longer than 8000 characters to stored procedure with varchar(max) parameter.
I asked a question in freetds mailing list:

I'have stored procedure with output param of datatype varchar(max) and can't find the way to pass string longer than 8000 characters.

Setting 5th parameter in dbrpcparam (maxlen) to value greater then 8000 has no effect. 
In sql server profiler varchar is always declared with max 8000 in size. 
Setting maxlen to 0 has the same effect.
From profiler: 

declare @p1 varchar(8000)
set @p1='                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                '
exec test_sp_varchar_max @p1=@p1 output
select @p1


What is the right way to use stored procedures with varchar(max) datatype.

Thanks,
Igor.

dbrpcparam reference:
http://www.freetds.org/reference/a00342.html#ga98b9b5db66975dafae8d422218e4de45

[MarkSonghurst]

Thanks @ianic - hopefully they will respond.
My research showed that the 8k limit applies to both input and output parameters to a stored procedure call.
Do you agree that this problem remains despite the excellent work in #28 ?
Many thanks. Mark.

[ianic]

Yes, this problem remains.
Stored procedures are handled with different set of api calls.
So far we have solution (thanks to NoICE) for using varchar(max) in queries but not in stored procedure params.

[MarkSonghurst]

For anyone reading this issue, In the end I resorted to using the Microsoft ODBC driver for Linux through database/sql to access varbinary(max) and varchar(max) rows without any issues.