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

• george.auckland 44813

  Old Hand

  Points: 365

  More actions

  November 7, 2012 at 1:27 pm

  #284397

  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

• Laerte Poltronieri Junior-367636

  Hall of Fame

  Points: 3314

  More actions

  November 18, 2012 at 1:01 pm

  #1560118

  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 !!![/url]