Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: Querying a linked server with local database...

    I would like to query a linked server and join that database to my local database. I finally was able to get it to work and I got this query to work:

    Code:
    select * from openquery([hcda-storagesrv\laserfiche], 'select * from toc where parentid=358')
    I would like to join one of my tables from my local database to the query. How can I do this? Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am not certain this will work. I have not used linked servers for a few years, but can you try....


    Code:
    select X.[ID],X.col1,my_othertable.col2 
    from openquery([hcda-storagesrv\laserfiche], 'select * from toc where parentid=358') X
    JOIN my_othertable
    ON X.[ID] = my_othertable.[ID]
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2005
    Posts
    165
    Worked perfectly! Thank you! Is there a better alternative to Linked Servers?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    depends on your needs. I bet this will perform not so great, but if you need up to the minute data and you do not want fuss with replication, linked servers are the way to go.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2005
    Posts
    165
    Thanks. I'll just stick with Linked Servers for now.

  6. #6
    Join Date
    Mar 2008
    Posts
    33
    If the linked servers are killing your performance too much consider perhaps using mirroring, replication, log shipping or something else of the sort (custom job running every X...) to copy your data from [hcda-storagesrv\laserfiche] to your other server. That way you can do your joins without using linked server.

    You don't have to replicate everything, just select the few tables/columns you actually need and replicate those. You can make it near real time if you want.

    if performance isn't an issue I would stick with linked server,

Posting Permissions

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