1

I'm trying to pass XML that is greater than 4000 characters to a stored procedure. I've tried using NTEXT, NVARCHAR(MAX), VARCHAR(MAX), VARCHAR(8000) and XML as the parameter type but I am always getting the error:

System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated.

The value is not being stored into a table, it's only a variable that has data selected out of it and then stored into tables.

Can ASP.NET not send more than 4000 characters as a parameter? Or is there a setting in SQL Server I can switch off?

Thanks,

Thomas

EDIT:

This is working with NTEXT. I went into the error details and found what line of the SQL was causing the error and it turns out there was a completely unrelated column set to VARCHAR(50) that should have been VARCHAR(MAX) - I assumed it was coming from the 10000 character XML but it was totally unrelated. My apologies.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tsdexter
  • 2,911
  • 4
  • 36
  • 59
  • What's the length of that column you're trying to write to in the database? – MilkyWayJoe May 03 '12 at 19:44
  • If you're passing the value in as a parameter, have you remembered to up the width of the parameter as well as the datatype? That one bites me every so often... – PhilPursglove May 03 '12 at 19:44
  • @MilkyWayJoe I'm not writing the string to a column, I'm selecting smaller bits out data out of the XML to different columns. The >4000 char string is only ever a parameter, it is not written to a table. – tsdexter May 03 '12 at 19:49
  • @PhilPursglove What do you mean by this? I'm declaring the parameter like "@XmlString XML = NULL " and "AddParameter(objCmd, "@XmlString ", SqlDbType.Xml, -1, ParameterDirection.Input, False, strXML)" – tsdexter May 03 '12 at 19:50
  • 4
    Show us the code so we can point the error, rather than guess. The truncation occurs because your code truncates it. – Remus Rusanu May 03 '12 at 19:52
  • Shame you're not using sql-server 2008, I don't believe table-valued param's have a limit – Chris Moutray May 03 '12 at 19:58
  • When you set the parameter value to a string (assuming strXML is a string?) instead of an actual Xml object it will treat it as NVarChar which has a 4k limit. See the answer on this SO question for the solution: http://stackoverflow.com/questions/574928/c-sql-whats-wrong-with-sqldbtype-xml-in-procedures – Nick Bork May 03 '12 at 20:19
  • 1
    You shouldn't be using `NTEXT` anymore - it's deprecated and will be removed from SQL Server soon. Use `NVARCHAR(MAX)` instead - it has all the same beneficial properties and can even be treated just like a "normal" `NVARCHAR(x)` string - use all string functions against it (handling `NTEXT` always was very kludgy) – marc_s May 03 '12 at 20:23
  • 3
    Consider deleting the question! – Ben May 03 '12 at 20:49

3 Answers3

2

Break the XML in smaller chunks and assign them on seperate variable and then concatenate them

DECLARE @SQL1,@SQL2

SET @SQL1=chunk1

SET @SQL2=chunk2

SET @SQLn=chunkn

exec (@SQL1+@SQL2.....+@SQLn)

sumit
  • 15,003
  • 12
  • 69
  • 110
1

This is an sql server limitation in all edition. You can split the xml in two varchar variables and merge them into the stored procedure. It's an workaround for the issue.

Romil Kumar Jain
  • 20,239
  • 9
  • 63
  • 92
0

Seems there are several ways to do this. This is how I recently went about it.

Community
  • 1
  • 1
Tim
  • 1,174
  • 12
  • 19