SQL Server Error : String or binary data would be

问题:

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 ???

回答1:

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.

回答2:

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..

回答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.