How to compare to Stored Procedure outputs

Roust_m
over 3 years ago

Hi,

I have two SQL instances and need to run the same stored procedure against each and compare the results, ideally to output the differences if any.

This is what I've created so far:

$Instance1 = 'MYDEV\I1'

$Instance2 = 'MYDEV\I2'

SL SQLSERVER:\SQL\"$Instance1"\Databases

$query = "EXEC MyDB.dbo.usp_replication_row_counts"

$q1 = invoke-sqlcmd -query $query 

 

SL SQLSERVER:\SQL\"$Instance2"\Databases

$q2 = invoke-sqlcmd -query $query 

When I try to output the $q1 and $q2 variables, I get empty output.  The executed stored procedure produces a record-set of 383 records and four columns: Database name, Schema Name, Table Name and Row count.

Besides I need to run this from a regular Powershell window.  The script above somewhat works only in SQL Server Powershell window.

Any ideas?

 

Thanks.

Parents
No Data
Reply
  • over 3 years ago

    This is what I ended up doing:

    foreach ($row1 in $rows1) {

         foreach ($row2 in $rows2) {

             If ($row2.DBname -eq $row1.DBname -And $row2.Sname -eq $row1.Sname -And $row2.name -eq $row1.name) {

            if ($row2.rows -ne $row1.rows) {

    Write-Output "$($row1.Dbname), $($row1.Sname), $($row1.name), $($row1.Rows), $($row2.Rows)" 

    }

    }

         }

    }

    Thanks for your help

Children
No Data