Site navigation
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 CATCHThe 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:
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 CATCHThis 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: