[SOLVED] Use Invoke-SQLCmd and return number of rows deleted/affected

Use Invoke-SQLCmd and return number of rows deleted/affected

I have a very simple powershell command, where $query is just "delete from mytable"

Invoke-sqlcmd -ServerInstance $databaseserver -Username $databaseuser -Password $databasepassword -Database $databasename -Query $query| Out-File -filePath $output

However, this does NOT output the number of rows affected?

The equivalent in SQLCMD does output the no rows affected:

& sqlcmd -S . -U $databaseuser -P $databasepassword -Q $query    -o $output

Is there any way to get Invoke-sqlcmd to return the number of rows?

Solution

You could call the ROWCOUNT function feature in SQL immediately after your query to do the following:

Select @@ROWCOUNT

Using your PowerShell as an example:

$query = "DELETE FROM MyTable; SELECT @@ROWCOUNT AS DeletedRows"
$Results = Invoke-sqlcmd -ServerInstance $databaseserver -Username $databaseuser -Password $databasepassword -Database $databasename -Query $query| Out-File -filePath $output
Write-Host "Total rows deleted: $($Results.DeletedRows)"

More information on ROWCOUNT @ http://technet.microsoft.com/en-us/library/ms187316(v=sql.90).aspx

In addition, you could take a row count before the deletion of records and do another row count after deletion then do the difference. This will only work on inserted/deleted records and will not work on for records that are updated.

SELECT COUNT( my_table.my_col ) AS row_count