Hi All,

I am trying to get the details of new databases created on a weekly basis. My Multiserver connection manager is not working, throwing the below error.

Test Connection failed because of an internal error in intializing provider. Login timeout expired

An error has occured while establishing a connection to the server. when connecting to sql server 2005,

this failure may be becuase by the fact that under default settings sql server does not allow remoted connections.

Named pipes provider: Could not open a connection to sql server [53]

SQL Server Version: 2005 Developer edition

Services enabled and running : SQL Server, SQL Server Browser, SQL Full text search

Network settings: TCP\IP, Named Pipes, Shared memory

--------------

Followed the below steps.

1. Created a sql server lists table with sql full names.

like row1 KRN045

row2 KRN045\TEST

2. Populating the ADO variables using execute sql task with full result set. Result variable is SQL_RS.

Scope: Package

Data Type: Object

Value: System.Object

Connection manger: Database: Monitor, Server: KRN045, SQL- Select SQLFullName from tblvwSQLServer

3.Right click on the empty panel for the Control Flow Tab. Select Variables. Then select the Add Variables icon and add the following two variables.

SRV_Conn

Scope: Package

Data Type: String

Value: KRN045

4.Right Click in Connection Managers Panel

* Select New OLE DB Connection

* Click New

* Enter Server name that has the KRN045

* Click OK

* Click OK

* Right Click on the newly added connection

* Select Rename

* Enter MultiServer

Now we need to customize this connection

* Right Click on MultiServer

* Select Properties

* Change the Following Properties

*

o Expressions click ... box

o

+ Click Property

+ Click Drop Down Arrow

+ Select ServerName

+ In the expression box type @[User::SRV_Conn]

+ Click OK

o Initial Catalog change to Master

Getting the above error when i try test connection.. Please let me know where i am going wrong.