14

My table :

log_id                 bigint
old_value                  xml
new_value                  xml
module                 varchar(50)
reference_id           bigint
[transaction]          varchar(100)
transaction_status         varchar(10)
stack_trace                ntext
modified_on                datetime
modified_by                bigint

Insert Query :

INSERT INTO [dbo].[audit_log]
           ([old_value],[new_value],[module],[reference_id],[transaction]
           ,[transaction_status],[stack_trace],[modified_on],[modified_by])
     VALUES
            ('asdf','asdf','Subscriber',4,'_transaction',
            '_transaction_status','_stack_trace',getdate(),555)

Error : 

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

Why is that ???

Anuya
  • 7,428
  • 45
  • 127
  • 216
  • 2
    +1 for providing sufficient information to understand your question. I almost took it back for not reading the error message and looking at your SQL to catch it yourself, though. :-) – Ken White Jun 01 '12 at 02:24
  • 2
    Just a suggestion : If you are not sure about the length of the values in Transaction_status make it to varchar(max) – praveen Jun 01 '12 at 02:39
  • 1
    @praveen wow, that is not a very good suggestion at all. You don't know that they don't know how long the column will be, and the first response is to find out. Should I buy a 30-bedroom house in case I have 30 kids? Of course not. – Aaron Bertrand Dec 09 '13 at 21:59

3 Answers3

56

You're trying to write more data than a specific column can store. Check the sizes of the data you're trying to insert against the sizes of each of the fields.

In this case transaction_status is a varchar(10) and you're trying to store 19 characters to it.

Tremmors
  • 2,778
  • 14
  • 13
3

this type of error generally occurs when you have to put characters or values more than that you have specified in Database table like in this case: you specify transaction_status varchar(10) but you actually trying to store
_transaction_status which contain 19 characters. that's why you faced this type of error in this code..

jaideep
  • 1,541
  • 16
  • 19
3

This error is usually encountered when inserting a record in a table where one of the columns is a VARCHAR or CHAR data type and the length of the value being inserted is longer than the length of the column.

I am not satisfied how Microsoft decided to inform with this "dry" response message, without any point of where to look for the answer.

zhrist
  • 799
  • 5
  • 21
  • With SQL Server 2019 the error is more descriptive with the actual real line number and the data that would be truncated. – Piccolo Jan 18 '20 at 16:13