Question

  • Creator
    Topic
  • #2210875

    How to query multiple DBs with teh same name on separate servers?

    Locked

    by monzonc ·

    I have multiple servers where I have this app – the app creates the DBs and tables. The DB and tables are call the same on all of the servers.. How can I query the DBs and tables on all of the server with a single query ? The server themselves have unique names.

    For example I want to search for a trading partner with the ID of 1234567 on all servers under the same table name..

    Thanks,
    Carlos

All Answers

  • Author
    Replies
    • #2843643

      Clarifications

      by monzonc ·

      In reply to How to query multiple DBs with teh same name on separate servers?

      Clarifications

    • #2843629

      Logically, a query can only do one thing at a time

      by robo_dev ·

      In reply to How to query multiple DBs with teh same name on separate servers?

      Do you mean to re-use the same query on each database instance?

      Then the way to do that would be to create a variable in the query that is populated with a table containing your list of database instances. Your code would then need to iterate through the process of sequentially retrieving the variable name, executing the query, then moving on to the next server.

      You could, of course, hard-code the database instance names into the query, but then the query breaks if a server is renamed, and some error recovery code needs to be added so the whole query does not fail if a server were offline.

    • #2843618

      SQLServer?

      by tony hopkinson ·

      In reply to How to query multiple DBs with teh same name on separate servers?

      You ned to add all the servers in as linked servers
      See http://msdn.microsoft.com/en-us/library/ms188279.aspx

      Then it’s just Select * From LinkedServerName.DatabaseName.SchemaName.TableName etc.

      As for querying them all in one query that’s a bit more complicated.

      If they are static links to can hard code it e.g.

      Select * From LinkedServer1.DatabaseName.SchemaName.TableName
      Union
      Select * From LinkedServer2.DatabaseName.SchemaName.TableName
      Union
      ..
      Select * From LinkedServer9.DatabaseName.SchemaName.TableName

      But that’s not what you call future proof…

      After that you are into cursors on system metatables and executing dynamic sql and lots of clever stuff.

      Some sort of replication mechanism back to a central reporting database might be a better option if this is more than an a one off request.

      • #2843559

        Reponse To Answer

        by monzonc ·

        In reply to SQLServer?

        Thanks for the reply back..

Viewing 2 reply threads