How to retrieve tables from multiple servers to a central server?

Question

Post reply

How to retrieve tables from multiple servers to a central server?

Vivien Xing

SSChampion

Points: 12297
More actions
June 26, 2008 at 4:24 pm

#126107

I need to retrieve info from tables in msdb and load data into a database in a central server. MSDB are from multiple servers (SQL2000 & SQL2005). I need to automate this process. Using linked server is not an option in my case. How can I do it?

Viewing 11 posts - 1 through 10 (of 10 total)

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

jezemine SSCrazy Eights Points: 8059 More actions · Answer 1

you could use OPENQUERY or OPENROWSET

---------------------------------------
elsasoft.org

Vivien Xing SSChampion Points: 12297 More actions · Answer 2

But openquery needs linkedserver. This is not what I want.

Angie Duncan Newbie Points: 5 More actions · Answer 3

I've pulled information from servers that are not linked using a DTS package. But all of those servers are 2000. I don't know if SSIS packages can support 2000 and 2005 databases.

MannySingh SSChampion Points: 12465 More actions · Answer 4

yes DTS or SSIS is a good approach and secure.

SSIS supports 2000 Databases.

Maninder
www.dbanation.com

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 5

Check this series of articles out, he shows how to dynamically connect to N number of servers, you simple populate a servername table to be used by an ADO connection and it also details central error logging. Following this will give you exactly what you need, he even kindly supplies the package so you can use\alter it for your own needs.

http://www.sqlservercentral.com/articles/Integration+Services/61621/

Andrew

jezemine SSCrazy Eights Points: 8059 More actions · Answer 6

Vivien Xing (6/26/2008)
But openquery needs linkedserver. This is not what I want.

OPENROWSET does not require a linked server. requires just the connection string to the remote.

---------------------------------------
elsasoft.org

Vivien Xing SSChampion Points: 12297 More actions · Answer 7

jezemine (6/26/2008)
Vivien Xing (6/26/2008)
But openquery needs linkedserver. This is not what I want.
OPENROWSET does not require a linked server. requires just the connection string to the remote.

This may help. Can I use windows authentication instead of userid/password? I have a table contains many server entries. I need to loop each server and get the data from each msdb to a central table.

Vivien Xing SSChampion Points: 12297 More actions · Answer 8

andrewkane17 (6/26/2008)
Check this series of articles out, he shows how to dynamically connect to N number of servers, you simple populate a servername table to be used by an ADO connection and it also details central error logging. Following this will give you exactly what you need, he even kindly supplies the package so you can use\alter it for your own needs.
http://www.sqlservercentral.com/articles/Integration+Services/61621/
Andrew

This is a good link. However, my central server is SQL2000. I can not use SSIS in this case.

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 9

[font="Verdana"]Go for linked server, create linked server and make use of this linked server to issue select statement..[/font]

Regards..Vidhya Sagar
SQL-Articles

Vivien Xing SSChampion Points: 12297 More actions · Answer 10

Finally use OSQL -SserverN -E -iMyinput.sql to loop through all my servers and append the output to the same output file, then load the file to a table.