Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Answered: Linking Database Tables on different servers?

    Is this recommended, any major risk?

    I have two production servers with two different databases and I was thinking about using Linked Servers, but never did this before. Any thoughts on this?

    I'm nervious but I realllllly need to do this to bounce two codes against each other to return the delta for an ETL load.

    Found this stored procedure

    sp_addlinkedserver('servername')

    Would you just execute this and then run your query after the SP? Sorry total rookie
    Last edited by VLOOKUP; 10-16-15 at 16:51.

  2. Best Answer
    Posted by Pat Phelan

    "As the maps of the 1400s used to say about 100 miles off the coast of Europe... "There be dragons here"

    It is possible to create linked servers and to link objects from one server to another. I do this in virtual machines for demos all of the time, but those machines run on a single host that is 100% under my control. It does NOT work well in the real world, especially from 17:30 until about 06:30... Avoid this if you have any desire for a home life!

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As the maps of the 1400s used to say about 100 miles off the coast of Europe... "There be dragons here"

    It is possible to create linked servers and to link objects from one server to another. I do this in virtual machines for demos all of the time, but those machines run on a single host that is 100% under my control. It does NOT work well in the real world, especially from 17:30 until about 06:30... Avoid this if you have any desire for a home life!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    To add to Pat's warnings, I have just had to diagnose a problem with a linked server. It is not fun. Since a linked server depends on libraries (ADO.NET, OLEDB, ODBC, etc.), any problem with these libraries will generally manifest itself by hanging all other linked servers. I presume, there is a pop-up box trying to be visible somewhere, but with SQL Server being a service, there is no where to display such a pop-up box, and no way to click, OK, Cancel, or whatever else it wants. This means there is a process INSIDE SQL Server that is hung with no hope of return.

    The only solution in these cases is to restart the service.

  5. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Is there a way to tell if the server is linked? I tried this before you messaged back and it said it linked but failed :/

    I'd like to look and see, anyway to tell?

  6. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by MCrowley View Post
    The only solution in these cases is to restart the service.
    Just to add interest, if you hang one of the providers (such as ADO), you can leave the Windows machine in a state where any attempt to access that provider will hang until after Windows itself restarts which is a lot more intrusive and "messy" than just having the SQL Service restart!

    Quote Originally Posted by VLOOKUP View Post
    Is there a way to tell if the server is linked?
    That depends on how lucky you feel and how badly you want to know. If you really, truly need to know then:
    1. Reboot Windows
    2. Open SSMS (SQL Server Management Studio).
    3. Try to access the Linked-Server
    4. If you are completely successful, then the Linked-Server is fully functional
    5. If you get an error message, note it and attempt to work things out
    6. If you hang without an error message, declare utter failure.
    7. If you weren't completely successful, then restart Windows a second time
    8. Remove the linked server

    At this point, your SQL Server ought to be stable again.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #6
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    SELECT * FROM sys.servers WHERE name = N'AccessDataSource' (Of course I put my server name here).

    Nothing returned I am good. I'm not able to query from the other server. I tried to link through the GUI not a SP.

    I was just looking for a script to look for linked servers

    When trying through the GUI it said a link had occured but an error in the connection happened do you want to keep the link, I said NO.
    Last edited by VLOOKUP; 10-16-15 at 18:20.

  8. #7
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Moving on to Access, bring the tables into here and doing my queries in Access. No more attempting to Link Servers.
    Last edited by VLOOKUP; 10-16-15 at 19:00.

  9. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It looks like my most recent problem with linked servers was with an Oracle user whose password had expired. I can catch the error in ADO.NET, but apparently the OLEDB driver does not report that as an error (as the user is expected to type in a new password, which is expected behavior).

    The process accessing the linked server had managed to soak up enough worker threads that other traffic on the server was having some major blocking issues. At least that is my theory. I was only so far into delving into the sys.dm_os_* DMV's to prove that out, when the call came "just fix the dang thing, this isn't a science experiment!!!" Typical users.

  10. #9
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Trust me I am sold on not doing this. In fact is there a way to check for this in the future? Like I said earlier I attempted to connect them but it wouldn't connect correctly so I selected don't link (Tried through the GUI)..... I ran a few queries to look for linked servers on the databases and nothing returned except for a message saying use SP_X to set one up, this told me that one wasn't set up on our server so that's the way it will stay. See I listen!

Posting Permissions

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