Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Manchester
    Posts
    1

    Question Unanswered: how to check if a liked server exists

    Hi there,

    I am quite new to SQL Server and am having a bit of trouble.

    I have created a linked server with the datasource an excel spreadsheet.

    I am trying to write a bit of T-SQL that basically checks if the linked server exists. If it does do X, if not do Y.

    Can anyone help, its driving me bananas!

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: how to check if a liked server exists

    Originally posted by graeme348
    Hi there,

    I am quite new to SQL Server and am having a bit of trouble.

    I have created a linked server with the datasource an excel spreadsheet.

    I am trying to write a bit of T-SQL that basically checks if the linked server exists. If it does do X, if not do Y.

    Can anyone help, its driving me bananas!
    sp_linkedservers
    it will return the list of linked servers defined on the local server.

    or you can query the master database
    select * from sysservers
    Last edited by harshal_in; 02-25-04 at 08:22.

  3. #3
    Join Date
    Feb 2004
    Posts
    8
    Hello

    by chance I am trying to do the same thing.

    WRT sp_linkedservers this is executed as following

    exec sp_linkedservers

    The resutls that I want appear in the grid windows in query analyser. How do i put the results of this sp into a table? I have tried

    exec sp_linkedservers into #LStable

    but this doesnt work. I have also looked at the sp definition and cant see a way of doing it either

    Thanks in advance

    Richard

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    create table #tmp (
    SRV_NAME varchar(128) not null,
    SRV_PROVIDERNAME varchar(128) not null,
    SRV_PRODUCT varchar(255) null,
    SRV_DATASOURCE varchar(255) null,
    SRV_PROVIDERSTRING varchar(255) null,
    SRV_LOCATION varchar(255) null,
    SRV_CAT varchar(255) null)
    go
    insert #tmp exec sp_linkedservers
    go
    select * from #tmp
    go
    drop table #tmp
    go

  5. #5
    Join Date
    Feb 2004
    Posts
    8
    Hi rdjabarov

    Thank you very much for your reply. It works perfectly

    Richard

  6. #6
    Join Date
    Feb 2004
    Posts
    8

    Thumbs up actual implementation

    This is my actual implementation. @servertype is the name of the particular linked server that is to be queried



    if exists (select srvname from master.dbo.sysservers where srvname = @servertype)
    begin
    print @servertype +' already exists and is dropped'
    exec sp_dropserver @servertype
    end



    Richard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •