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 ca

Solution 1:

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