How to configure Connection manager

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

How to configure Connection manager

Author

Topic

roshana
Starting Member

31 Posts

Posted - 2010-12-09 : 06:47:52

Hi All,
I have to run one alter statement in 50 servers and 1 database per server. I have the list of servers and database in one table. How can i configure the connection manger to achieve this
eg: execute script1 in SQLserver1 /DB1 then connect to SQLserver2 /DB1 etc till SQLserver50 /DB1

Thanks,
Roshan

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-12-09 : 07:40:33

Loop through the list of servers and databases and perform the required query using OPENROWSET. Pseudo-code:

Declare cur for SELECT server, database FROM table

while ...
  SELECT a.*
  FROM OPENROWSET('SQLNCLI', 'Server=' + @Servername + ';Trusted_Connection=yes;',
     'SELECT Col1
      FROM ' + @databasename + '.dbo.sometable') AS a;

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources