Sql 2000 - Error Handling when server no longer exists (Linked Servers)

• lisa.archuleta

  Newbie

  Points: 6

  More actions

  May 12, 2009 at 4:12 pm

  #133869

  Hello,

  I currently have a stored procedure that obtains a list of linked servers and then acquired the databases that they are each storing from their respective master.sysservers table.

  However, the linked servers do not automatically get removed by our DBA's after the linked server is deleted. So, I sometimes get a message with the connection faliling when the server no longer exists. (Msg 17, Level 16, State 1, Line 1 - [DBNETLIB] [ConnectionOpen (Connect()).]Sql Server does not exist or access denied.)

  Apparently, the script does not get executed fully and some of the fields in my table that should be populated with data do not get populated.

  Is there an exception handling procedure that I can run for the instances where the server no longer exists to ignore the server and move on to the next server?

  Thanks,

  --Lisa

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  May 12, 2009 at 8:44 pm

  #993394

  Try using dynamic SQL to do this.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]