String or binary data data would be truncated error message (1 Viewer)

[Mittle]

Hi

I am executing an

INSERT INTO

Column1
Column2

SELECT FROM

Column1
Column2

Statement with about 50 columns and getting the


String or binary data would be truncated error .SQL doesnt tell you which column is the issue


does anyone know the fastest way to find out the culprit column please.



Thanks

 

[Minty]

What are the Data types of all four columns involved - Target and Source?

 

[Mittle]

What are the Data types of all four columns involved - Target and Source?

there are over 60 columns involved NOT 4. thats the challenge .

 

[Minty]

Can you perform the insert query in SSMS it should tell you the column?

 

[Mittle]

Can you perform the insert query in SSMS it should tell you the column?

I'm running the query in SSMS . No it doesnt tell the column am afraid. the error Msg points to the 1st line of the insert query which doesnt tell me anything . am running SQL Server 2012. maybe this error tells you the column in later versions . ive googled this error and unfortunately I can only see long winded solutions .

especially if you have quite a few columns . it will take a bit of time

 

[Minty]

Okay - The error is basically telling you that a text string(99% of the time) will be truncated because the target field isn't long enough.
Therefore you can almost certainly ignore numeric fields (Unless you are possibly saving decimal numbers into an integer field), and concentrate on the text fields.

Run your select part of the insert query and add a Len(YourFIeld) to each column (easy in the text editor if you use block select mode) and see if any of the results are longer than you expect.

 

[Mittle]

Thanks . am just working on it but extremely painful process though .

 

[Minty]

If you use the block select mode of the SSMS editor or Notepad ++ or similar it becomes really simple to add the field again, and the Len( clause

 

[gemma-the-husky]

Maybe one string is >256 chars. (i think you get error 3265 off hand - field not large enough)
Maybe the total record length is >4000 chars. (might cause an issue)

you could write code to do it, and see where it errors

set rst =  currentdb..openrecordset(targettable)  'might need to declare a database object first. 
rst.addnew
rst!fieldname1 = whatever
rst!fieldname2 = whatever
...
rst.update

 

[Isaac]

Minty - I've never found a shortcut, to taking out one column one at a time until I find the culprit. Sadly.

 

[Minty]

There are possible solutions here https://dba.stackexchange.com/quest...ntify-the-column-that-is-causing-truncation-e

But I think the OP is on SQL 2012, in which case try a Select Into a New temp table and compare the field types.

 

[Isaac]

Thanks for posting. although frankly I question whether any of those are actually shortcuts. Just other manual ways to do them

 

[Mittle]

thanks All for your help and suggestion .

Isaac I think you are right . no shortcuts but I found a good link that helps generate the column lengths faster although I still have to compare the source and target manually with the bare eyes
https://blogs.lessthandot.com/index.php/datamgmt/datadesign/how-to-find-what-column/

 

[WayneRyan]

I’m on an iPad and can’t type, but ...

The newer version of the server will tell you the row/column that violates ... finally

In lieu of that you can:
1) select * into mytemp from your table where 1 = 0 (no data)
2) design mytemp, allowing all column to be null able.

Then you can “interval-halve” your select statement columns and find it pretty quickly.


HTH,
Wayne