Script to loop through SQL Server Instances and pass Instance Name to Connection String

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » Powershell » Script to loop through SQL Server Instances...

Script to loop through SQL Server Instances and pass Instance Name to Connection String

Rate Topic

Display Mode

Topic Options

Author

Message

george.auckland 44813

Posted Wednesday, November 07, 2012 1:27 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 29, 2013 11:36 AM
Points: 1, Visits: 47

I am trying to use a SQL Table to loop through SQL Instances and pass the Instance to a script:

$DestinationSQLServer = 'coaidera01\idera'
$DestinationDBName = 'CTM_Monitoring'
$DestinationQry = "select InstanceName from COA_SQLServerInstanceInfo WHERE InstanceName Like 'coaidera01\idera'"

$SQLSvr = get-SqlData -sqlserver $DestinationSQLServer -dbname $DestinationDBName -qry $DestinationQry

$SQLSvr | foreach {

$myserver = $_

$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $myserver;

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

I get this error: The following exception was thrown when trying to enumerate the collection: "Failed to connect to server System.Data.DataRow.".

Any Clues

Post #1382156

Laerte Poltronieri...

Posted Sunday, November 18, 2012 1:01 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 19, 2013 10:29 AM
Points: 156, Visits: 732

If you get-member in get-sqldata you will see that it returns a System.Data.DataRow and the properties related to your query. This is what you need to use :

In you query,the name of the Instance is InstanceName rigth ?

Change you script to :

$myserver = $_.InstanceName

and will work :)

$hell your Experience !!!

Post #1386090

« Prev Topic | Next Topic »

Permissions