Link Server
-
Using SQL 2000...
I need help with determining using SQL code if a link server is available. If it is, then the proc will continue on. If it is not, then it will send a message out letting the admin know it is not reachable. Can anyone give me some guidance on how I can do this through SQL code?
Thank you,
Carol
-
Maybe something like this :
If exists (Select * from LinkedServer.DbName.dbo.TableName)
begin
--do what you need to do
end
if @@error > 0
begin
--linkedserver is down, raiserror...
end
but you could always do what you have to do and check for an error after the statement.. would save you a roundtrip to the linked server (I have one server that takes 5 secs just to connect).
-
If I remember, right, though, doesn't this generate a sev 16 error if the linked server is unavailable (meaning it drops out of the batch and doesn't run anything else). We wanted to do something similar, verify servers were up but resorted to DTS. Basically the DTS has an ActiveX script that makes an ADO connection to the server. We can check the Connection state property to verify if a connection was successfully made. Maybe something like this would work for you.
K. Brian Kelley
@kbriankelley -
Don't know about the severity level... good point though. Maybe this is not a task to be handled in a proc.
-
Hey how come I have question marks under my name while you have a nice(r) logo??
-
Remi,
You can't check from TSQL the conectivity of a Linked Server because as Brian said above the batch will be suspended if an error occurs. The solution is DMO, ACTIVEX or A JOB that runs out of your current context
* Noel -
I won't agree on this one because that would be redundant
. Thanx for the info. -
You can always update your AVATAR under the Account Profile
* Noel -
And here I'll agree with me
* Noel -
That's not it. The avatar is its tranformer logo, the rectangles is another logo (linked with post count/member status??).
-
I was a guinea pig of Steve and Andy as they were transitioning to the messageboards. Somewhere along the line I get the graphic under my name. I like it a whole lot more than the question marks so I haven't complained.
K. Brian Kelley
@kbriankelley -
"guinea pig" : I'll keep the question marks on then... Anyways it's gonna switch to "carpal tunnel" in a few weeks so I'll forget it for a while
. -
The rectagles are for MODERATORS
http://www.sqlservercentral.com/forums/images/rankimages/moderator.gif
do you want the rectangles or the AVATAR ?
* Noel -
I didn't want the "? ? ? ? ?"...
-
I guess you will have to post this one on the Suggestions Forum
* Noel
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply