Clever Geek Handbook

Why is SQL truncating my data?

I am trying to insert data into a field defined as nvarchar (1000) using SSMS (SQL Server 2008). This will prevent me from literally typing 1000 characters. I get the message:

Msg 8152, Level 16, State 4, Line 1 String or binary data will be truncated. The application has been completed.

If I truncate it to 850 characters, it inserts, but using LEN () finds that it has truncated the data to 749 characters.

I really need my 1000 characters. How do I download this data?

Here's 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

+3


source to share


3 answers


Not sure if this has to do only with the formatting here on SO, but to me it looks like you are trying to insert about 80 space characters into a field, causing your total character count to be up to 1080.



Also, it LEN

does not account for trailing spaces (yes, that's terrible).

+4


source


I really need my 1000 characters. How do I download this data?

One solution / workaround would be to remove spaces, carriage returns and lines from a string:

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


Applying this to your sample data (with spaces) allows you to paste in nvarchar(1000)

.

If you want to keep the string as is, with spaces and CR/LF

, the obvious solution would be to modify the table to use longer strings, perhaps nvarchar(max)

.

Sample SQL script

+1


source


I am using a user defined function for this purpose.

http://blog.sqlauthority.com/2008/10/10/sql-server-2008-enhenced-trim-function-remove-trailing-spaces-leading-spaces-white-space-tabs-carriage-returns-line- feeds /

0


source






More articles:


All Articles