I was unable to cobble together some Powershell code that I could execute within a job-step to check our linked-servers were working.
So I resorted to making the best of the built-in, but flawed, “SP_testlinkedserver” (Its a flawed procedure as if a link fails, it crashes, slowly).
The code below, when ran in a job-step overnight, will dynamically create one job for each linked-server on the box. The job(s) will then run and email the “DBA” operator every linked-server that fails, before deleting themselves.
-- testlinkedservers.sql
-- get list of all linked servers on this box
CREATE TABLE #temp (
srv_name varchar(MAX),
srv_providername varchar(MAX),
srv_product varchar(MAX),
srv_datasource varchar(MAX),
srv_providerstring varchar(MAX),
srv_location varchar(MAX),
srv_cat varchar(MAX))
INSERT INTO #temp EXEC sp_linkedservers
DELETE FROM #temp WHERE srv_name LIKE 'LOGSHIP%'
DELETE FROM #temp WHERE srv_name = @@SERVERNAME
-- loop
DECLARE @name VARCHAR(MAX), @cmd VARCHAR(MAX), @run VARCHAR(MAX)
WHILE (SELECT COUNT(*) FROM #temp) > 0
BEGIN
SELECT TOP 1 @name = srv_name FROM #temp
-- create the job code
SET @cmd = 'BEGIN TRANSACTION
DECLARE @jobId BINARY(16)
SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N''DBA - LinkedServerTest ' + @name + ''')
IF (@jobId IS NULL)
BEGIN
EXEC msdb.dbo.sp_add_job @job_name=N''DBA - LinkedServerTest ' + @name + ''',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=3,
@description=N''No description available.'',
@category_name=N''[Uncategorized (Local)]'',
@owner_login_name=N''sa'',
@notify_email_operator_name=N''DBA'',
@job_id = @jobId OUTPUT
END
-- create the job-step code
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId AND step_id = 1)
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''one'',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N''TSQL'',
@command=N''sp_testlinkedserver [' + @name + ']'',
@database_name=N''master'',
@flags=0;
-- create instantiation code
EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N''(local)''
COMMIT TRANSACTION'
-- create the job
EXEC(@cmd)
-- run the job
SET @run = 'EXECUTE msdb.dbo.sp_start_job ''DBA - LinkedServerTest ' + @name + ''''
EXEC(@run)
-- move to next row in loop
DELETE FROM #temp WHERE srv_name = @name
END
.
Database Consultant 