How can I insert more than 8000 characters in a VARCHAR(MAX) column with ExecuteNonQuery?

Question

I am trying to insert > 8000 characters (submit from a web page) via ExecuteNonQuery (and DatabaseFactory.CreateDatabase() from MS Practices Enterprise Library). The stored procedure defines the parameter as VARCHAR(MAX). The column is VARCHAR(MAX). In theory, 2GB of data should be able to be passed.

What can I do to pass data > 8000? I set a breakpoint and the string.Length is indeed > 8K.

   public static void UpdateTerms(string terms)
   {
        Database db = DatabaseFactory.CreateDatabase();
        db.ExecuteNonQuery("uspUpdateTerms", terms);
    }

Stored procedure:

ALTER PROCEDURE [dbo].[uspUpdateTerms]
    @Terms VARCHAR(MAX)
AS
  SET NOCOUNT ON

  INSERT INTO tblTerms(Terms)
  VALUES(@Terms)

Table (just to show that everything is varchar(max)):

CREATE TABLE [dbo].[tblTerms](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Terms] [varchar](max) NULL,
[DateUpdated] [datetime] NULL,

.

Update:

I just changed the code, and this seems to work, though I am not sure what the difference is:

 public static void UpdateTerms(string terms)
 {
        Database db = DatabaseFactory.CreateDatabase();
        DbCommand cmd = db.GetStoredProcCommand("uspUpdateTerms");
        db.AddInParameter(cmd, "Terms", DbType.String, terms);
        db.ExecuteNonQuery(cmd);
 }

Answer 1

REPLICATE returns the input type irrespective of later assignment. It's annoying, but to avoid silent truncation, try:

SET @x = REPLICATE(CONVERT(VARCHAR(MAX), 'a'), 10000);

This is because SQL Server performs the REPLICATE operation before it considers what you're assigning it to or how many characters you're trying to expand it to. It only cares about the input expression to determine what it should return, and if the input is not a max type, it assumes it is meant to fit within 8,000 bytes. This is explained in Books Online:

If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.