One method is using ADO.NET objects as you would in any .NET application. The PowerShell example below doesn't require SQL tools to be installed.
To execute the query using Windows authentication, specify Integrated Security=SSPI in the connection string:
$connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI";
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString);
$command = New-Object System.Data.SqlClient.SqlCommand("DELETE FROM dbo.YourTable WHERE YourTableID = 1", $connection);
$connection.Open();
$rowsDeleted = $command.ExecuteNonQuery();
Write-Host "$rowsDeleted rows deleted";
$connection.Close();
To execute the query using SQL authentication, specify User ID=YourSqlLogin;Password=YourSqlLoginPassword in the connection string.
$connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;User ID=YourSqlLogin;Password=YourSqlLoginPassword";
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString);
$command = New-Object System.Data.SqlClient.SqlCommand("DELETE FROM dbo.YourTable WHERE YourTableID = 1", $connection);
$connection.Open();
$rowsDeleted = $command.ExecuteNonQuery();
Write-Host "$rowsDeleted rows deleted";
$connection.Close();
In either case, DELETE permissions on the table are required.
I'm not sure of the purpose of the NET USE command in the script you added to your question, unless that is to authenticate to the server in a workgroup environment. Personally, I would just use SQL authentication and remove the NET USE ugliness.
EDIT:
In the case of multiple SELECT statements in the same batch, each will return a separate recordset. This will require invoking NextRecordset if you are using a DataReader, which will return false when no more recordsets are available:
$reader = $command.ExecuteReader();
do {
While($reader.Read()) {
#process row here;
}
} while($reader.NextResult());
Alternatively, you could use a DataAdapter to fill a 'DataSet'. The DataSet will contain a separate DataTable for each resultset:
$da = New-Object System.Data.SqlClient.SqlDataAdapter($command);
$ds = New-Object System.Data.DataSet;
$null = $da.Fill($ds);
foreach($dt in $ds.Tables) {
$dt | Out-GridView;
}
You could also tweak your SQL query to concatenate the results into a single resultset using UNION ALL if the number of columns and data types are identical. Here's an example snippet:
$sqlQuery = @("
SELECT *
FROM DB926.dbo.Version_Solution
WHERE Notes ='9.2.7'
UNION ALL
SELECT *
FROM DB_926.dbo.Version_Solution
WHERE Notes ='9.2.7'";
);
$command = New-Object System.Data.SqlClient.SqlCommand($sqlQuery, $connection);
