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

User · Answer

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  gt    price    Of course  you could just use a normal SELECT statement

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server