Verbose truncation warnings setting not working

1

Does anyone know how to use this setting in SQL 2019?

ALTER DATABASE SCOPED CONFIGURATION
SET VERBOSE_TRUNCATION_WARNINGS = ON

I ran that and it says it ran but when I test it by creating a table and inserting data longer than it is set to accept, I still don't get verbose messages. same useless error that the data would be truncated.

2

Asking most obvious question first: Did you set this on all of the databases you're working with? If you're using temp tables, then probably want to run it on tempdb too, just in case it got turned off.

USE myDatabase;  -- do this for each DB you want to generate the new message
ALTER DATABASE SCOPED CONFIGURATION
SET VERBOSE_TRUNCATION_WARNINGS = ON;
3

What is your database compatibility level ?

VERBOSE_TRUNCATION_WARNINGS

For database compatibility level 140 or lower, error message 2628 remains an opt-in error message that requires trace flag 460 to be enabled, and this database scoped configuration has no effect.

4

I usually select the db I'm running it on in the dropdown first when creating a new query. but i did run the user db command just to be sure. but still no change to the syntax.

5

bingo! I changed it to the one for 2019 and now it works! Thanks!

6

question. if I am running in 2016 or higher and below 2019, and I run something like this:

INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar)
VALUES ('Baby', '2006 Subaru Impreza WRX GD')
OPTION (QUERYTRACEON 460);
GO

should this return verbose messaging or how do I grab the trace info from there? Is there some sort of log file this writes to?

7

use TRY...CATCH and log to a table in the ERROR_MESSAGE() in the CATCH block

8

yikes! never heard of this and it sounds very confusing to me. You able to provide an example of what this might look like using my example? Like how would I input this using my query?

9

first you create a table to log the error messages

CREATE TABLE dbo.TruncateError(message nvarchar(4000));

then wrap your query in TRY block and in CATCH block insert the ERROR_MESSAGE() in the table. There are other error information available.


BEGIN TRY
  INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar)
  VALUES ('Baby', '2007 Subaru Impreza WRX GD');
END TRY
BEGIN CATCH
    INSERT INTO dbo.TruncateError (message)
    VALUES (ERROR_MESSAGE())
END CATCH;

db<>fiddle demo

10

I would set the flag globally, should be more consistent:

DBCC TRACEON( 460, -1 );