Linked Servers

• muralip

  SSC Rookie

  Points: 26

  More actions

  December 5, 2002 at 9:41 am

  #63227

  Folks,

  I am using linked servers to insert data into a database on another server. How do I know whether the linked server is available or not? If not, how to grab the error message?

  TIA

  Murali

• December 9, 2002 at 8:00 am

  This was removed by the editor as SPAM

• dj_meier

  SSCrazy

  Points: 2732

  More actions

  December 10, 2002 at 11:40 am

  #443973

  Here is one method using sp_OACreate/DMO. You can set the timeout value to whatever you think fits your network speed, etc. I've ommitted the error handing for simplicity.

   
  
  DECLARE @object int, @rc int
  
  -- Create the DMO object.
  
  EXEC @rc = sp_OACreate 'SQLDMO.SQLServer', @object OUT
  
  EXEC @rc = sp_OASetProperty @object, 'LoginTimeout', '3'
  
  EXEC @rc = sp_OAMethod @object, 'Connect', NULL, 'MYSERVER', 'user1', 'passwd'
  
  IF @rc <> 0
  
    PRINT 'SERVER NOT FOUND.' 
  
  ELSE
  
  BEGIN
  
    PRINT 'SERVER FOUND.' 
  
    EXEC @rc = sp_OAMethod @object, 'DisConnect' -- Disconnect.
  
  END
  
  EXEC @rc = sp_OADestroy @object
  
  

  -Dan

  Edited by - dj_meier on 12/10/2002 11:41:18 AM

  
  -Dan