Kenneth Fisher explains a nasty-looking error to us:
So, are you seeing this error?
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
If you read the error it might freak you out a bit. The key words memory and corrupt can be a bit .. concerning. Fortunately in this case they are also rather misleading.
Click through to understand what’s going on and how you can fix the problem if you see this error.
Andy Leonard troubleshoots an odd error in SSIS:
Exception deserializing the package “Operation is not valid due to the current state of the object.”. (Microsoft.DataTransformationServices.VsIntegration)
As a professional consultant who has been blogging about SSIS for 12 years and authored and co-authored a dozen books related to Microsoft data technologies, my first response was:
“Whut?!”
That is a reasonable first response. Fortunately, Andy also had a second response which was more helpful in finding the root cause.
Jack Vamvas takes us through a reason why you might get error 3041:
An error message has started appearing in the SQL Server Error Logs during a nightly full backup.
Could not clear ‘DIFFERENTIAL’ bitmap in database ‘Database1’ because of error 9002. As a result, the differential or bulk-logged bitmap overstates the amount of change that will occur with the next differential or log backup. This discrepancy might slow down later differential or log backup operations and cause the backup sets to be larger than necessary. Typically, the cause of this error is insufficient resources. Investigate the failure and resolve the cause. If the error occurred on a data backup, consider taking a data backup to create a new base for future differential backups.
Click through for the root cause and solution.
Marco Russo takes us through some of the more common Power BI error messages around writing DAX:
The message should help the author fix the code, but sometimes the text suggests a possible action without describing the underlying issue. The goal of this article is to explain the more common DAX error messages by providing a more detailed explanation and by including links to additional material. If some terms are not clear, look at the linked articles or consider some free self-paced training such as Introducing DAX.
Click through for several examples.
So how does this relate to error tables? Like most well-documented APIs, the U.S. Census Bureau API has a page devoted to listing and describing all the possible response codes that can be returned by their service. I take this information and build an internal table within the query that defines and describes these response codes in my own words. I’m now able to throw custom messages that make the difference between a
400response code and a404response code more obvious.For example, in the code below, I use the
Error.Recordfunction to create individual records that allow me to catch these unsuccessful requests and throw my own custom error messages to the user. I then create an extra field in each record called ‘Status’, which maps each HTTP response code returned by the API to a corresponding error message of my choosing:
There’s a bit of work, but the end result is a fairly simple explanation for end users.
Jack Vamvas shows us how we can capture errors from SQLCMD:
I’m executing code using SQLCMD from a batch file . The code points to a sql file and there is also an output file.
SQLCMD -E -S MYSERVER\INST1 -i “setup_job_entry.sql” -o “setup_job_entry.log”
But I noticed that if the actual SQLCMD returns an error , for example , if I’m connecting to an server which doesn’t exist this error message will appear in the output file – but there will not be an ERROR number , which would allow me to trap and return an appropriate message
There is a way and Jack shows us how.
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.
Kevin Feasel
2019-02-20
Error Handling, SQL Server Management Studio
No Comment