A Fine Slice Of SQL Server
Tom Collins wants to know what’s going wrong:
We have an ETL job failing every night . The ETL job is using SQL Server Stored Procedures , so we’re looking to be trace the errors reported in SQL Server, to give some clues on how to fix the problem?
Do you have an approach to monitor and save SQL Server error messages ?
This is an extremely useful way of catching problems in code, as well as people goofing up ad hoc queries.
Leave a CommentMeagan Longoria talks trade-offs:
Low-code solutions often accelerate development and make tasks accessible to people who can’t or don’t want to write their own code. But it’s important to remember that it’s a trade-off. You are often trading decreased development and maintenance time for limited configuration options and minimal monitoring capabilities. Low-code solutions are great…until they aren’t.
This kind of thing can pop up in code-based libraries or APIs as well, though there’s typically another viable solution. With low-code solutions, veering from the happy path often lands you in a world of frustration.
Leave a CommentRichard Swinbank runs into a problem:
TL;DR:
- Data Factory implementations in Fabric, Azure Synapse Analytics or Azure Data Factory evaluate pipeline expressions separately for logging and execution.
- Log information reported from activities using non-deterministic functions may be unreliable.
Richard does give us a nice tl;dr, but still read the whole thing.
Comments closedHaha, just kidding. Erik’s code never has mistakes, but he does have to deal with other people who have foolishly erred. This video is a good one. It covers a broad base of error handling in SQL Server, including improper parameter inputs, try-catch blocks, automatic retries, handling lock timeouts, and a lot more.
Comments closedNivritti Suste handles an error:
In our organization, most data is stored on-premises with a limited set of less critical data is in the cloud. We use Azure to benefit from the cloud environment and Azure Data Factory (ADF) to move data.
With ADF, there are many components that need to integrate within the environment. The data on our on-premises servers needs to be shifted to the cloud periodically and we use Self-hosted Integration Runtime.
Our developers complain an ADF pipeline is failing with error: ‘The Self-hosted Integration Runtime is offline…’ What does this mean?
Click through for the answer.
Comments closedAndy Brownsword wrote code with an error in it:
When constructing or investigating a SSIS package we can run into a variety of issues. To help resolve there are various techniques which can be used to troubleshoot the package.
Whilst we have the Progress tab for the package to tell us what’s happened during execution, it’s usually more effective to debug packages in flight. Below we’ll look at a few ways we can achieve this:
Never having written code that doesn’t work perfectly the first time, this post is, of course, merely academic for all of us perfect developers. But for those people who may have let a little something sneak in or have to deal with the errors that others have seeded into our beautiful programs, Andy provides three separate techniques for troubleshooting packages in flight.
Comments closedJim Evans gives us a reminder:
When troubleshooting SQL Agent jobs, often the Job history output is truncated or poorly formatted, making it hard to read. This is especially true when calling SSIS Packages, running jobs like DBCC CheckDB or when running T-SQL code that returns a lot of output. Are there options to get more readable Job output to aid in troubleshooting?
There are a few settings here that we can use to make troubleshooting SQL Agent jobs a little bit easier. In addition to these, it’s also a good idea to retain more history for longer, especially if you’re not in a position to track those job outputs each day.
Comments closedBen Johnston runs into an error:
I’ve been working in the Azure space for the last few years and less with full server installations. Azure leaves file locations out of my control, but I recently had a client upgrading an on-prem server to SQL 2022.It was a fresh installation on a new VM. I installed SQL, restored all the data, then I moved files to their optimal locations. This was a fresh installation of SQL Server and I wanted to move all of the data files, log files and tempdb files to their own drives. I also wanted to move the system databases. The documentation cautions that cumulative updates (CU) could fail if a specific registry key isn’t updated after moving the master files, so I wanted to test this before I spent too much time configuring the server.
An excerpt of that warning follows here.
Click through for the warning, as well as how to fix the problem.
Comments closedUsing the “REPLACENULL” functionality frequently in the “Derived Column” component, the “Conditional Split” component, and other places in SSIS where formulas can be applied is common.
However, I recently encountered an issue with the “DT_DBTIMESTAMP2” data type.
The following formula produced an error:
REPLACENULL(TestDt, (DT_DBTIMESTAMP2,7)”1900-01-01 00:00:00.0000000″)
Error: 0xC020902A at Test Transformation, Derived Column [2]: The “Derived Column” failed because truncation occurred, and the truncation row disposition on “Derived Column.Outputs[Derived Column Output].Columns[TestDt]” specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Read on for an explanation and two alternatives.
Comments closedAlexis Tekin and Jeremy Ber handle an error:
Data streaming applications continuously process incoming data, much like a never-ending query against a database. Unlike traditional database queries where you request data one time and receive a single response, streaming data applications constantly receive new data in real time. This introduces some complexity, particularly around error handling. This post discusses the strategies for handling errors in Apache Flink applications. However, the general principles discussed here apply to stream processing applications at large.
Read on to see how this all works when you’re hosting a Flink application. This directly relates to Flink applications that live in AWS, though very little in the article is AWS-specific.
Comments closed