Figure out which column would be truncated (date would be truncated)

Question

good evening! When trying to enter a record in Sqlserver through my application I am getting a truncated data message. The problem is that there are many columns that this screen fills and I have no idea which one is being truncated. Is there any way to know which column / table is causing the error?

ERROR Jdbcexceptionreporter.logExceptions(101) | String or Binary data would be truncated.

Answer 1

Of the many requests to the team responsible for MS SQL Server from the user community, this should be at the top of the list. Here is just one example:

Please fix the "String or Binary data would be truncated" message to Give the column name

If you encounter error in production environment it is quite complicated to detect which column is responsible for the error, because the data in many cases comes from the UI layer.

In development/testing environment, or if you are for example uploading information from a file, there are some alternatives, one of which is to access the information available in the sys.Columns table and compare, column by column, with the information you are trying to insert into the table.

Since you are running an Insert, I assume you have somehow mapped the information between the data source and the target table.

Start by creating a temporary table with the data you want to insert, it is important that the column names are the same as the table you want to insert.

In this example we will assume that you want to insert the information in the table Table Table and that it has the following structure:

CREATE TABLE TabelaXPTO
(
   Coluna1     NVARCHAR(250),
   Coluna2     NVARCHAR(250),
   Coluna3     NVARCHAR(250),
   Coluna4     NVARCHAR(250),
   Coluna5     NVARCHAR(250),
)


SELECT X1 AS Coluna1
      ,X2 AS Coluna2
      ,X3 AS Coluna3
      ,X4 AS Coluna4
      ,X5 AS Coluna5  
  INTO #TabelaTemporaria
  FROM Origem

Now, using the information in the sys.Columns table, you can compare the two tables (temporary table and destination table):

SELECT 
WITH Destino AS 
(
    SELECT C.column_id ,
           ColumnName= C.name ,
           C.max_length ,
           C.user_type_id ,
           C.precision ,
           C.scale ,
           DataTypeName = T.name
      FROM sys.columns C
     INNER JOIN sys.types T 
        ON T.user_type_id = C.user_type_id
     WHERE OBJECT_ID = OBJECT_ID('TabelaXPTO')
),
DadosOrigem AS 
(
    SELECT C.column_id ,
           ColumnName= C.name ,
           C.max_length ,
           C.user_type_id ,
           C.precision ,
           C.scale ,
           DataTypeName = T.name
      FROM TempDB.sys.columns C
      INNER JOIN tempdb.sys.types T 
         ON T.user_type_id=C.user_type_id
      INNER JOIN tempdb.sys.objects O 
         ON o.object_id=c.object_id
      WHERE O.name = 'TabelaTemporaria'
)
SELECT * 
  FROM Destino D
  INNER JOIN DadosOrigem O 
    ON D.ColumnName = O.ColumnName
WHERE ISNULL(D.max_length,0) < ISNULL(O.max_length,999)  --> O tamanho da coluna na tabela de destino é inferir ao tamanho dos dados que pretende inserir. 

It is possible that I have to make some adjustments to the code above, since I am not currently on a computer with SQL Server.

Answer 2

Open Sql Server Provider, take the query and open it in sql manager and try to run it in interactive. There it gives you the error line.