Determine the server if connected

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Determine the server if connected

Author

Topic

rpc86
Posting Yak Master

200 Posts

Posted - 2008-08-24 : 23:46:11

Hi guys,

I used sp_linkedserver. Before I use this sp, I always use sp_dropserver. But my server names are dynamic because we are on-line.

No, how will I determine if one server is connected ? somethine like...


if...this.server is..connected...

thank you

rpc86
Posting Yak Master

200 Posts

Posted - 2008-08-25 : 20:37:02

Why no replies ?

Please, please help

thank you

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-25 : 22:45:22

sp_linkedservers with no params lists what is linked

rpc86
Posting Yak Master

200 Posts

Posted - 2008-08-25 : 22:53:58

ok, thanks.
what if i want to determine only 1 server if its existing

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-25 : 23:26:56

output the result of sp_linkedservers into temp table and check against it

KH
[spoiler]Time is always against us[/spoiler]

rpc86
Posting Yak Master

200 Posts

Posted - 2008-08-26 : 00:14:32

how?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-26 : 00:18:19

[code]
create table #temp ( . . . ) -- structure should be same as the result of sp_linkedservers

insert into #temp exec sp_linkedservers

select * from #temp where <...>
[/code]

KH
[spoiler]Time is always against us[/spoiler]

rpc86
Posting Yak Master

200 Posts

Posted - 2008-08-26 : 01:10:57

ok thanks, but what I need is something like this...


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[myTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[myTable]
GO

But instead of Table, its the server name I want to validate its existence.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-26 : 03:37:18

[code]

create table #temp ( . . . ) -- structure should be same as the result of sp_linkedservers

insert into #temp exec sp_linkedservers

if not exists (select * from #temp where <...> )
begin
exec sp_linkedservers . . .
end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-26 : 03:58:06

or
if not exists (select * from sysservers where srvname='your server name')
begin
exec sp_linkedservers . . .
end

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources