Post reply

Connecting other sql server database from sql query

  • April 22, 2014 at 11:57 pm

    #292647

    Hi ,

    I am writing stored procedure in sql server called SERVER1 and inside same procedure i want to access another server called SERVER2 to get data from particular table. How can i do this?

    Please help me out with this... Any simple example will help a lot...

    Any help will be appreciated..

    Thanks,

  • April 23, 2014 at 12:51 am

    #1707966

    First of all, you need to link the two servers (under server objects -> linked servers-> New linked server)

    Then query the table in much the same way you would any other table:-

    SELECT col1 ,

    col2 ,

    col3

    FROM server2.databasename.schemaname.object as LinkedTable

    Note the four part naming:- server, database,schema + object as opposed to the format you might be used to.

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • April 23, 2014 at 3:39 am

    #1708008

    Another way to do it is to use OPENQUERY. There are examples at the link.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 23, 2014 at 7:12 am

    #1708084

    Grant Fritchey (4/23/2014)

    Another way to do it is to use OPENQUERY. There are examples at the link.

    Or OPENROWSET as referenced at the bottom of the link 🙂

    Far away is close at hand in the images of elsewhere.
    Anon.

  • April 23, 2014 at 7:59 am

    #1708108

    David Burrows (4/23/2014)

    Or OPENROWSET as referenced at the bottom of the link 🙂

    Yeah, this. I said OPENQUERY, but OPENROWSET was what I was thinking about.

    Thanks David.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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