Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Location
    Bangalore, India
    Posts
    7

    Unanswered: Need help to retrieve data from two servers

    Hi,

    I am a newbie to SQL and learning as I go.

    I am currently trying to write a SELECT statement that returns all the data including the null values from 2 identical tables(job) in 2 different SQL databases on 2 different SQL servers.

    I have read some threads but was not useful as they were just a brush up saying they have done something. Well here goes the scenario:

    Server1>db1>table1 [job]
    Server2>db2>table2 [job]

    the structure of the tables are same.

    I tried to use a Union ALL

    Select * from [server1].db1.table1
    UNION ALL
    Select * from [server2].db2.table2

    the server was not able to identify [server2].db2.table2.

    What is the best way to retrieve this information. We need this info to be used in Framework Manager, to create reports in Cognos 8.

    Hope the question is clear and will appreciate if anyone can clearly tell me the steps to follow to get this data.

    Thanks in advance,
    Rahul

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Use linked servers:

    sp_addlinkedserver (Transact-SQL)
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    And if it is already a linked server, you need to use the four-part naming convention:

    server2.db2.schemaowner.table2

    schemaowner is typically dbo but can be something different.

  4. #4
    Join Date
    May 2009
    Location
    Bangalore, India
    Posts
    7
    I dont have the priveleges to add linked server as I am not a DBA. But i checked whether these servers are linked and its not. Is there any other way to view this other than this method.

    Appreciate the help.

    Thanks,
    Rahul

  5. #5
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    Afraid not, not within SQL anyway. Maybe utilizing Access or VB or som other tool but you'll still need valid credentials to attach to the servers/databases.

    Edit: The easiest thing would be to ask the DBA to setup the linked server - IMO.

Posting Permissions

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