October 30, 2017 at 1:32 pm
I'm having a weird string truncation issue that we've fixed, but it driving me nuts and was hoping somebody may have an answer on why this is happening.
I have a ColumnA varchar(120) that is getting inserted into a table with ColumnB varchar(8). The data in ColumnA is a UDF and so can stored data in various length. The data that I am specifically inserting is always under 8 characters. For some reason some data got insert with trailing spaces which is no big deal. I'll just use RTRIM to remove the trailing spaces so the data will be under 8 characters again, but the insert still fails with "String or binary data would be truncated".
I've rechecked the data and when I run the select statement with LEN and RTRIM, everything is under 8 characters. Using the exact same insert and select statement, if I add a DISTINCT, it works fine.
The ColumnB table does not have any indexes, constraints, identity. This has been working fine and the only that I can tell is the data has change from the source table and this is only the empty spaces.
Why does a SELECT DISTINCT fix a string truncation error?
October 31, 2017 at 12:58 pm
This is a data typing issue, created by the nature of the query. If you SELECT some expression, the resulting data type can be a complex determination, depending on the nature of the expression, as well as something known as datatype precedence. Generally, whenever multiple data types contribute to an expression, and there are both numeric and character data types involved, things can get messy due to the precedence of data types. I don't have the list in precedence order, but you can Google search it and likely find it. Order of things in the expression can also matter, particularly when using CASE. Post your query and someone will likely be able to show you the why, if what I've already mentioned and your Google searching doesn't get you what you need. Would love to hear the results of your search. Push comes to shove, using CAST or CONVERT to varchar(8) would likely solve this problem.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 31, 2017 at 1:34 pm
November 6, 2017 at 2:56 pm
Steve T - Tuesday, October 31, 2017 1:34 PMThis is interesting. I'm reviewing https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql. I will post back once I reviewed my data. Thank you.
Interestingly, that link fails with a Microsoft 404 "page not found" error.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 6, 2017 at 3:45 pm
sgmunson - Monday, November 6, 2017 2:56 PMSteve T - Tuesday, October 31, 2017 1:34 PMThis is interesting. I'm reviewing https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql. I will post back once I reviewed my data. Thank you.Interestingly, that link fails with a Microsoft 404 "page not found" error.
There is a period at the end of the link - take that out and you'll be good.
Sue
November 7, 2017 at 1:03 pm
Sue_H - Monday, November 6, 2017 3:45 PMsgmunson - Monday, November 6, 2017 2:56 PMSteve T - Tuesday, October 31, 2017 1:34 PMThis is interesting. I'm reviewing https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql. I will post back once I reviewed my data. Thank you.Interestingly, that link fails with a Microsoft 404 "page not found" error.
There is a period at the end of the link - take that out and you'll be good.
Sue
Cheese whiz... I should have caught that... my eyes are NOT what they used to be... Gettin' old ain't for sissies... 😉
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply