Data Type Truncation

Question

I am importing a CSV into a database table. I've got it loaded into its own table and I am using that table to feed 2 related tables so that the data is easier to see on a Power BI dashboard. When I attempt to copy some data from the original table I get the following error.

Msg 8152, Level 16, State 2, Procedure Update_Licence_Tables, Line 42 [Batch Start Line 2]
String or binary data would be truncated.

This is weird as the column that is causing the issue is nvarchar(255) and the destination column is also nvarchar(255). I am unsure why this error is occurring when the data types are identical.

Here is the INSERT INTO that is throwing the error. #WLD is the temp table the CSV info is saved to.

SET @active_id = (SELECT TOP(1) id FROM #WLD);
        INSERT INTO WELL_LICENCE_DATA (licence_number, licence_status, unique_well_identifier, deemed_asset_amount, deemed_liability_amount_total, pvs_value_applied, active_indicator)
        SELECT F1, F2, F3, CONVERT(decimal(18, 2), REPLACE(REPLACE(REPLACE(F4, '$', ''), ',', ''), ' ', '')), CONVERT(decimal(18, 2), REPLACE(REPLACE(REPLACE(F5, '$', ''), ',', ''), ' ', '')), F6, REPLACE(F7, ' ', '')
        FROM #WLD
        WHERE id = @active_id;

Is there a way to force ignore this warning or a known issue and fix?

Any insights are appreciated

Answer 1

What version of SQL Server are you one? Please post the output of "SELECT @@version".

If you are on a recent CU of SQL 2016 or SQL 2017, you can run

DBCC TRACEON(460)

on the same connection as you run your procedure. This will change the error message to include table and column name, as well as the truncated value that caused the error. This makes troubleshooting a little easier.

Answer 2

Hi @John Oke ,

The error indicates that the procedure is attempting to store something in the DBServerInfo table that is larger than the column allows. The two known reasons this can occur are:
There are the following methods you can try:
It may be that the length of the field definition in the data table structure is smaller than the length of the actual field content to be written, so it cannot be INSERT INTO.
You can try this to alter the length of the column:

ALTER TABLE #WLD   
ALTER COLUMN xxx NVARCHAR(xxx) NOT NULL  

The 'show advanced options' parameter is enabled in SQL Server and you are running the Enterprise edition of SQL Server.
You can verify if it is the problem by running the SQL query:

sp_configure  

If one of the parameter names returned in the result is "common criteria compliance enabled," then you can resolve the issue by executing the following query to hide the advanced options from the sp_configure results:

sp_configure 'show advanced options', 0;  
go  
reconfigure;  
go  

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.