Microsoft SQL Linked Server Test

Today's topic? Microsoft SQL Server Linked Servers!

Linked Servers are objects created in Microsoft SQL Server that allow queries to access data on other database servers such as Oracle. There are many reasons why a query that uses a linked server may fail with driver and network being the most common ones.

When I need to troubleshoot scenarios like the above, I usually start with testing the linked server connectivity with the query below.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
declare @srv nvarchar(128), @retval int;
set @srv = my_linked_server';
begin try
    exec @retval = sys.sp_testlinkedserver @srv;
end try
begin catch
    set @retval = sign(@@error);
end catch;
if @retval <> 0
  raiserror(Failed to connect to server!, 16, 2 );

Popular posts from this blog

Syslog Message Collection for OMS from sources that do not support the agent

Generating Alerts On OMS

How to Configure Message Forwarding on a Mailbox Level