Data Type Truncation

John Oke 21 Reputation points
2021-06-18T19:04:00.533+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,691 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2021-06-18T21:10:39.43+00:00

    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.

  2. Seeya Xi-MSFT 16,436 Reputation points
    2021-06-21T05:57:55.047+00:00

    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.