Why is SQL truncating my data?

Question

I am trying to insert data into a field defined as nvarchar(1000), using SSMS (SQL Server 2008). It will not allow me to enter a literal 1000 characters long. I get the message:

Msg 8152, Level 16, State 4, Line 1 String or binary data would be truncated. The statement has been terminated.

If I shorten it to 850 characters, it does the insert, but using LEN() reveals that it has truncated the data to just 749 characters.

I really need my 1000 characters. How can I load this data?

Here is my script:

    INSERT INTO [OnsiteV3].[dbo].[SQLPendingEvent]
       ([KeyDeviceID]
       ,[UpdateTime]
       ,[EventClass]
       ,[EventCode]
       ,[EventType]
       ,[EventDateTime]
       ,[KeyPropertyID]
       ,[KeyEntityID]
       ,[EventText]
       ,[KeyUserID]
       ,[EventImage]
       ,[TaskType]
       ,[TaskSubType]
       ,[UniqueTaskID])
 VALUES
       ('SVS-IMPL-DARREN'
       ,GETDATE()
       ,'REPAIR'
       ,'NOTE'
       ,'ADD'
       ,GETDATE()
       ,1003443
       ,10812
       ,'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
       '
       ,'DARRENWORK'
       ,NULL
       ,NULL
       ,NULL
       ,'hsg00010812000P')

GO

Answer 1

Not sure if this is only due to formatting here on SO, but to me it looks like you are trying to insert about 80 characters of whitespace into the field as well, bringing your total character count up to 1080.

Also, LEN does not count trailing whitespace (yes, this is awful).

Answer 2

I really need my 1000 characters. How can I load this data?

One solution/workaround would be to remove the spaces, carriage returns and line feeds from the string:

replace(replace(replace( <your string> ', ' ', ''), char(10),''), char(13), '')

Applying this to your sample data (with blanks) makes it possible to insert into nvarchar(1000).

If you want to keep the string as is, with blanks andCR/LF, the obvious solution would be to alter the table to allow longer strings, maybe to nvarchar(max).

Sample SQL Fiddle