Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Script to check all your linked server connections for SQL Server

MSSQLTips author Thomas LaRock By:   |   Read Comments (2)   |   Related Tips: 1 | 2 | 3 | 4 | More > Linked Servers
Problem

You need to quickly check all linked server connections for a given instance or list of instances.

Solution

Using Powershell and a Central Management Server you can quickly check your linked servers for connectivity.


Checking your connections

Every now and then you may have the need to check and verify that all of your defined linked servers are able to provide basic connectivity. You will most often find yourself needing to check these linked server definitions usually in between the time a server has been rebuilt after a disaster event and the time you turn it back over to your end users for them to test their systems. Having a script to quickly cursor through and check each definition comes in handy as it can save you headaches later when users complain about their systems not working.

It is not uncommon, especially as servers get older, to have more than a handful of linked servers defined. And these links could point to a variety of sources, and require a variety of specific drivers to be loaded onto your instance. As such, it is quite possible that as time passes, and systems pass from one person to another to administer, that the details of the installations can be lost. The last thing you want to do after a disaster is to hand over a server that is simply not ready.

Another use for this script would be for routine checks to make sure that your links are still valid, and to clean up ones that are no longer working. It is always better to have only those linked servers defined that are necessary, rather than to simply continue to carry forward a host of definitions that are no longer valid. This script works against SQL 2005 and SQL 2008 instances.

$OutputFile = "C:\LinkedServerFail.txt"
"Starting linked server connectivity test..." | Out-File $OutputFile 
$CMSGroup = Set-Location "SQLSERVER:\SQLRegistration\Central Management Server Group\YourCMSInstance\YourCMSDirectory\"
$InstanceListFromCMS = dir $CMSGroup -recurse | where-object { $_.Mode.Equals("-") } | select-object Name -Unique
foreach ($InstanceName in $InstanceListFromCMS) 
{
 $InstanceNameFriendly = (Encode-SqlName $InstanceName.Name) 
 $InstanceNameFriendly = (Decode-SqlName $InstanceNameFriendly) 
 
 $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceNameFriendly
 try
 {
 $LSName = $s.linkedservers | where-object {$_.State -eq "Existing"}
 }
 catch
 {
 "Cannot connect to server $InstanceNameFriendly." | Out-File $OutputFile -Append
 }
 
 foreach ($Name in $LSName)
 {
  if ($Name -ne $null)
  {
   try
   {
    $Name.testconnection()                     
    $Connectivity = $true
   } 
   catch 
   {                    
    $Connectivity = $false
    "$InstanceNameFriendly $Name connection failure." | Out-File $OutputFile -Append
   }
  }
 } 
}

The above script will output to a text file located at "C:\LinkedServerFail.txt", feel free to place the output file anywhere you want. You will also need to substitute the name of the instance where your CMS is registered (YourCMSInstance) as well as the directory for the servers you want to check. If you do not include a directory it will return all server instances resgitered in the CMS.

For example: $CMSGroup = Set-Location "SQLSERVER:\SQLRegistration\Central Management Server Group\SQL252\SQL2008\Test\"  

SQL252 is the name of my CMS instance. I have a directory named SQL2008 and a sub-directory named Test. The script will enumerate the servers registered and assigned to the directory and the script will connect to each server name and then attempt to test the connection for each linked server that is defined.

To execute the script, save it to a file with a .ps1 extension. For example, you could save it to c:\run.ps1. Open up a command prompt, navigate to the folder where the .ps1 files exists and run the following command:

C:> sqlps c:\run.ps1

The output will be written to a file that you can use to further investigate your linked server connections.


Next Steps


Last Update: 5/27/2010


About the author
MSSQLTips author Thomas LaRock
Thomas LaRock is a seasoned IT professional with over a decade of experience. He is a Senior DBA for Confio Software and SQL Server MVP.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Thursday, August 19, 2010 - 3:10:02 PM - krystian Read The Tip
Another way to check linked servers connections for SQL servers defined on particular SQL instance is to used new system store procedure available in sql 2005.

Procedure located in master database 'sp_testlinkedserver' @servername

it takes one parameter @servername - linked server name.

Below is example how procedure can be used: 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[usp_CheckLinkedServersConnectivity]

@recipients nvarchar(500) = 'someone@imaginarycompany.ca', --- here you ca specify all email addresses for sending email notification

@Debug int = 1

as

/* procedure to check connectivity to all linked servers define on local SQL server (must be 2005 and up)

its using new system store procedure sp_testlinkedserver

Created by Krystian Szczyrba 29 June 2010

*/

declare @Linked_Server_Name nvarchar(128)

declare @Return_Status int

declare @subject nvarchar(500)

DECLARE

@ErrorMessage NVARCHAR(4000),

@ErrorNumber INT,

@ErrorSeverity INT,

@ErrorState INT,

@ErrorLine INT,

@ErrorProcedure NVARCHAR(200);

set nocount on

declare Linked_Servers cursor for select srvname from master.dbo.sysservers

open Linked_Servers

FETCH NEXT FROM Linked_Servers

INTO @Linked_Server_Name

WHILE @@FETCH_STATUS = 0

BEGIN

begin try

execute @Return_Status = sp_testlinkedserver @servername = @Linked_Server_Name

end try

begin catch

IF ERROR_NUMBER() IS NOT NULL

BEGIN

-- Assign variables to error-handling functions that

-- capture information for Sending Error message in email.

SELECT

@ErrorNumber = ERROR_NUMBER(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE(),

@ErrorLine = ERROR_LINE(),

@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

-- Building the message string that will contain original

-- error information.

SELECT @ErrorMessage = 'Error Number : ' + cast(@ErrorNumber as varchar(5)) + ', Error Severity : ' + cast(@ErrorSeverity as varchar(5)) + ', Error State : ' + cast(@ErrorState as varchar(5)) + ', Error Line : ' + cast(@ErrorLine as varchar(5)) + ', Error Procedure : ' + cast(@ErrorProcedure as varchar(5))

+ 'Message: '+ ERROR_MESSAGE() + ' -- ' + @Linked_Server_Name

set @subject = 'Unable to CONNECT to linked server : ' + @Linked_Server_Name + ' from ' + @@SERVERNAME + ', check why connection is not working'

execute msdb.dbo.sp_send_dbmail @subject = @subject,

@recipients = @recipients,

@body = @ErrorMessage

END

END CATCH

FETCH NEXT FROM Linked_Servers

INTO @Linked_Server_Name

END

CLOSE Linked_Servers

DEALLOCATE Linked_Servers

 

 




Wednesday, March 06, 2013 - 10:53:00 AM - ramesh Read The Tip

 

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (Microsoft SQL Server, Error: 7308)



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.