June 27, 2018 at 9:11 pm
Comments posted to this topic are about the item String or binary data would be truncated
June 28, 2018 at 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.
5ilverFox
Consulting DBA / Developer
South Africa
June 28, 2018 at 2:59 am
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
June 28, 2018 at 4:03 am
I'd be happy if an error message at least indicated the row number and column before throwing the rattle out of the pram.
June 28, 2018 at 4:45 am
Even just knowing which column, would be a huge help.
June 28, 2018 at 4:53 am
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 ?
June 28, 2018 at 6:19 am
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.
June 28, 2018 at 6:24 am
If I remember correctly, MS Access has been providing this info years ago
...
-- FORTRAN manual for Xerox Computers --
June 28, 2018 at 6:31 am
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.
-
June 28, 2018 at 9:45 am
Japie Botma - Thursday, June 28, 2018 2:15 AMI 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.
June 28, 2018 at 9:48 am
Henrico Bekker - Thursday, June 28, 2018 2:59 AMAgree 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')
I haven't necessarily proposed a fix, and I'd be nervous about truncating. If anything, I'd truncate but kick out the data as an error value. We just need some way to understand where and what the error is.
June 28, 2018 at 9:50 am
Matthew Joughin - Thursday, June 28, 2018 4:53 AMI 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
It's not always a fundamental coding issue. This could be a data issue for a single data load.
June 28, 2018 at 9:52 am
riix - Thursday, June 28, 2018 6:19 AMone 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.
This is because Azure is the subset of the MS area and that's where feedback is. There's not sqlserver.com site for feedback, so this is it.
June 28, 2018 at 9:57 am
riix - Thursday, June 28, 2018 6:19 AMone 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.
Actually, everything you see in the on-premise version of SQL Server is developed first in SQL Azure. If you want to the latest and greatest, that is where you will find it before you can use it on-premise.
June 28, 2018 at 10:00 am
Japie Botma - Thursday, June 28, 2018 2:15 AMI 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.
Hello Japie,
Steve is not complaining about the error, but the vagueness of the error. The error doesn't specify the location of the offending column(s). In a SQL statement with many columns, it becomes a real chore to find out which column(s) are causing the error.
Regards,
Omar
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply