February 25, 2016 at 9:04 pm
Comments posted to this topic are about the item CAST tinyint to nvarchar
February 26, 2016 at 12:47 am
This was removed by the editor as SPAM
February 26, 2016 at 2:10 am
According to CAST and CONVERT, an E should be returned, but that is not the case.
Below the table where it states that "E" will be returned there is a footnote that states:
E = Error returned because result length is too short to display.
So surely when it states it returns "E" and you get an arithmetic overflow error the behaviour is exactly as described?
February 26, 2016 at 5:09 am
gareth.davison (2/26/2016)
According to CAST and CONVERT, an E should be returned, but that is not the case.
Below the table where it states that "E" will be returned there is a footnote that states:
E = Error returned because result length is too short to display.
So surely when it states it returns "E" and you get an arithmetic overflow error the behaviour is exactly as described?
The function doesn't return an "E" at all. It doesn't return anything. It throws an error.
Thanks, Steve. BOL being wrong is a great way to end the week.
February 26, 2016 at 6:51 am
Ed Wagner (2/26/2016)
gareth.davison (2/26/2016)
According to CAST and CONVERT, an E should be returned, but that is not the case.
Below the table where it states that "E" will be returned there is a footnote that states:
E = Error returned because result length is too short to display.
So surely when it states it returns "E" and you get an arithmetic overflow error the behaviour is exactly as described?
The function doesn't return an "E" at all. It doesn't return anything. It throws an error.
Thanks, Steve. BOL being wrong is a great way to end the week.
BOL is NOT wrong. If you look below the chart in question, it explains that "E" is a place holder in the chart meaning that some error will be returned.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 26, 2016 at 8:46 am
BOL does note this will return an error, but the "E" in the chart is very misleading. I'd say this is one of the poorer documentation efforts to not just put the "rror" in the chart.
February 26, 2016 at 8:59 am
Steve Jones - SSC Editor (2/26/2016)
BOL does note this will return an error, but the "E" in the chart is very misleading. I'd say this is one of the poorer documentation efforts to not just put the "rror" in the chart.
Agreed. The article could definitely be improved.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 26, 2016 at 9:35 am
Ed Wagner (2/26/2016)
gareth.davison (2/26/2016)
According to CAST and CONVERT, an E should be returned, but that is not the case.
Below the table where it states that "E" will be returned there is a footnote that states:
E = Error returned because result length is too short to display.
So surely when it states it returns "E" and you get an arithmetic overflow error the behaviour is exactly as described?
The function doesn't return an "E" at all. It doesn't return anything. It throws an error.
Thanks, Steve. BOL being wrong is a great way to end the week.
What? BOL wrong? Again? :hehe::hehe:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 26, 2016 at 10:21 am
Orlando Colamatteo (2/26/2016)
Steve Jones - SSC Editor (2/26/2016)
BOL does note this will return an error, but the "E" in the chart is very misleading. I'd say this is one of the poorer documentation efforts to not just put the "rror" in the chart.Agreed. The article could definitely be improved.
Strong second on that here.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 26, 2016 at 10:55 am
Steve Jones - SSC Editor (2/26/2016)
BOL does note this will return an error, but the "E" in the chart is very misleading. I'd say this is one of the poorer documentation efforts to not just put the "rror" in the chart.
It could be improved, but even as it stands I don't think a claim that it's misleading is reasonable. Before the chart the possible results and listed; E isn't one of them, so it's already clear that E in the chart doesn't mean the character E; and the after the chart it's clearly stated that"E" in the chart means the result is an error, just in case anyone was half asleep and didn't notice that a single character E was not one of the possible outcomes listed above the chart. Yes, it would be nice to have "Error" in the chart instead of "E" but that's a lot less important than fixing some of the many places where BOL is just plain absolutely wrong.
Of course one might want to ask what clown determined that conversion to nvarchar should throw an error in cases where conversion to varchar returns '*'. It really seems crazy. But I suspect that the answer would probably be that an ANSI or ISO working group did it for their "national character varying" type (or maybe for a WVARCHAR or "wide character varying" type in the bad old days) and Microsoft was effectively stuck with it for compatability.
Tom
February 26, 2016 at 11:37 am
Yeah, I tend to go with a very literal interpretation. As has been pointed out, a simple Error in the table would have helped. I can see how it refers to a note, but it would have still been simpler to finish the word in the table.
February 26, 2016 at 12:13 pm
I'll disgree with you, Tom. The reason is this, and this is what I missed at first.
The top of the chart has an * for the first few entries. This often indicates a footnote, which is fine. I looked down and saw an * below the chart. Then I saw the E for the problem I experienced, and convention would have the E =, below the *, not as a part of that footnote. In glancing at this, I thought E was being returned.
In my experience, with the convention in many publications, we should see.
</table>
* = the value is too long.
E = an error
Though rather than E in the chart, since space is not an issue, it should just say error, lower case.
February 26, 2016 at 1:01 pm
Steve Jones - SSC Editor (2/26/2016)
I'll disgree with you, Tom. The reason is this, and this is what I missed at first.The top of the chart has an * for the first few entries. This often indicates a footnote, which is fine. I looked down and saw an * below the chart. Then I saw the E for the problem I experienced, and convention would have the E =, below the *, not as a part of that footnote. In glancing at this, I thought E was being returned.
In my experience, with the convention in many publications, we should see.
</table>
* = the value is too long.
E = an error
Though rather than E in the chart, since space is not an issue, it should just say error, lower case.
I initially thought the BOL entry was fairly clear, but I'm now thinking it has some annoying features.
They use E in the table, and then below the table say that E= an error is returned. That's fair, and pretty clearly indicates that it's not a literal 'E' that is returned. However, they do the same thing for *; they say *= the value is too long, but this time '*' is also the literal value returned, which means E= and *= are treated in opposite ways.
For E=, it's what the table value represents that is returned, while for *= it is the table value that is returned, not what it represents.
If you already know that one returns a '*' and one results in an error it's easy to gloss over, but it is certainly is not a stellar example of documentation 🙂
February 26, 2016 at 1:14 pm
Jacob Wilkins (2/26/2016)
Steve Jones - SSC Editor (2/26/2016)
I'll disgree with you, Tom. The reason is this, and this is what I missed at first.The top of the chart has an * for the first few entries. This often indicates a footnote, which is fine. I looked down and saw an * below the chart. Then I saw the E for the problem I experienced, and convention would have the E =, below the *, not as a part of that footnote. In glancing at this, I thought E was being returned.
In my experience, with the convention in many publications, we should see.
</table>
* = the value is too long.
E = an error
Though rather than E in the chart, since space is not an issue, it should just say error, lower case.
I initially thought the BOL entry was fairly clear, but I'm now thinking it has some annoying features.
They use E in the table, and then below the table say that E= an error is returned. That's fair, and pretty clearly indicates that it's not a literal 'E' that is returned. However, they do the same thing for *; they say *= the value is too long, but this time '*' is also the literal value returned, which means E= and *= are treated in opposite ways.
For E=, it's what the table value represents that is returned, while for *= it is the table value that is returned, not what it represents.
If you already know that one returns a '*' and one results in an error it's easy to gloss over, but it is certainly is not a stellar example of documentation 🙂
I don't believe it's completely useless. If nothing else, it's a stellar example of what not to do. 😛
February 28, 2016 at 7:55 am
Ed Wagner (2/26/2016)
Yeah, I tend to go with a very literal interpretation. As has been pointed out, a simple Error in the table would have helped. I can see how it refers to a note, but it would have still been simpler to finish the word in the table.
Heh... I wonder if that would lead some people who don't read all the documentation for a given thing to think that it would return the word "Error". 😛
I do have to admit that since it returns an "*" in one case, one would easily assume an "E" would be returned in the others. Even the legend under the chart could confuse some that might just take the word of the chart instead of doing an actual test for clarification (especially if it's the first time they were to use such a thing).
What's really aggravating to me is the fact that there are two different possibilities that can be returned during a "too short" conversion. It would be nice if they either returned a "*" consistently or an error consistently. I'd personally prefer the non-error-prone "*" because that would make it really easy to quickly isolate the offending value. Of course, I suppose you could use TRY_CONVERT for such a thing but it seems more complex than just looking for an "*".
Even better would be an "*" returned along with a non-fatal advisory message kind of like you get when you have NULLs in a column that you're trying to aggregate.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply