Thank you, Microsoft SQL Server Development team. I really am happy with your work.
I was going through emails and noticed the someone had posted a surprise in the SQL Server 2019 CTP. The post was slightly cryptic, but since I saw the dreaded string-or-binary-data-truncated message, I was intrigued. Digging further, with some help from fellow MVPs, I found that one of the most voted on feature requests was actually implemented. I blogged about this briefly, but in case you wonder, this is the message in SQL Server 2017 and below:
Msg 8152, Level 16, State 14, Line 8
String or binary data would be truncated.
In SQL Server 2019, I get this from the same repro:
Msg 2628, Level 16, State 1, Line 8
String or binary data would be truncated in table 'Sandbox.dbo.Customer', column 'CustomerName'. Truncated value: 'Is this th'.
You do need to enable trace flag 460, but this works and should help you find that problematic data. I’m really glad that Microsoft has implemented the first stages of a fix, and I do appreciate their efforts here. This is a nice step forward.
I do hope that future versions, or even continued development on SQL Server 2019 will enhance this. Right now I just get the first occurrence, which is good, but I’d love it if we could get a result set back, or maybe an indication of more problems. I know I’m asking for more here, but there is room for improvement.
If this is all we get for SQL Server 2019, that’s fine. I think this is going to be very helpful for those problematic imports, and at least help us find bad data more quickly in those poorly structured input strings.