jump to navigation

Testing linked servers May 25, 2009

Posted by msrviking in Scripts.
trackback

Its been scripting day, although not too complicated scripting. One of my team mates came up to me asking “Is there any easy way out to test linked servers configured in an instance?”. I said there is a way, but not sure if it would be easy or hard way out. Well, at the end with little tussle around on the script, I realized I should keep it simple and here is the bunch of t-sql statements I have written.

I have built this script from an original script written by someone (thanks to his ideas!).

/*Script to Test Linked Servers connectivity*/

BEGIN
SET NOCOUNT ON
/*Variable Declaration */
DECLARE @ServerName sysname
DECLARE @msg VARCHAR(500)
DECLARE @status INT ;
/*Table variable to capture the linked server status*/
DECLARE
@LinkedSrvrStatus TABLE
(
LineItem INT IDENTITY(1,1)
, LinkedSrvName VARCHAR(200)
, LinkedSrvStatus TINYINT
, TestComments VARCHAR(800)
)

— Get the list of Linked Servers
DECLARE LnkdsrvrCrsr
CURSOR FAST_FORWARD FOR
SELECT name FROM sys.servers
WHERE is_linked=1 AND name NOT IN (”) –Not in Clause can be used to exclude some Test Linked Servers

OPEN LnkdsrvrCrsr
FETCH NEXT FROM LnkdsrvrCrsr INTO @ServerName

WHILE @@FETCH_STATUS = 0
BEGIN
— The Below query will test the connectivity of the linked server
BEGIN TRY
EXEC master..sp_testlinkedserver @ServerName
/*PRINT ‘YIPEE! THERE IS A LINKED SERVER OF SUCH KIND AND CONNECTIVITY IS THROUGH’ */ — Debugging statement, and the content of the statement could be changed appropriately.
INSERT INTO @LinkedSrvrStatus (LinkedSrvName, LinkedSrvStatus, TestComments) VALUES (@ServerName,1,’YIPEE! THERE IS A LINKED SERVER OF SUCH KIND’)
END TRY

— Checking Status and Sending Alert Mail to Team
BEGIN CATCH
PRINT ‘NO SUCH LINKED SERVER OR CREDENTIALS ARE INCORRECT!’
INSERT INTO @LinkedSrvrStatus (LinkedSrvName, LinkedSrvStatus, TestComments) VALUES (@ServerName,0,’NO SUCH LINKED SERVER OR CREDENTIALS ARE INCORRECT!’)
END CATCH

FETCH NEXT FROM LnkdsrvrCrsr INTO @ServerName
END
CLOSE LnkdsrvrCrsr
DEALLOCATE LnkdsrvrCrsr
END

/*Select linked server status from the table variable*/
SELECT * FROM @LinkedSrvrStatus

I hope this script helps someone out there.

Cheers!

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: