17

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

Improve this question
4
  • 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. :-) Commented Jun 1, 2012 at 2:24
  • 2
    Just a suggestion : If you are not sure about the length of the values in Transaction_status make it to varchar(max) Commented Jun 1, 2012 at 2: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. Commented Dec 9, 2013 at 21:59
  • This question is similar to: SQL Server String or binary data would be truncated. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. Commented Jul 29, 2024 at 20:45

4 Answers 4

Reset to default
68

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Why does not MSSQL list the columns that are too short? Is it possible to obtain that list?
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..

Improve this answer

Comments

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.

Improve this answer

1 Comment

With SQL Server 2019 the error is more descriptive with the actual real line number and the data that would be truncated.
0

Solution 1:- Alter Column size: ALTER TABLE [dbo].[audit_log] ALTER COLUMN transaction_status varchar(20) GO

Solution 2:- SET ANSI_WARNINGS OFF

Solution 3:- if don't want to modify the length then ok to trunct value then use LEFT('text',10)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.