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);
 }

Interesting, if I do: insert into Test (data) values (@x + @x + @x) select Data, Len(Data) from Test ... it shows 24000 (not 30K), but obviously allows > 8K — Outside the Box Developer, Jan 15 '13 at 22:42
@twoleggedhorse - `a` is treated as `varchar(1)` not `nvarchar(8000)` (there isn't even such a datatype) — Martin Smith, Jan 15 '13 at 22:47
I meant to say VARCHAR not NVARCHAR, I've reposted my comment as I couldn't edit it again — twoleggedhorse, Jan 15 '13 at 22:48
@twoleggedhorse - What is? A string literal over 8,000 characters is treated as `varchar(max)`. It is the use of `REPLICATE` as the existing answers already indicate. concatenating `varchar(1-8000)` to `varchar(1-8000)` will cause truncation. — Martin Smith, Jan 15 '13 at 22:51
I hadn't noticed this behavior before but 'a' (an undeclared string type) is treated as VARCHAR(8000) by default, you must CAST it to VARCHAR(MAX). +1 for this question. — twoleggedhorse, Jan 15 '13 at 22:51
@MartinSmith So it's the string operation that treats undefined string types as varchar(8000) then? I need to read more around this topic I think. — twoleggedhorse, Jan 15 '13 at 22:54
@twoleggedhorse concatenating a non max to another non max will truncate at 8,000 bytes rather than return a max. — Martin Smith, Jan 15 '13 at 22:55
I highlighted why this was reposted. I was very tempted to close this until I noticed your reasoning. — usr, Jan 15 '13 at 23:29
Please show the SQL Profiler output. EF might be sending the wrong data type to the SP. — usr, Jan 15 '13 at 23:30

Aaron Bertrand · Answer 1 · 2013-01-15T23:06:49.490

3

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.

edited Jan 15 '13 at 23:06

answered Jan 15 '13 at 22:41

Aaron Bertrand

272,866
37
466
490

This test I understand, but ultimately how do I accomplish what I am trying to do, which is pass > 8K from a C# program using ExecuteNonQuery? – Outside the Box Developer Jan 15 '13 at 22:44
@Investor5555 I don't know of any problem sending a larger query from C# (though if your query text is longer than 8K, perhaps you should consider stored procedures, parameterized queries, table-valued parameters, etc). Perhaps your question should illustrate that problem instead of the one we've answered? – Aaron Bertrand Jan 15 '13 at 22:45

competent_tech · Accepted Answer · 2013-01-15T23:40:23.463

The issue may not be the storage of the data, it may be the retrieval.

If you are trying to determine whether or not more than 8000 chars were stored in the DB through enterprise manager, then you are out of luck if you just select the contents of the columns and look at the text length: enterprise manager limits the column output.

To determine how much data is actually stored in the column, execute the following query:

SELECT DATALENGTH(Terms) FROM tblTerms

This will tell you how much text was stored.

EDIT:

Another thought just occurred: the enterprise library caches stored procedure parameters in order to improve performance. If you changed the stored procedure after testing with the parameter set to nvarchar(8000), then switch the parameter to nvarchar(max) without resetting the application (if IIS-hosted, then iisreset or dirty web.config), then you will still be using the old stored proc parameter.

That was probably it. The SP was VARCHAR(8000) and I changed it. I put my code back to the way it was, and it also works. — Outside the Box Developer, Jan 15 '13 at 23:44

Gordon Linoff · Answer 3 · 2013-01-15T22:48:45.797

2

Your sample code can be fixed by doing:

declare @x varchar(max)
set @x = replicate (cast('a' as varchar(max)), 10000)
select @x, len(@x)

edited Jan 15 '13 at 22:48

answered Jan 15 '13 at 22:41

Gordon Linoff

1,242,037
58
646
786

Tim Schmelter · Answer 4 · 2013-04-05T16:23:48.993

1

You haven't shown the code where you are trying to use ExecutenonQuery. Note that you should use parameters.

using(var con = new SqlConnection(conString))
using(var cmd = new SqlCommand("storedProcedureName", con))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@text", SqlDbType.NVarChar, -1);
    cmd.Parameters["@text"].Value = yourVeryLongText;
    cmd.ExecuteNonQuery();
}

edited Apr 05 '13 at 16:23

answered Jan 15 '13 at 22:45

Tim Schmelter

450,073
74
686
939

This is completely a SQL issue - undefined string types are treated as NVARCHAR(8000) by default. – twoleggedhorse Jan 15 '13 at 22:47
1

@twoleggedhorse there is no such thing as `NVARCHAR(8000)`. – Aaron Bertrand Jan 15 '13 at 22:49
public static void UpdateTerms(string terms) { Database db = DatabaseFactory.CreateDatabase(); db.ExecuteNonQuery("uspUpdateTerms", terms); } – Outside the Box Developer Jan 15 '13 at 22:49
@twoleggedhorse . . . Wouldn't that be `nvarchar(4000)`? – Gordon Linoff Jan 15 '13 at 22:49
@Investor5555 please don't shove a bunch of code in comments. Does that look fun to read to you? Either update your question to reflect your actual problem, or start a new question since there are already answers that address the [very different] problem you originally described. – Aaron Bertrand Jan 15 '13 at 22:50
VARCHAR(8000) not NVARCHAR(8000) – twoleggedhorse Jan 15 '13 at 22:55
@timSchmelter - does the argument `-1` for size mean `MAX` ? – whytheq Apr 05 '13 at 14:33
@whytheq: It means that it's inferred from the parameter value which is also default if you don't specify the size. – Tim Schmelter Apr 05 '13 at 16:21

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

4 Answers4

Linked

Related