1

I'm running a Website using classic ASP and SQL Server 2012, on IIS 7.5. The website is data-driven, and I'm using stored procedures to create strings of HTML text to present the Web page. But the strings passed back from the stored procedures are being truncated to 4000 characters. How do I keep truncation from happening?

Details: my stored procedure is defined as:

CREATE PROCEDURE [dbo].[spCourseButtonsDisplay]
    @str NVARCHAR(MAX) OUTPUT

The string concatenation in the stored procedure uses a cast (I read somewhere that this would guard against truncation):

SET @str = CAST(@str + ... AS nvarchar(MAX));

Finally, I return with: 

RETURN len(@str)

Note that I can execute the stored procedure in SQL Server Management Studio and get strings longer than 4000 (I've tested with >12000). But not so in asp.

I define the stored procedure parameters in my asp code:

.Parameters.Append .CreateParameter("StrLen", 3, 4)  'adInteger, adParamReturnValue
.Parameters.Append .CreateParameter("Str", 202, 2, 2147483646)   'adVarWChar, adParamOutput

The 2147483646 is one less than MAX, but is the largest number I can put without an error.

But when I execute the stored procedure from asp and output these 3 values:

Response.Write(CStr(objSP.Parameters("StrLen")) + "<br/>")
Response.Write(CStr(Len(objSP.Parameters("Str"))) + "<br/>")
Response.Write(objSP.Parameters("Str"))

I get the actual length the stored procedure created (>4000), 4000, and a truncated string.

How can I get the whole string?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JohnC48
  • 21
  • 5
  • 2
    Don't change your datatype to TEXT. @MohsinMehmood the TEXT datatype has been deprecated in favor of varchar(max) for almost 15 years now. – Sean Lange May 22 '18 at 19:23
  • 1
    @MohsinMehmood: this is **horribly bad** advice - the `TEXT` datatype is **deprecated** and should **NOT** be used in any way anymore - don't suggest this - please! – marc_s May 22 '18 at 20:22
  • Seen this? [Returning varchar(max) Output parameter from stored procedure truncating to 4000 characters](https://stackoverflow.com/questions/21667805/returning-varcharmax-output-parameter-from-stored-procedure-truncating-to-4000) – Flakes May 24 '18 at 02:13
  • 1
    This worked (although in my environment I had to use SQLNCLI10)! Thank you @SearchAndResQ! I successfully tested this to 12549 characters. – JohnC48 May 29 '18 at 00:18

2 Answers2

1

The suggestion above by @SearchAndResQ to check out Returning varchar(max) Output parameter from stored procedure truncating to 4000 characters is what I needed: "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 their SQLOLEDB equivalents". I changed my connection string from Provider=SQLOLEDB.1; to Provider=SQLNCLI10;. I've successfully tested with >12000 characters. Be careful to use the appropriate version of SQL Server Native Client (SQLNCLI11, SQLNCLI10, or SQLNCLI).

JohnC48
  • 21
  • 5
0

Try using -1 as the length of the return parameter.

BoCoKeith
  • 817
  • 10
  • 21
  • A negative 1?! Well, on the chance @BoKeith is not being facetious, I tried it at both the SP end (substitute for max) and the ado end (substitute for 2147483646), and both give an error. Sorry, no up-vote! – JohnC48 May 23 '18 at 19:36
  • By the way, a colleague suggested I break up my SP output into a number of 4000-character strings. But I have no idea how many I'd need! Some of the DB fields I'm collecting for the output are nvarchar(max) themselves (such as the transcript for a lecture). There has to be a better way. – JohnC48 May 23 '18 at 19:50
  • I wasn't being facetious; it worked somewhere for me recently. Too bad it didn't work here. – BoCoKeith May 24 '18 at 22:31