Post reply

Determine if a Server is a Linked Server

  • January 8, 2009 at 10:00 am

    #129037

    I want to know if there's any reliable way to determine if a server is a linked server on both SQL Server 2000 and SQL Server 2005. I noticed that there only appears to be one field that may give me this information in sysservers but it appears to behave differently in SQL Server 2000 and SQL Server 2005.

    After much research I came across this article: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=142272. I don't know what a "remote" server is. I thought a server was either local or you could create a linked server to another server. If someone could clarify that would help.

    The only other way I thought of doing it was comparing the srvname values against @@SERVERNAME and removing the match. I figure all the rest of the names returned would be the linked servers.

    Input? Advice?

    Thanks,

    John

  • January 8, 2009 at 11:01 am

    #923565

    I wasn't aware of that issue... bizarre!

    You might just have to use serverproperty('servername') instead of @@server

    @@server can be out-of-date if you rename a server incorrectly, but your thinking looks right. If you do that also consider the replication server: repl_distributor

    1 more thing... consider sys.servers as a source of truth for SQL 2005 maybe?

    I never looked to see if it was right or not in the case where sysservers isn't... but there's always more than one way to skin a cat.

    ~BOT

    Craig Outcalt


    Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
    My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

  • January 8, 2009 at 10:34 pm

    #923913

    John (1/8/2009)

    I want to know if there's any reliable way to determine if a server is a linked server on both SQL Server 2000 and SQL Server 2005. I noticed that there only appears to be one field that may give me this information in sysservers but it appears to behave differently in SQL Server 2000 and SQL Server 2005.

    After much research I came across this article: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=142272. I don't know what a "remote" server is. I thought a server was either local or you could create a linked server to another server. If someone could clarify that would help.

    The only other way I thought of doing it was comparing the srvname values against @@SERVERNAME and removing the match. I figure all the rest of the names returned would be the linked servers.

    Input? Advice?

    Thanks,

    John

    select * from sys.servers

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • January 8, 2009 at 10:37 pm

    #923914

    SQL Server 2000 and SQL Server 2005

Viewing 4 posts - 1 through 3 (of 3 total)

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