Post reply

sp_AddLinkedServer

  • May 27, 2009 at 6:07 pm

    #210055

    Hi all.

    I have an e-mail report that genrates daily from one of our servers, which basically gathers all the job stats to tell us on one summarized table which jobs have succeeded and which have failed.

    All works fine unless one of the servers it's looking at is restarted, in which case the link to that server drops out.

    I don't want to make permanent links between our various SQL Servers, for security reasons, but what I'd like to do is check that the 3 or 4 servers are linked prior to running the SQL that gathers the data for the report, and then drop the links when finished.

    Obviously, if I try to just add the linked servers and they are already linked, I will get an error, so I need either a way to error trap this or to check for linked servers prior to trying to add them, and only add them if they aren't already linked.

    If anyone has code to accomplish this, it would be much appreciated. I haven't had a chance to search around and see if the answer is already on here... I've got a couple other deadlines making life difficult, and no time to investigate this properly.

    Sorry also if I haven't put it under the right forum category, wasn't sure where a question like this would go.

  • May 27, 2009 at 6:21 pm

    #1001151

    the table sysservers has all the linked servers that have been added;

    select * from sysservers

    with that info, you could do an

    IF NOT EXISTS(SELECT NAME FROM SYSSERVERS WHERE SRVNAME='LINKEDSERVERNAME')

    BEGIN

    --..code to add linked server

    END

    and add it if needed for each of your servers you poll.

    let us know if you need more than that.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 27, 2009 at 7:21 pm

    #1001171

    Thanks a billion for that Lowell. Exactly what I was looking for. Now I can get on with things.

  • May 27, 2009 at 9:29 pm

    #1001187

    Hi again Lowell.

    Where does sysservers exist? In msdb? I can't seem to find it.

  • May 27, 2009 at 10:46 pm

    #1001209

    Hi,

    Sys.sysservers is a internal table. We will not be able to find it either in MSDB or MASTER database.

    If I am not wrong it is similar to one of those SYSHOBT tables.

    Regards

    Gurpreet Sethi

  • May 28, 2009 at 12:22 am

    #1001232

    Yes, was missing the dot between sys and servers.

    All is good now, and my code is working fine.

    No more pesky dropped servers and resulting problems.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply