Checking the REMOTE site of a database-link

CREATE FUNCTION DBA_LS_remote_site  (@servername varchar(100)) 
returns int
as
--*****************************************************************************************
--This Function is checking if the remote site of the LINKED SERVERS are responding or not ( using ping and @@servername )
-- parameter in = servername 
------------------------------------------------------------------------------------------------------------------------
-- return values
--
-- 0 - Ping OK and @@servername OK
-- 1 - Ping OK and @@servername NOK
-- 2 - Ping NOk and @@servername OK
-- 3 - Ping NOK and @@servername NOK
-- 4 - Server does not exists as ACTIVE server
------------------------------------------------------------------------------------------------------------------------
-- To retrieve the return value
--
-- declare @var1 int
-- execute @var1 = DBA_LS_remote_site '<servername>' 
-- print @var1
--*****************************************************************************************
-- Guus Kramer(GKramer at wxs.nl) <replace the ' at ' with an '@'>
-- The Netherlands
--*****************************************************************************************
begin

--Check servername section --------------------------------
declare @check_servername varchar(100)  

--PING and @@SERVER section -------------------------------
declare @Result int
declare @String01 sysname
declare @String02 sysname
declare @ping varchar(2)
declare @server_up varchar(2)
--Return section--------------------------------------------
declare @returnvalue int
-----------------------------------------------------------

set @servername = upper(@servername)

--Check servername section --------------------------------
set @check_servername = ''

if not exists ( select upper(servername)
from   < here a check if the server excist as an ACTIVE server in our server table> )
  begin
    --NOT exists
    set @check_servername = 'YY'
  end
else
  begin
    --exists
    set @check_servername = 'XX'
  end

if (@check_servername = 'YY')
  begin
    -- databases is not available in as ACTIVE in the DBA database
    set @returnvalue = 4
  end 
else
  begin
  --PING and @@SERVER section -------------------------------
set @String01 = 'ping '+substring(@servername,1, case when CHARINDEX('\', @servername)-1 > 0 then CHARINDEX('\', @servername)-1 else 100 end )
--print @String01 
             exec @result = master..xp_cmdshell @String01 , NO_OUTPUT

if (@result  = 0)
  begin
       --print @servername +' is reachable'
set @String02 = 'osql -E -S '+ @servername  +'  -q "select @@servername'
exec @result = master..xp_cmdshell @String02 , NO_OUTPUT
  if (@result  = 0)
    begin
      set @ping = 'Y'
      set @server_up = 'Y'
      -- do nothing
    end
  else
    begin
      set @ping = 'Y'
      set @server_up = 'N'
    end
  end
else
  begin
       --print 'No ping possible to '+@servername 
set @String02 = 'osql -E -S '+ @servername  +' -q "select @@servername'
exec @result = master..xp_cmdshell @String02 , NO_OUTPUT
  if (@result  = 0)
    begin
      set @ping = 'N'
      set @server_up = 'Y'
    end
  else
    begin
      set @ping = 'N'
      set @server_up = 'N'
    end
  end
  end 

  -----------------------------------------------------------

if @ping = 'Y' and @server_up = 'Y'
  begin
  -- server is up an SQL is reachable
  set @returnvalue = 0
  end
if @ping = 'Y' and @server_up = 'N'
  begin
  -- server is up an SQL is NOT reachable
  set @returnvalue = 1
  end
if @ping = 'N' and @server_up = 'Y'
  begin
  -- server is NOT up an SQL is NOT reachable
  set @returnvalue = 2
  end
if @ping = 'N' and @server_up = 'N'
  begin
  -- server is NOT up an SQL is NOT reachable
  set @returnvalue = 3
  end

return (@returnvalue)
end