3

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.

Mike
  • 105
  • 3
  • 10
  • Couldn't you just use a text column rather than varchar, then you can enter as many characters as you like (well, up to 2GB) – John Feb 10 '14 at 02:13
  • my understanding is that text was deprecated back with SQL2005. Varchar(max) gives you the same ability upto 2GB anyway. I've read about the truncation occuring because of implicit casting from varchar(max) to varchar, but I can't see where this would occur in my case. Thanks for commenting. – Mike Feb 10 '14 at 02:17
  • http://stackoverflow.com/questions/834788/using-varcharmax-vs-text-on-sql-server Connection string -- not issue – Zam Feb 10 '14 at 07:44
  • @Mike I might be wrong but don't you just use `cmd.Parameters.Append cmd.CreateParameter("@detail", 200, 3, -1, strDetail)`? - Prefer to use the ADO constants by adding the `metadata` into the `global.asa` so it would be `cmd.Parameters.Append cmd.CreateParameter("@detail", adVarChar, adParamInputOutput, -1, strDetail)` see [this](http://carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx) excellent resource I swear by. Any reason your using `adParamInputOutput` (3) instead of `adParamInput` (1)? – user692942 Feb 10 '14 at 11:31
  • Hi @Lankymart. It doesn't like 200,3,-1 either sorry. I get the following error: ErrorCode="800a0e7c", Description="Parameter object is improperly defined. Inconsistent or incomplete information was provided." I've actually changed the direction of the parameters from 3 to 2 as they are all Output parameters from the SP. Cheers. – Mike Feb 11 '14 at 04:12
  • @Lankymart I have changed the parameter mode from 3 to 2 with no difference. In the end I have conceded defeat on this and have resorted to using 2 output parameters of varchar(8000) and splitting the column data over both of them using LEFT and SUBSTRING functions within SQL. – Mike Feb 11 '14 at 21:41
  • @Mike If it's only returning 4000 characters it's behaving like `nvarchar` not `varchar` which would be 8000 characters. It's a SQL Server 2008 database right, does the compatibility mode reflect that? That's all I can think of, only thing I can think of that would force the data type in row. Maybe this [thread](http://stackoverflow.com/q/2502734/692942) will help. – user692942 Feb 11 '14 at 21:49
  • @Mike Bit more digging think maybe you're right with the connection string trail of thought see [this article](http://ask.sqlservercentral.com/questions/5607/adodb-output-parameter-for-varcharmax.html) `"You have to be using SQL Native Client, and not SQLOLEDB. Because SQLOLEDB only understands old-school LOB types, the new large-value types get mapped to old-school LOB types at run-time if you're using SQLOLEDB"` – user692942 Feb 11 '14 at 21:53

1 Answers1

3

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
user692942
  • 16,398
  • 7
  • 76
  • 175