Post reply

accessing tables on one server from a different server

  • March 21, 2006 at 11:50 am

    #90723

    I have read access to our remote server (connecting to it by IP through enterprize manager) that I have to run reports on. I also have a local SQL server in the office. i need to be able to gather data from multiple databases on the remote server and create reports for the finance department. the databases in the remote server all have the same table structures (ex: db1.account table is the same as db2.account table and db3.account table). is there a way to loop through the databses on the remote server and get to the tables and their data? i need to create denormalized temporary tables(overnight batch process) on the local server for reporting.

  • March 22, 2006 at 8:28 am

    #628114

    If I understand your concern, I think all you really need to do is use a "union" to gather all the information required for the report you are trying to run.

    You can use the example below to:

    Select 'TableName1' as TableName, account, ... from [111.222.33.4].databasename.schema.tablename 

    union

     Select 'TableName2' as TableName, account, ... from [444.555.66.7].databasename.schema.tablename 

    union

     Select 'TableName2' as TableName, account, ... from [777.888.99.0].databasename.schema.tablename 

    etc...

    You can also create a "link" to all the servers from the "local server", this will eliminate the use of the IP address and even the database name specification, if directly connected to that database.

    I hope I answered your question.

  • March 22, 2006 at 2:31 pm

    #628230

    I think you have. I just was trying to see if there is an easier way then to go through all the db. but this will definitely work.

    Thank you very much

  • March 22, 2006 at 3:22 pm

    #628245

    You can connect to all databases (either on the same server or on different servers) and run the query/script and get aggregated results using tools such as SQL Farm Combine. The aggregation is then perfomed on the client side and the load is distributed among the queried database, thus being significantly more efficient.

    Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.

  • March 29, 2006 at 7:16 am

    #629475

    I am stuck on this again...

    now it looks like new databases will be added to the remote server almost every weekend. it could even be more then 1 thats added. i dont want to have to maitain the database list all the time. is there any way to say:

    for all the databases in myRemoteServer

         select clientName from tblClientList

         insert the result in myLocalServer.tempTable

    loop

    so by the end of this i have a tempTable full of clietnames from all the databases on myRemoteServer

  • March 30, 2006 at 4:09 pm

    #629987

    Does anyone know if the stored rocess sp_MSforeachdb will work? I have not found any examples of that so far.

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

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