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;

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
  $SqlCmd.CommandText = $sqlQuery;
  $SqlCmd.Connection = $SqlConnection;

1 Answer 1


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.