String or binary data would be truncated. The statement has been terminated

Question

I have met some problem with the SQL server, this is the function I created:

ALTER FUNCTION [dbo].[testing1](@price int)
RETURNS @trackingItems1 TABLE (
   item       nvarchar  NULL,
   warehouse   nvarchar NULL,
   price int   NULL
) 
AS
BEGIN
   INSERT INTO @trackingItems1(item, warehouse, price)
   SELECT ta.item, ta.warehouse, ta.price 
   FROM   stock ta
   WHERE  ta.price >= @price; 

   RETURN;
END;

When I write a query to use that function like the following it getting the error

String or binary data would be truncated. The statement has been terminated

How can I fix this problem?

select * from testing1(2)

This is the way I create the table

CREATE TABLE stock(item       nvarchar(50) NULL,
                   warehouse   nvarchar(50) NULL,
                   price int NULL);

It simply means that you are inserting a value that is greater than the maximum allowed value. Ex, a column can only hold up to 5 characters, but you are inserting 10-character string. — John Woo, Feb 22 '13 at 07:59
@JW but in my table there is included the value 2 for the price, and the data type is actually same (eg:int) — user2098512, Feb 22 '13 at 08:01
Also way of calling function is select [dbo].testing1(2) from tablename — DevelopmentIsMyPassion, Feb 22 '13 at 08:02
@AshReva when i trying your suggestion, it get this error "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.testing1", or the name is ambiguous." but i can confirm that i have this method in my table-valued function already — user2098512, Feb 22 '13 at 08:05
how can i thank everyone in one time? haha, i solved it tq, the problem is in my functions i didn declare nvarchar(50) — user2098512, Feb 22 '13 at 08:15
I got this error in a different context, altering a column from 11 to 4 characters. The trick for me was to go through and update the data in the column first (in this case I wanted the RIGHT(column, 4)). Then it happily let me reduce the column size. — user420667, Feb 28 '18 at 18:00
Does this answer your question? [SQL Server String or binary data would be truncated](https://stackoverflow.com/questions/6388756/sql-server-string-or-binary-data-would-be-truncated) — wibeasley, May 26 '21 at 19:16

score 71 · Answer 1 · edited May 21 '20 at 20:56

When you define varchar etc without a length, the default is 1.

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

So, if you expect 400 bytes in the @trackingItems1 column from stock, use nvarchar(400).

Otherwise, you are trying to fit >1 character into nvarchar(1) = fail

As a comment, this is bad use of table value function too because it is "multi statement". It can be written like this and it will run better

ALTER FUNCTION [dbo].[testing1](@price int)
RETURNS
AS
   SELECT ta.item, ta.warehouse, ta.price 
   FROM   stock ta
   WHERE  ta.price >= @price;

Of course, you could just use a normal SELECT statement..

OakNinja · Answer 2 · 2013-02-22T08:10:48.867

41

The maximal length of the target column is shorter than the value you try to insert.

Rightclick the table in SQL manager and go to 'Design' to visualize your table structure and column definitions.

Edit:

Try to set a length on your nvarchar inserts thats the same or shorter than whats defined in your table.

edited Feb 22 '13 at 08:10

answered Feb 22 '13 at 08:05

OakNinja

2,196
15
19

for me the issue was with length of column, so changing from `nvarchar(50)` to `ntext` in sql sever, solves the issue, hope helps someone – Shaiju T Apr 12 '15 at 13:11
@stom you should always try to use the smallest possible length for your data. Doing so keeps your tables as small and fast as possible, and shows intent as well. If someone need to index or migrate your db in a few years, they will have to find out why the tables is designed the way they are. That said, if you want "unlimited" text in a field, ntext is a viable solution. – OakNinja Apr 12 '15 at 19:01
thank you for the tip given on performance, right now i am storing website links and some are long , so i choose ntext. – Shaiju T Apr 13 '15 at 05:23
1

@stom You could set it to nvarchar(2083) which is the max length for Internet Explorer. That way you maximize performance and compatibility. – OakNinja Apr 13 '15 at 08:42
ya `nvarchar(2083)` is best column type for a URL from [here](http://stackoverflow.com/questions/1159928/what-is-the-best-column-type-for-url), thank you. – Shaiju T Jul 28 '15 at 12:01

Hammad Khan · Answer 3 · 2017-07-14T07:54:08.403

22

In my case, I was getting this error because my table had

varchar(50)

but I was injecting 67 character long string, which resulted in thi error. Changing it to

varchar(255)

fixed the problem.

edited Jul 14 '17 at 07:54

answered Jul 20 '16 at 08:17

Hammad Khan

14,008
14
100
123

:) Changing to varchar(some_value) fixed my problem. – Naveen Kumar V Feb 12 '18 at 08:38

score 8 · Answer 4 · answered Feb 22 '13 at 08:09

Specify a size for the item and warehouse like in the [dbo].[testing1] FUNCTION

@trackingItems1 TABLE (
item       nvarchar(25)  NULL, -- 25 OR equal size of your item column
warehouse   nvarchar(25) NULL, -- same as above
price int   NULL

)

Since in MSSQL only saying only nvarchar is equal to nvarchar(1) hence the values of the column from the stock table are truncated

score 2 · Answer 5 · answered Jul 15 '20 at 08:05

SQL Server 2016 SP2 CU6 and SQL Server 2017 CU12 introduced trace flag 460 in order to return the details of truncation warnings. You can enable it at the query level or at the server level.

Query level

INSERT INTO dbo.TEST (ColumnTest)
VALUES (‘Test truncation warnings’)
OPTION (QUERYTRACEON 460);
GO

Server Level

DBCC TRACEON(460, -1);
GO

From SQL Server 2019 you can enable it at database level:

ALTER DATABASE SCOPED CONFIGURATION 
SET VERBOSE_TRUNCATION_WARNINGS = ON;

The old output message is:

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

The new output message is:

Msg 2628, Level 16, State 1, Line 30
String or binary data would be truncated in table 'DbTest.dbo.TEST', column 'ColumnTest'. Truncated value: ‘Test truncation warnings‘'.

In a future SQL Server 2019 release, message 2628 will replace message 8152 by default.

String or binary data would be truncated. The statement has been terminated

5 Answers5

Linked