Is the linked server being dropped, or is some error happening when you are
trying to access it? If so, utilize my IF EXISTS statement below.
Additionally, depending on the amount of traffic and number of connections
that will be accessing the linked server, you can do something along these
lines:
--Drop Server if it exists
IF EXISTS (SELECT srv.srvname FROM master..sysservers srv WHERE srv.srvid !=
0 AND srv.srvname = 'LinkedServerName')
EXEC sp_dropserver @server = 'LinkedServerName'
-- (Re)Create Server
EXEC sp_addlinkedserver @server = 'LinkedServerName', @srvproduct='SQL
Server'
-- Begin Tasks on Linked Server HERE
...
-- End Tasks
-- (Re)Drop Server
EXEC sp_dropserver @server = 'LinkedServerName'
Of course you may have to mess with the connection string and depending on
the security context you use on the linked server add a Linked Server Login
(sp_addlinkedsrvlogin) line, but hopefully you get the drift. If you do use
this route, you will need to put the sp_dropserver and sp_addlinkedserver
statements in their own SPs and call them from some parent SP because they
need to execute successfully in their own context for the linked server to
activate and be utilized.
HTH,
John Eisbrener
SQL Database Administrator
Capitol Insurance Companies
-----Original Message-----
From: Ligda John
[mailto:mssqldba-ezmlmshield-x88485115.[Email address protected]
Sent: Thursday, November 29, 2007 4:09 PM
To: LazyDBA Discussion
Subject: RE: linked server suffers act of god
I forgot to mention this is server 2000 so no TRY CATCH :(
-----Original Message-----
From: Ligda John
[mailto:mssqldba-ezmlmshield-x60614017.[Email address protected]
Sent: Thursday, November 29, 2007 2:06 PM
To: LazyDBA Discussion
Subject: linked server suffers act of god
Hi,
Im looking for procedure logic that says; 'Try this linked server, if
its there, great do stuff with it, but if its gone, do nothing and go to
the next step."
How do I trap an error message for a linked server call when the linked
server blows up?
John
--
DISCLAIMER
Confidentiality Note: This e-mail is intended only for the person or
entity to
which it is addressed and may contain information that is privileged,
confidential or otherwise protected from disclosure. Dissemination,
distribution or copying of this e-mail or the information herein by
anyone
other than the intended recipient, or an employee or agent responsible
for
delivering the message to the intended recipient, is prohibited. If you
have
received this e-mail in error, please notify us immediately (telephone
415-288-0544 or e-mail [Email address protected] and destroy the
original
message and all copies.
Hall Capital Partners LLC reserves the right to monitor and review the
content
of all e-mail communications sent and/or received by its employees.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
--
DISCLAIMER
Confidentiality Note: This e-mail is intended only for the person or entity
to
which it is addressed and may contain information that is privileged,
confidential or otherwise protected from disclosure. Dissemination,
distribution or copying of this e-mail or the information herein by anyone
other than the intended recipient, or an employee or agent responsible for
delivering the message to the intended recipient, is prohibited. If you have
received this e-mail in error, please notify us immediately (telephone
415-288-0544 or e-mail [Email address protected] and destroy the original
message and all copies.
Hall Capital Partners LLC reserves the right to monitor and review the
content
of all e-mail communications sent and/or received by its employees.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page