Posted in SQL Server, T-SQL

OPENROWSET, Dynamic SQL & Error Handling

Now that we understand a little more how dynamic SQL works, let’s see how it helped me solve the problem.


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.

Query shows the local server, session, and database alongside the same information from OPENROWSET where we can see the different server, session, and database name being returned.

Now let’s take a look at getting some data from the remote server along with the execution plan

Query against the LABRADOR server and Superheroes database to return some data
NOTE: the execution plan says “Restricted Text”. This means we have no clue what’s going on from this side of the OPENROWSET.

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.

Great – someone told Sebastian, our Chihuahua mix, that there’s no server called CHIHUAHUA. Yep, I’m looking frazzled for a reason…

Let’s see what that query looks like:

Our OPENROWSET query with an error

Now let’s add some error handling

Adding a TRY… CATCH block around the query that we know will cause an error does not actually catch the problem.

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.

Error handling catches the problem

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.


2 thoughts on “OPENROWSET, Dynamic SQL & Error Handling

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s