/ Published in: SQL
This piece of code is really useful to manage the Linked Servers. It will tell you if a linked server is no longer valid. That is pointing to an invalid DNS entry or an address that doesn't ping.
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
-- Script to get all the info from the Linked Server properties -- Also extract the ALIAS from the client network utility -- Also extract the DNS IP from the Alias USE master GO IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'linkedsvr_detail') DROP TABLE tempdb..linkedsvr_detail IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'linkedsvr_summary') DROP TABLE tempdb..linkedsvr_summary IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'linkedsvr_dns') DROP TABLE tempdb..linkedsvr_dns GO -- SQL Client Network Alias CREATE TABLE #alias(Alias nvarchar(50), Server nvarchar(250)) INSERT INTO #alias EXEC master..xp_regenumvalues 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo' UPDATE #alias SET Server = SUBSTRING(Server, charindex(',', Server)+1, 100) SELECT DISTINCT @@servername FromServer, s.srvname LinkedServerName, a.server alias, '255.255.255.255' ip, isnull(a.server, datasource) rmtserver, u.rmtloginame INTO tempdb..linkedsvr_summary FROM sysservers s JOIN sysoledbusers u ON s.srvid = rmtsrvid LEFT JOIN #alias a ON a.alias = s.datasource WHERE s.isremote = 1 AND u.rmtloginame IS NOT NULL ORDER BY 2,3 SELECT @@servername FromServer, s.srvname LinkedServerName, datasource, a.server alias, u.rmtloginame, l.loginname INTO tempdb..linkedsvr_detail FROM sysservers s JOIN sysoledbusers u ON s.srvid = rmtsrvid JOIN syslogins l ON u.loginsid = l.sid LEFT JOIN #alias a ON a.alias = s.datasource WHERE s.isremote = 1 ORDER BY s.srvname, u.rmtloginame -- DNS Alias Entry CREATE TABLE #dns(alias nvarchar(50) PRIMARY KEY, ip VARCHAR(15)) INSERT INTO #dns(alias) SELECT DISTINCT CASE WHEN charindex('\',rmtserver)>0 then left(rmtserver,charindex('\',rmtserver)-1) else rmtserver end from tempdb..linkedsvr_summary create table #output(output nvarchar(255)) -- shell cmd output declare @alias nvarchar(50), @cmd nvarchar(250), @ip varchar(15) declare ALIASCUR cursor read_only fast_forward for select alias from #dns open ALIASCUR fetch next from ALIASCUR into @alias while @@fetch_status = 0 begin set @cmd = 'PING -n 1 ' + @alias insert into #output exec master..xp_cmdshell @cmd select @ip = substring(output, charindex('[',output)+1,charindex(']',output)-1-charindex('[',output)) from #output where left(output,7)='Pinging' update #dns set ip = isnull(@ip,'DNS error') where alias = @alias fetch next from ALIASCUR into @alias end close ALIASCUR deallocate ALIASCUR update s set ip = d.ip from tempdb..linkedsvr_summary s left join #dns d on case when charindex('\',rmtserver)>0 then left(rmtserver,charindex('\',rmtserver)-1) else rmtserver end = d.alias select * into tempdb..linkedsvr_dns from #dns -- just for backups drop table #dns drop table #output drop table #alias -- Now you need to gather info from all servers and see if we have SA login used by Linked Servers -- select * from tempdb..linkedsvr_summary -- select * from tempdb..linkedsvr_detail -- select * from tempdb..linkedsvr_dns -- select @@servername, lower(name) from master..syslogins where sysadmin = 1 -- select fromserver, [to] LinkedServerName, l.Login -- from dbo.LinkedServers s -- join sa_logins l on l.Server=s.toserver and l.Login=s.login -- order by 1,2