asked
Inserting data from source datatype nvarchar(max) to destination datatype XML and get "string or binary data would be terminated"
This is the bit of SQL causing the issue. The field is MESSAGE and the source datatype is nvarchar(max). The destination table has the field datatype of XML. The error message is "string or binary data would be truncated". I have dumped the source data into Excel and used the LEN function to check the length of the field. The max length is 320,000 characters. XML is unlimited isn't it so this shouldn't be a problem? Or am I wrong here? Any ideas on how to fix this please? INSERT INTO ODS.dbo.WEBSERVICELOG (WEBSERVICELOGID, DATETIME, DIRECTION, MESSAGEID, MESSAGETYPE, MESSAGE, RESULT, ERROR, STATUSID, OriginSystemID) SELECT WEBSERVICELOGID, DATETIME, DIRECTION, MESSAGEID, MESSAGETYPE, CAST(REPLACE(CAST(MESSAGE AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML) AS MESSAGE, RESULT, ERROR, STATUSID, OriginSystemID FROM PLATOCHHODS.dbo.WEBSERVICELOG WHERE WEBSERVICELOGID > @WEBSERVICELOGID --AND MESSAGETYPE NOT LIKE '%ROHLIG%' ORDER BY WEBSERVICELOGID
The problem with that error message is it doesn't tell you which field would be truncated - let alone the row(s) that are/would be affected... If you have any other (n)varchar(non-max) fields, check that the destination field is at least the same size as the source field.
Thanks for the reply. All sorted now. Yes you were correct, I was looking at the wrong field. The source had changed to nvarchar(max) and the destination was xml. Because this was the only field that changed I assumed (incorrectly) that it was the culprit when in fact it was another field. I changed that field and it's all good now. Cheers.
Check if the MESSAGE contains proper XML format. Maybe is a ' forget somewhere... See below example Declare @Message as nvarchar(MAX); set @Message = ' Tove Jani Reminder Don't forget me this weekend! ' select CAST(REPLACE(CAST(@Message AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML)
18 People are following this question.
