Now that we understand a little more how dynamic SQL works, let’s see how it helped me solve the problem.
What is OPENROWSET?
OPENROWSET is a functionality that allows you to access data sources outside your current server. This could be reading from an Excel file or calling another SQL Server instance. You’re able to treat that other data source as record set, or derived table, and work with the rows returned as you would a local table. One reason you may want to do this is that you need to use a stored procedures to query data from other servers and bring the data together, effectively creating an ELT (Extract – Load – Transform) process without having to use SSIS or Azure Data Factory (ADF).
One key thing to note about OPENROWSET is understanding where that data gets processed.
Let’s connect to the home lab and look at some examples. (This is why we have a home lab after all, right?) We’ll connect to our HAVOK server and query our LABRADOR server.
Now let’s take a look at getting some data from the remote server along with the execution plan
As you can see, what’s being done in OPENROWSET is being executed on the remote instance. This also holds true for the execution plan.
Now let’s introduce some error handling…
My requirements say that if a server is unavailable, I don’t want the whole process to fail, but I do want to know that I had trouble accessing the server. This means I need to add some error handling to my statements.
If I were using linked servers, I could use sp_testlinkedserver to see if it was available. But I’m using OPENROWSET, so this isn’t an option.
Since the error is occurring on the remote instance, if there is an error in the statement, I won’t see it until it gets executed against the remote server because the check for parsing against the remote instance isn’t done until the statement is executed.
Let’s say my process is asked to do the same query I executed before on a server called CHIHUAHUA, which doesn’t exist.
Let’s see what that query looks like:
Now let’s add some error handling
Notice that the error handling didn’t actually catch the error here. When you read the documentation, you’ll notice that there are some things that just can’t be caught. One of those conditions is “Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution”. I believe this is the condition that I’ve been running into. But this is where dynamic SQL comes in and saves the day.
Because of the dynamic SQL, we’re able to delay the parse and catch the error at runtime.
With my real world bug, I had to take this a step further where the results from the OPENROWSET were being inserted into another table. Unfortunately, I had trouble reproducing that locally, which means there was probably another condition at play that I haven’t identified. But I was able to fix this by moving my insert statement inside the dynamic SQL statement in order for the TRY…CATCH block to work. Knowing that I could combine the INSERT from the OPENROWSET as a single dynamic SQL statement that error handling could catch was the solution that I felt confident implementing.
So there you have it – knowing some of these details on how dynamic SQL, OPENROWSET, and TRY…CATCH work created a relatively simple solution.
Deb the DBA 
2 thoughts on “OPENROWSET, Dynamic SQL & Error Handling”