SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


String or binary data would be truncated


String or binary data would be truncated

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)

Group: Administrators
Points: 620886 Visits: 21265
Comments posted to this topic are about the item String or binary data would be truncated

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Japie Botma
Japie Botma
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2487 Visits: 449
I am sorry Steve, but truncating data without the user knowing is an error. It is frustrating that the source and target schema don't match, but it is still an error. Either the source data is wrong (swapped columns by mistake, etc) or the target schema is wrong. Either way: It should tell me that. A simple substring in the source query can fix it, if you are willing to lose data.

5ilverFox
Consulting DBA / Developer
South Africa
Henrico Bekker
Henrico Bekker
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25730 Visits: 6050
Agree with you Japie, although I think there should be a function to override truncation on normal inserts.
If I understand Steve correct, we'd like something similar to SSIS, to choose what happens when truncation should/could occur, along the lines of:

*fictional command below*
INSERT WITH NoStopOnTruncate INTO myTable values('string that would be truncated if longer than len')


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
Dwayne Dibley
Dwayne Dibley
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2156 Visits: 3256
I'd be happy if an error message at least indicated the row number and column before throwing the rattle out of the pram.
aklt
aklt
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 178
Even just knowing which column, would be a huge help.
Matthew Joughin
Matthew Joughin
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 152
I think a table or column option would be a great solution for this - then its up to the developer to turn it on for the table or column if they want it to truncate varchar's and nvarchar's

if you don't have the time to do it right, when will you have the time to do it over ?
riix
riix
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 70
one freaky thing though .. this issue is posted on "feedback.AZURE.com ..." - as if SQL Server was a sub-entity of the Azure cloud world (I sure as sh*t hope not!) Anyhow, upvoted it also, thx Steve for keeping this issue alive.
jay-h
jay-h
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16184 Visits: 2811
If I remember correctly, MS Access has been providing this info years ago

...

-- FORTRAN manual for Xerox Computers --
Jason-
Jason-
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2221 Visits: 605
I agree with you Steve, this has to be addressed. I would suggest Microsoft handle character data differently altogether and treat varchar as a string the same way C# or Java or ObjectiveC etc do; at least as far as removing the length specification. That's a bigger thing to accomplish since it effects the storage engine and query optimization and indexing and on and on, but this is Microsoft, I've seen what they can do and it's impressive, this can be done too.

-
Steve Jones
Steve Jones
SSC Guru
SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)

Group: Administrators
Points: 620886 Visits: 21265
Japie Botma - Thursday, June 28, 2018 2:15 AM
I am sorry Steve, but truncating data without the user knowing is an error. It is frustrating that the source and target schema don't match, but it is still an error. Either the source data is wrong (swapped columns by mistake, etc) or the target schema is wrong. Either way: It should tell me that. A simple substring in the source query can fix it, if you are willing to lose data.


You've missed the entire point of the piece. This isn't about eliminating the error or silently truncating data. This is about ensuring the developer or DBA can find the source of the error easily.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search