XML FeedsI have a bunch of linked servers to SQL Servers and also to Sybase ASE Servers ON AIX machines. There are some interesting things that can happen. For example if you type the name of the object or the linked server itself wrong, you can't trap this..... or can you?
Let's take a look. Open up SSMS and connect to your local instance. Now create a linked server named TestLinkedServer which points to the local server. The code to do that is below.
- EXEC master.dbo.SP_ADDLINKEDSERVER @server = N'TestLinkedServer',
- @srvproduct=N'',
- @datasrc='(local)',
- @provider='SQLNCLI'
Now we can do a small test, run the following code
- SELECT * FROM OPENQUERY(TestLinkedServer,'select count(*) from tempdb..sysobjects')
That should return an integer.
If we try something more interesting like a division by zero, will it get trapped?
- BEGIN TRY
- SELECT * FROM OPENQUERY(TestLinkedServer,'select 1/0')
- END TRY
- BEGIN CATCH
- PRINT ERROR_MESSAGE()
- PRINT ERROR_NUMBER()
- END CATCH
- PRINT 'TEST'
Divide by zero error encountered.
8134
TEST
Yes, that worked just as expected.
Now what do you think will happen if we change the table name from sysobjects to sysobjects2? Let's run it and see
- BEGIN TRY
- SELECT * FROM OPENQUERY(TestLinkedServer,'select count(*) from tempdb..sysobjects2')
- END TRY
- BEGIN CATCH
- PRINT ERROR_MESSAGE()
- PRINT ERROR_NUMBER()
- END CATCH
- PRINT 'TEST'
OLE DB provider "SQLNCLI10" for linked server "TestLinkedServer" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tempdb..sysobjects2'.
Ouch it blew up on us and never made it to the print statement.
How about if we use TestLinkedServer2 instead of TestLinkedServer?
- BEGIN TRY
- SELECT * FROM OPENQUERY(TestLinkedServer2,'select count(*) from tempdb..sysobjects')
- END TRY
- BEGIN CATCH
- PRINT ERROR_MESSAGE()
- PRINT ERROR_NUMBER()
- END CATCH
- PRINT 'TEST'
Msg 7202, Level 11, State 2, Line 4
Could not find server 'TestLinkedServer2' 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.
Same problem, blows up and never makes it to the print statement. What if we take the last two examples and wrap them inside an exec statement?
- BEGIN TRY
- exec( 'SELECT * FROM OPENQUERY(TestLinkedServer,''select count(*) from tempdb..sysobjects2'')')
- END TRY
- BEGIN CATCH
- PRINT ERROR_MESSAGE()
- PRINT ERROR_NUMBER()
- END CATCH
- PRINT 'TEST'
OLE DB provider "SQLNCLI10" for linked server "TestLinkedServer" returned message "Deferred prepare could not be completed.".
Invalid object name 'tempdb..sysobjects2'.
208
TEST
That caught the exception and TEST was printed
- BEGIN TRY
- exec( 'SELECT * FROM OPENQUERY(TestLinkedServer2,''select count(*) from tempdb..sysobjects'')')
- END TRY
- BEGIN CATCH
- PRINT ERROR_MESSAGE()
- PRINT ERROR_NUMBER()
- END CATCH
- PRINT 'TEST'
Could not find server 'TestLinkedServer2' 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.
7202
TEST
Beautiful, Even using a non existent linked server name is caught when using it inside an EXEC statement.
As you can see it is possible to trap a problem with linked servers if you wrap it inside an Exec statement which you could not trap otherwise. One thing you can't trap however is a timeout.
Here are some of the error messages I trapped on my boxes
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "Test".
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Test".
The OLE DB provider "MSDASQL" for linked server "Test" could not INSERT INTO table "[MSDASQL]".
The OLE DB provider "MSDASQL" for linked server "Test" reported an error committing the current transaction.
Hope you learned something and hopefully this will help you in your troubles with linked servers
*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.