Sql-server – Powershell – SQL Server – connectionstring in loop for multiple Instances

powershellsql servert-sql

Thisis part of a bigger script which finds DatabaseFiles on a SQL Server machine (multiple instances).

Following should just return all files for 3 instances.

The server is called V3000801 and there is one default instance + 2 named instances on there. It's ok with me if either default or named doesn't work I'll work around this alone (most likely create a flag and do default with another connection string).

$SqlCmd.ExecuteNonQuery() just returns -1 which does not make any sense for me.

Thanks for the help 

for($i=0;$i -lt $instances.Length;$i++){
  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
  $Server= "V3000801\"+$instances[$i];
  $SqlConnection.ConnectionString = "Server = $server ; Database = master; Integrated Security = sspi;trusted_connection=true";
  $sqlQuery="SELECT physical_name FROM sys.master_files;";

  Write-Host $SqlConnection.ConnectionString;

  $SqlConnection.Open();
  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
  $SqlCmd.CommandText = $sqlQuery;
  $SqlCmd.Connection = $SqlConnection;
  $SqlCmd.ExecuteNonQuery();
  $SqlConnection.Close();
}

Best Answer

The return value of -1 makes sense when you consider what ExecuteNonQuery() does. It returns the number of rows affected or -1 in case of set nocount on or non-updating queries.

As per documentation,

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. ... For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Try .ExecuteReader() instead.

Related Question