SQL Server: Test Linked Server Connection

If you want to have access to data stored in a different server, one of the ways to do so is to make that server as a Linked Server to the current server and then query using the linked server name. Let us assume that there is a server named test available over the network and you want to get some data from there. System stored procedure sp_addlinkedserver can be used to create a linked server and fire distributed queries.

The following code adds the server test as Linked server to the current server

EXEC sp_addlinkedserver test

If you want to know if the connection to the linked server is accessible, you can make use of system stored procedure sp_testlinkedserver as shown below

EXEC sp_testlinkedserver test

It simply checks if the connection is successful. If the result is "Command(s) completed successfully.", it means the connection is successful. This is the simplest way to check if the linked server is accessible

Also check Viewing Linked Server Information in SQL Server 2005/2008

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan