0

I have SQL server 2016 SP2 version installed with DB compatibility set to 130 . Running a SQL code to do an insert into table variable results in Msg 8152, Level 16, State 31, Line 8 (String or binary data would be truncated.) But when i run the same code in that same database with compatibility level changed to less than 130 ( 120 , 110,100 ) it works just fine without any issues. I've checked this from various SSMS versions ( ranging from 2014 to 2019) and they all produce the same result ( which rules out the SSMS factor, not an issue with SSMS ). I've turned on TRACEFLAG 460 and ran the SQL but it doesn't give the extra information on error details ( not sure if this is due to usage of table variable ) . I'm completely at loss not knowing how to fix this or if this could be a bug in SQL 2016 ?

Here is the code for it that runs and outputs "TestMatrix_all" value in SSMS when the DB compatibility is set to less than 130 but when i change the compatibility_level to 130 ( SQL 2016) it throws the error Msg 8152, Level 16, State 31, Line 8 (String or binary data would be truncated.). This seems very weird as the same code works fine in prior versions of sql ,Any help would be much appreciated .

Note : The user defined type "TablenameType" is defined as nvarchar(32) null

**TSQL code : **


DECLARE @table_class_committed TABLE (table_name TableNameType NOT NULL  )  
  
DECLARE @AllSuffix sysname  
SET @AllSuffix = N'_all'  
    
SELECT so.name,tc.class_name FROM table_class_committed tc  INNER JOIN sysobjects so on  
  so.name = tc.table_name + @AllSuffix  
WHERE  so.type = 'U' 

INSERT INTO @table_class_committed (table_name)  
SELECT so.name FROM table_class_committed tc  INNER JOIN sysobjects so on  
  so.name = tc.table_name + @AllSuffix  
WHERE  so.type = 'U'  AND NOT EXISTS (select 1  
  FROM  table_class_committed tc2  
  WHERE tc2.table_name = tc.table_name + @AllSuffix  
  AND tc2.class_name = tc.class_name)  

Select * from @table_class_committed

-- output expected - 'Testmatrix_all'

user21401535
  • 1
  • You could try to use `nvarchar(max)` instead of `tablenametype` for testing purposes and check if there is any unexpected extra output from your final select, that exceeds those 32 characters .. – derpirscher Mar 15 '23 at 15:30
  • Okay removed the tabelnametype and tried nvarchar(max) it works fine & gives o/p as expected -"Testmatrix_all" ,i also tried nvarchar(100) which works fine, just to see what point it breaks i tried nvarchar(46) not null which failed with msg 8152 error, but works for nvarchar(47) which means if i change the tablenametype definitin to nvarchar(47) it works in sql 2016 which is very wierd as the O/p (TestMatrix_all) string length is only 14 characters & takes 28 bytes(unicode) which is still less than defined narchar(32). it works pefectly fine in other SQL versions prior to 2016. – user21401535 Mar 16 '23 at 06:02

0 Answers0