title: Return strings from SQL Server stored procedures being truncated
body: <p>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?</p>
  
  <p>Details: my stored procedure is defined as:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[spCourseButtonsDisplay]
      @str NVARCHAR(MAX) OUTPUT
  </code></pre>
  
  <p>The string concatenation in the stored procedure uses a <code>cast</code> (I read somewhere that this would guard against truncation):</p>
  
  <pre><code>SET @str = CAST(@str + ... AS nvarchar(MAX));
  </code></pre>
  
  <p>Finally, I return with: </p>
  
  <pre><code>RETURN len(@str)
  </code></pre>
  
  <p>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.</p>
  
  <p>I define the stored procedure parameters in my asp code:</p>
  
  <pre><code>.Parameters.Append .CreateParameter("StrLen", 3, 4)  'adInteger, adParamReturnValue
  .Parameters.Append .CreateParameter("Str", 202, 2, 2147483646)   'adVarWChar, adParamOutput
  </code></pre>
  
  <p>The 2147483646 is one less than MAX, but is the largest number I can put without an error.</p>
  
  <p>But when I execute the stored procedure from asp and output these 3 values:</p>
  
  <pre><code>Response.Write(CStr(objSP.Parameters("StrLen")) + "&lt;br/&gt;")
  Response.Write(CStr(Len(objSP.Parameters("Str"))) + "&lt;br/&gt;")
  Response.Write(objSP.Parameters("Str"))
  </code></pre>
  
  <p>I get the actual length the stored procedure created (>4000), 4000, and a truncated string.</p>
  
  <p>How can I get the whole string?</p>