Trapping linked server errors

SQL Server provides a TRY CATCH mechanism to enable an application to realise that something has gone wrong and execute appropriate code to handle the problem. For example:

BEGIN TRY
   some code
END TRY
BEGIN CATCH 
   PRINT 'This is the error: ' + error_message()
END CATCH

The code in the TRY block is executed first. If an error occurs, execution is passed to the CATCH block.

What are the implications for error handling if you want to use TRY CATCH with a linked server? In this situation, an error can occur in:

1. The local SQL Server instance.
2. The SQL Server interface used to facilitate the connection with the remote server.
3. The interface used to make the remote server accessible to SQL Server.

Our example setup will use a Salesforce server as the remote server. For this remote server type, interface 2. is the Microsoft OLE DB Provider for ODBC Drivers and interface 3. is Easysoft's Salesforce ODBC driver.

The following error occurs locally in SQL Server. The linked server named SALESFORCE has not yet been created and so SQL Server is unable to locate it:

BEGIN TRY
    EXEC('SELECT * FROM OPENQUERY(SALESFORCE, ''SELECT * FROM LEASE'')')
END TRY
BEGIN CATCH 
    PRINT 'This is the error: ' + error_message()
END CATCH

This is the error: Could not find server 'SALESFORCE' in sys.servers. Verify that
the correct server name was specified. If necessary, execute the stored procedure
sp_addlinkedserver to add the server to sys.servers.

The next error occurs in the Microsoft OLE DB Provider for ODBC Drivers layer. The linked server named SALESFORCE references an ODBC data source that contains the connection details for the target Salesforce server. However, SQL Server is 64-bit but the ODBC data source has been set up in the 32-bit ODBC Data Source Administrator. The connection fails with an "architecture mismatch" error

OLE DB provider "MSDASQL" for linked server "SALESFORCE" returned message
"[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture
mismatch between the Driver and Application".
This is the error: Cannot initialize the data source object of OLE DB provider
"MSDASQL" for linked server "SALESFORCE".

Notice that SQL Server's interpretation of the OLE DB error has been caught by the CATCH block. The "raw" error form the OLEDB layer has not.

This final error occurs in the ODBC layer used to connect to Salesforce. The select query references a custom Salesforce object ("LEASE") that has not yet been enabled and populated. "[Easysoft ODBC]Base table or view LEASE not found" is an error from the ODBC driver, which is passed to and displayed by the Microsoft OLE DB Provider for ODBC Drivers. As in the previous example, this message is not trappable, SQL Server's interpretation of the message is trappable.

OLE DB provider "MSDASQL" for linked server "SALESFORCE" returned message
"[Easysoft ODBC]Base table or view LEASE not found".
This is the error: An error occurred while preparing the query
"SELECT * FROM LEASE" for execution against OLE DB provider "MSDASQL" for linked
server "SALESFORCE".

For more information about SQL Server error handling both with and without linked servers see:

http://www.sommarskog.se/error_handling/Part1.html