[SOLVED] Returning varchar(max) Output parameter from stored procedure truncating to 4000 characters

Returning varchar(max) Output parameter from stored procedure truncating to 4000 characters

I've got a classic ASP appln with a SQL2012 database. I recently changed a table column from varchar(8000) to varchar(max) as it wasn't big enough to store the required data.

I can update the column with all of the data I need to store, but the SP I use to return the column data as an output parameter is only returning 4000 characters (at least that is what the result of the following code is giving me:

Len(cmd.Parameters("@detail").Value)

I'm using the following parameter declaration as part of the call to the SP:

cmd.Parameters.Append cmd.CreateParameter("@detail", 8, 2,  -1, strDetail)

8 being the value for adBStr. I've tried changing the 8 to 200, 201 and 203 but this gives the following error:

Error: 800a0e7c

Description:
Parameter object is improperly defined. Inconsistent or incomplete information was provided.

I thought updating the data would be the hard bit, but I just cant work out how to retrieve the entire contents of the column.

I'm returning the DATALENGTH of the column and it says it is 10,536 in length but I'm only getting 4,000 characters including spaces returned via the output parameter. I can see all of the data (10k chars) from Visual Studio so I know its in there.

My connection string Provider=SQLOLEDB.1. Could this be an issue? Should I be using the newer SQL Server Native Client 11.0 OLE DB Provider - SQLNCLI11??

Anyone got any ideas?

Cheers, Mike.

Solution

Your assumption about the connection string is spot on

You need to the use the SQL Server Native Client instead of SQLOLEDB.1 to support the VARCHAR(MAX) and NVARCHAR(MAX) data types otherwise they will be truncated back to there SQLOLEDB equivalents.

You then want to be using the following parameter definitions

'For varchar(max) OUTPUT use;
Call cmd.Parameters.Append(cmd.CreateParameter("@detail", adLongVarChar, adParamOutput, -1, strDetail))

'For nvarchar(max) OUTPUT use;
Call cmd.Parameters.Append(cmd.CreateParameter("@detail", adLongVarWChar, adParamOutput, -1, strDetail))

'** Constants **
' adLongVarChar = 201
' adLongVarWChar = 203
' adParamOutput = 2