question

JohnOke-3422 avatar image
0 Votes"
JohnOke-3422 asked SeeyaXi-msft commented

Data Type Truncation

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-generalsql-server-transact-sql
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Maybe show the relevant parts of Update_Licence_Tables.


0 Votes 0 ·

How did you determine the column that is causing the issue? What column?

0 Votes 0 ·

Trial and error. I removed columns to find one at a time to find it. From what I can see it is all of the columns where I am not transforming the data (F1, F2, F3, F6).

0 Votes 0 ·

Maybe different collation causes the issue?


0 Votes 0 ·

Hi @JohnOke-3422 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it dosn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered JohnOke-3422 commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Here is the response from @@version

 Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)   Mar 18 2018 09:11:49   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19042: ) 

DBCC TRACEON(460) didn't work so I'm assuming it's too old. I've asked the system admin in case it's a permissions thing but I don't think so.

Thanks for your help!

0 Votes 0 ·

That is SQL 2016 SP2 without any CU at all applied. You need at least CU6. The most recent CU for SQL 2016 SP2 is CU 17. I would encourage your admin to apply CU17, not only to give you access to the improved error message. SP2 is quite an old build by now.

0 Votes 0 ·

I'll ask about it thanks for your help!

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered SeeyaXi-msft commented

Hi @JohnOke-3422,

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.

· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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:

Can you clarify what you are alluding to here? I can't seen any connection between "show advanced options" and "String and binary data would be truncated". Do you have a reference?
0 Votes 0 ·

Before you quote a random post that Bing finds you, you should make a consideration whether it is applicable. The post you found appears to relate to a certain product IBM Security SiteProtector System. Even in that context, the reference to sp_configure appears dubious.

And any case, the post has absolutely no bearing to the problem that John Oke has run into.

0 Votes 0 ·

I noticed when using the SQL Server import wizard that it automatically gives nvarchar(255) to all columns. Is it possible the wizard was able to override this and input a longer string than would normally be possible?

0 Votes 0 ·

I noticed when using the SQL Server import wizard that it automatically gives nvarchar(255) to all columns. Is it possible the wizard was able to override this and input a longer string than would normally be possible?

If the column is nvarchar(255) it can fit 510 bytes and that's that.

But you can run sp_help on source and target tables to verify that the lengths are what you believe them to be.

0 Votes 0 ·

Figured as much just wanted to make sure there wasn't some butt****ery going on. Thanks!

0 Votes 0 ·
Show more comments