accessing tables on one server from a different server

Question

accessing tables on one server from a different server

tariquer

SSC Eights!

Points: 811
More actions
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.

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

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

YB SSC Enthusiast Points: 171 More actions · Answer 1

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.

tariquer SSC Eights! Points: 811 More actions · Answer 2

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

Omri Bahat-228920 Mr or Mrs. 500 Points: 540 More actions · Answer 3

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.

tariquer SSC Eights! Points: 811 More actions · Answer 4

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

tariquer SSC Eights! Points: 811 More actions · Answer 5

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