Reza Rad has a lengthy post covering how you can track errors in Power Query:
To build a robust BI system, you need to cater for errors and handle errors carefully. If you build a reporting solution that the refresh of that fails everytime an error occurs, it is not a robust system. Errors can happen by many reasons, In this post, I’ll show you a way to catch potential errors in Power Query and how to build an exception report page to visualize the error rows for further investigation. The method that you learn here, will save your model from failing at the time of refresh. Means you get the dataset updated, and you can catch any rows caused the error in an exception report page. To learn more about Power BI, read Power BI book from Rookie to Rock Star.
There’s a lot of work, but also a lot of value in doing that work.
Denis Gobo shares one of the biggest improvements in SQL Server 2019 CTP 2.0:
And there we go, you get the table name, the column name as well as the value, notice that the message id changed from 8152 to 2628 now
Msg 2628, Level 16, State 1, Line 20
String or binary data would be truncated in table ‘truncatetest.dbo.TruncateMe’, column ‘somevalue’. Truncated value: ‘33333’.
The statement has been terminated.So it looks it only returns the first value that generates the error, let’s change the first value to fit into the column and execute the insert statement again
It’s not perfect, as it only shows one column from the first failed row, but that is still a lot more information than we had before and I’m happy that this is making into the product.
Monica Rathbun walks us through a replication error:
Ever seen the below error? Until this week I hadn’t. So, I figured I’d take a little time and introduce it to those that had not.
Error Description: Length of LOB data (65754) to be replicated exceeds configured maximum 65536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit
We ran into an issue with a customer this week, this error was flooding the error log. After a little digging I found it had to do with transactional replication (also applies to Change Data Capture) they had setup which included LOB data.
Read on to see what you can do to resolve this error. Also, check out the comments and be glad you’re not in that boat…unless you are, in which case…
Thomas Rushton reproduces an error state in SQL Server:
A question asked on one of the forums today wasn’t easily answerable by Googling. Summary of the question “I have error 18456 State 73 – why?”
Google seemed remarkably quiet on the subject of that particular state code. Even Aaron Bertrand’s list of causes of state codes for SQL Server error 18456 missed this one.
However, some searching did find a link to what appears to be some in-depth VMWare VSAN training documentation that includes that error in some logging information, which made me wonder if it was related to the error 18456 state 38 that followed.
Read on for more details.
Tim Wilde shares an oft-forgotten truth:
How often have you found yourself contemplating some hair-brained regex scheme in order to extract an inkling of value from a string and wishing the data had just arrived in a well-structured package without all the textual fluff?
So why do we insist on writing prose in our logs? Take “Exception while processing order 1234 for customer abc123” for example. There are at least four important pieces of information drowning in that one sentence alone:
- An exception was raised!
- During order processing
- Order number 1234
- Customer abc123
Being an exception log message, it’s more than likely followed by a stack trace, too. And stack traces certainly don’t conform to carefully crafted log layout patterns.
Logging is something we tend to forget about and slap in at the last minute. We also think about it from the viewpoint of a developer looking at a single error message. Those are both mistakes that lead to a huge amount of extra work later.
Manish Mishra gives a few examples of how to handle errors in Scala:
Try[T] is another construct to capture the success or a failure scenarios. It returns a value in both cases. Put any expression in Try and it will return Success[T] if the expression is successfully evaluated and will return Failure[T] in the other case meaning you are allowed to return the exception as a value. However with one restriction that it in case of failures it will only return Throwable types:
def validateZipCode(zipCode:String): Try[Int] = Try(zipCode.toInt)But Throwing an exception doesn’t make much sense here since it is not much of a calculation. Although we can take this example to understand the use case. If the given string is not a number, it will be a failure. The value from the Try can be extracted in same as Option. It can be matched
As you write more complicated Spark operations, handling errors becomes critical.
Jason Brimhall has an interesting story around a common error message:
Now is the time to start pulling out your hair. There is no syntax error in the query. Go ahead and look it over 10-15 times. I know I did.
Ok, if there is no syntax error, then what could possibly be the real problem? Is the database corrupt? Maybe a system table is corrupt? Grasping at straws here, but could it possibly even be some sort of royally screwed up permissions.
Everything seems to be checking out properly. There is no corruption whatsoever. Laptop is soon to be launched at this point right? Ok, maybe not launched because this is a simple query. But, had this been a production related query that was rather intense and complicated, there really may be something getting launched as the frustration mounts.
Click through for the answer. Sometimes the error message is technically correct but utterly confounding.
It starts slowly. Maybe your home-grown centralized logging cluster becomes more difficult to operate, demanding unholy amounts of engineer time every week. Maybe engineers start to find that making a query about production is a “go get a coffee and come back later” activity. Or maybe monitoring vendors offer you a quote that elicits a response ranging anywhere from curses under the breath to blood-curdling screams of terror.
The multi-headed beast we know as Scale has reared its ugly visage.
As some of you may have already guessed from the title, I’m going to discuss one way to solve this problem, and why it might not be as bad as you might think.
Take some of your precious information and throw it in the garbage. In lots of cases, you can just drop those writes on the floor as long as your observability stack is equipped to handle it.
In other words, sample.
Read on for a couple of methods. One thing I’ve taken a fancy to is collecting the first N of a particular type of message and keeping track of how often that message appears. If you get the same error for every row in a file, then you might really only need to see that one time and the number of times it happened. Or maybe you want to see a few of them to ensure that they’re really the same error and not two separate errors which are getting reported together due to insufficient error separation.
Kenneth Fisher shares a story of hunting down the cause of an error message:
This time we had a vendor reporting the following error:
Msg 207, Level 16, State 1, Line 7
Invalid column name ‘Name’Now the vendor was certain this was a permissions issue. It worked fine on their systems, it worked fine on some of ours. So why didn’t it always work? Well, the easy answer is permissions! Particularly since we had denied them db_owner just recently.
So why do I sound so dismissive about permissions as a possibility? I mean it COULD be permissions. It certainly is possible. But first of all, we don’t use column level permissions very often (no one uses them all that often from what I can tell) and secondly it worked on several other systems where they had exactly the same permissions as this system.
Ok, so what is the problem? You guessed it! (I really have to stop asking for guesses after I’ve put the answer in the title.)
I went out of my way not to give the answer here, so you’ll have to look at Kenneth’s title. And then read the whole thing.
If you build an SSDT project you can get an error which says:
“SQL71502: Function: [XXX].[XXX] has an unresolved reference to object [XXX].[XXX].”
If the code that is failing is trying to use something in the “sys” schema or the “INFORMATION_SCHEMA” schema then you need to add a database reference to the master dacpac:
Click through for the answer and a comment-by-comment walkthrough from Ed.
Kevin Feasel
2018-11-27
Error Handling, ETL, Power BI
No Comment