Geek asks and answers

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

I have a classic ASP application with a SQL2012 database. I recently changed the table column from varchar (8000) to varchar (max), because it was not large enough to hold the required data.

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

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

I use the following parameter declaration as part of an SP call:

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

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

Error: 800a0e7c

Description:
The parameter object is incorrectly defined. Inconsistent or incomplete information was provided.

I thought updating the data would be tough, but I just can't figure out how to get the entire contents of the column.

I am returning a DATALENGTH column, and it says that it has a length of 10 536, but I only get 4000 characters, including spaces returned through the output parameter. I see all the data (10k characters) from Visual Studio, so I know it there.

My connection string is Provider = SQLOLEDB.1. Could this be a problem? Should I use the newer SQL Server Native Client 11.0 OLE DB Provider - SQLNCLI11 ??

Does anyone have any ideas?

Cheers, Mike.

+3
source share
1 answer

Your suggestion about the connection string is indicated on

VARCHAR(MAX) NVARCHAR(MAX) SQL Server SQLOLEDB.1, SQLOLEDB.

'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
+3

More articles:


All Articles