Fri, April 15 2016
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
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:
- The local SQL Server instance.
- The SQL Server interface used to facilitate the connection with the remote server.
- The interface used to make the remote server accessible to SQL Server.
Our example setup will use a Salesforce.com 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.com 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.com server. However, SQL Server is 64-bit but the ODBC data source has been set up in the 32-bit ODBC 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.com. The select query references a custom Salesforce.com 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: