string or binary data would be truncated in table '******', column '******'. Truncated value: '******'

One of our applications report on error message 2628:

string or binary data would be truncated in table '', column ''. Truncated value: ''

instead of

String or binary data would be truncated in table 'mytable', column 'mycolumn'. Truncated value: 'myvalue'.

which I get with SSMS.

What setting do I need to change to get the full message?

I've tried to search for this, but all I get is that I need to set VERBOSE_TRUNCATION_WARNINGS to ON and compatibility_level to 150.
Which I've done ages ago.

The application has this error handler:

try
     {
         ES.isWorking = true;
         Worker worker = new Worker(new DBConnection(Settings.ConnectionString));
         worker.DoWork();
         ES.isWorking = false;
     }
 catch (Exception ex)
     {
         ES.isWorking = false;
         this.eventLog.WriteEntry("In OnTimer exception ! message: " + ex.Message.ToString());
         errorHandler.HandleException(ex, "In OnTimer exception !", ErrorSeverities.Error);
     }

The errorHandler looks like this

```
Public Function HandleException(ByVal incommingEx As System.Exception,
ByVal note As String,
ByVal errorSeverity As ErrorSeverities,
Optional ByVal sessionId As String = "",
Optional ByVal applicationSource As String = "",
Optional ByVal ignoreDB As Boolean = False,
Optional ByVal noMail As Boolean = False) As Integer
Dim stackTraceBuilder As New System.Text.StringBuilder
Dim targetSite As String = ""
Dim errorTime As DateTime = Now
Dim messageBuilder As New System.Text.StringBuilder
Dim innerEx As Exception = incommingEx.InnerException
Dim errorId As Integer = 0
Dim innerCount As Integer = 1
Dim loggedToDb As Boolean = False
Dim appSource As String = Me._applicationSource
If applicati

It looks like this table was defined with dynamic data masking, and the user that the application uses to access the database doesn't have permission to view masked data (which is good!).

This is why the behavior differs between the application and SSMS: I expect you're using a higher-privileged user when running the query from SSMS

Here's a demo:

CREATE TABLE dbo.Test
(
    Id int IDENTITY PRIMARY KEY,
    Filler varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL
);
GO

CREATE USER TestUser WITHOUT LOGIN;  
GRANT SELECT ON dbo.Test TO TestUser;  
GRANT INSERT ON dbo.Test TO TestUser;
GO

EXECUTE AS USER = 'TestUser';
INSERT dbo.Test 
    (Filler)
VALUES
    (REPLICATE(N'A', 101)); 
REVERT;

The result of which is:
Msg 2628, Level 16, State 1, Line 12
String or binary data would be truncated in table '', column ''. Truncated value: ''.


To get the full message in the application, you'll need to grant the user the UNMASK permission:

GRANT UNMASK TO TestUser;

Running the INSERT code again results in the full error text:
Msg 2628, Level 16, State 1, Line 14
String or binary data would be truncated in table 'tempdb.dbo.Test', column 'Filler'. Truncated value: 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'.


When using Azure SQL Database, Dynamic Data Masking can also be configured in the Azure Portal user interface. You may want to check there (although as far as I can tell, masks set up this way still update the sys.columns metadata):

I would also check if there are other databases that are part of the same logical SQL Server instance in Azure - maybe there are some kind of strange, cross-database queries going on.

StackExchange Database Administrators Q#273325, answer score: 13