从存储过程返回varchar(max)输出参数，截断为4000个字符 [英] Returning varchar(max) Output parameter from stored procedure truncating to 4000 characters

问题描述

我有一个带有SQL2012数据库的经典ASP appln.我最近将表列从varchar(8000)更改为varchar(max)，因为它不足以存储所需的数据.

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.

我可以使用需要存储的所有数据来更新列，但是我用来返回列数据作为输出参数的SP仅返回4000个字符(至少这是以下代码的结果)给我:

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)

我将以下参数声明用作对SP的调用的一部分:

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是adBStr的值.我尝试将8更改为200、201和203，但这会产生以下错误:

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

错误:800a0e7c

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.

我返回该列的DATALENGTH，它说它的长度为10,536，但是我只得到4,000个字符，包括通过输出参数返回的空格.我可以从Visual Studio中看到所有数据(1万个字符)，因此我知道其中的数据.

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.

我的连接字符串Provider = SQLOLEDB.1.这可能是个问题吗?我应该使用较新的SQL Server Native Client 11.0 OLE DB提供程序-SQLNCLI11吗?

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??

有人有什么主意吗?

干杯， 迈克.

推荐答案

您对连接字符串的假设已经确定

Your assumption about the connection string is spot on

您需要使用SQL Server Native Client而不是SQLOLEDB.1来支持VARCHAR(MAX)和NVARCHAR(MAX)数据类型，否则它们将被截断回到那里的SQLOLEDB等效项.

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

这篇关于从存储过程返回varchar(max)输出参数，截断为4000个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！