First published on MSDN on Feb 21, 2007
SQL Server 2005 Service Pack 2 (SP2) released on Monday to the web. If you want to start downloading it while reading the rest of the post, go right to
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx
.
Over the next several weeks, several of us in PSS will be posting blogs specifically on changes or features for SP2 focusing on troubleshooting and supportability.
I'll kick start this series of posts by telling you that we have brought back GUI features for linked server query setup and configuration. What do I mean by this? In previous versions of SQL Server, you could "test" the connection to a linked server in Enterprise Manager by selecting the linked server and trying to enumerate tables in the remote database. In SQL Server 2005, this functionality was not included in SQL Server Management Studio.
With SP2, that feature is now back and an additional feature to "Test" a linked server connection is now available. When you create a linked server connection, the connection is tested at the time you create it (This is new. Prior to SP2, we just created the linked server definition whether you can connect or not). So if you have a problem with the connection configuration, you will know this at the time you create it. But after that, here is how you can test your linked server connection and browse its catalog to test basic functionality:
1) You can right click on a defined link server connection (Under the Server Objects tree) and select "Test Connection" (I've attached an example of the screen with this post). If it fails, you will get details of why the linked server connection could not be made.
2) When you have a linked server defined you can enumerate the catalogs (databases) and the tables in these catalogs using the familiar tree view interface. In the enumeration fails, you will get an error explaining the cause.
So if you are having issues with linked server queries failing, try out this new feature in Management Studio to ensure the basic connectivity and catalog enumeration works for your linked server. It may save you a call to PSS.
Bob Ward, Microsoft
test_linked_server.bmp
