How to compare to Stored Procedure outputs

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.

  • I can envision a variety of ways that your stored procedure could be operating. Some of them might not play well with invoke-sqlcmd. Can you post your SP? (I hope it isn't too long)

  • Sure, it is just returning one dataset at the end with row counts for replicated tables.  This dataset I need to compare:

     

    USE [MyDB]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

     

    CREATE PROCEDURE [dbo].[usp_MySP]

    AS

    SET NOCOUNT ON

    DECLARE @DBname sysname, @s nvarchar(max), @Tname sysname, @Sname sysname, @RID int

    DECLARE @DBs Table (Dbname sysname) 

     

    IF OBJECT_ID('tempdb..#Tables') IS NOT NULL

    DROP TABLE #Tables

    CREATE TABLE #Tables(

    RID int IDENTITY(1,1), Sname sysname, Tname sysname)

    IF OBJECT_ID('tempdb..#stats') IS NOT NULL

    DROP TABLE #stats

    CREATE TABLE #stats(

    Dbname sysname,

    Sname sysname,

    Name nvarchar(200), 

    Rows char(11),

    [reserved kb] varchar(18),

    [Data kb] varchar(18),

    [index_size kb] varchar(18),

    [Unused kb] varchar(18))

    IF OBJECT_ID('tempdb..#temp') IS NOT NULL

    DROP TABLE #temp

    CREATE TABLE #temp(

    Name nvarchar(200), 

    Rows char(11),

    [reserved kb] varchar(18),

    [Data kb] varchar(18),

    [index_size kb] varchar(18),

    [Unused kb] varchar(18))

     

    insert into @DBs (Dbname)

    select name from master.sys.databases (nolock)

    where is_published = 1

    order by name

     

    select @DBname = MIN(Dbname) from @DBs

    while @DBname is not NULL

    begin

    select @s = 'USE ' + @DBname + CHAR(13) + CHAR(10) + 

    'insert into #Tables (Sname, Tname)

    select s.name, a.name from dbo.sysarticles (nolock) a

    join sys.objects (nolock) o on a.objid = o.object_id

    join sys.schemas (nolock) s on o.schema_id = s.schema_id

    where o.type = ''U''

    order by s.name, a.name' 

    --print @s

    exec sp_executesql @s 

    --select * from #Tables

    select @RID = MIN(RID) from #Tables

    select @Sname = Sname from #Tables where RID = @RID

    select @Tname = Tname from #Tables where RID = @RID

    while @RID is not NULL

    begin

    --select @DBname, @Sname, @Tname

    set @s = 'USE ' + @DBname + CHAR(13) + CHAR(10) + 'exec sp_spaceused N''' + @Sname  + '.' + @Tname + ''''

    --print @s 

    INSERT #temp

    EXEC sp_executesql @s

     

    select @RID = MIN(RID) from #Tables where RID > @RID

    select @Sname = Sname from #Tables where RID = @RID

    select @Tname = Tname from #Tables where RID = @RID

    end

    insert into #stats ([Dbname], [Sname], [Name],[Rows],[reserved kb],[Data kb],[index_size kb],[Unused kb])

    select @DBname, @Sname, [Name],[Rows],[reserved kb],[Data kb],[index_size kb],[Unused kb] from #temp

    truncate table #temp

    truncate table #Tables

    select @DBname = MIN(Dbname) from @DBs where Dbname > @DBname

    end

    select [Dbname], [Sname], [Name],[Rows] from #stats order by Dbname, Sname, Name

    SET NOCOUNT OFF

     

    GO

     

     

  • I don't know. Trying to duplicate what you originally posted against the stored procedure, it returned all the rows. I messed around with the syntax a little to see if I could make it fail, but it never did.

    The only thing I see is that in your original post, you execute "EXEC MyDB.dbo.usp_replication_row_counts", but the stored procedure you sent is named "usp_MySP". Could it be something that simple?

    Have you tried running it through SMO?

    [reflection.assembly]::loadwithpartialname("Microsoft.SqlServer.SMO") | out-null
    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Instance1
    $db = $srv.databases["MyDB"]
    $r = $db.executewithresults($query)
    $r.tables[0]

     

  • Thanks for your help. 

    I just changed the name of the SP, so it is the same stored procedure.

    Tried it in the standard Powershell window (not SQL Server powershell) and it worked!  I could see all the records.

    Next question is how do I compare two datasets from different SQL Server instances?

     

  • The compare-object cmdlet should do it for you.

  • I've tried

    Compare-Object $r1.tables[0] $r2.tables[0]

    and 

    Compare-Object $r1 $r2

    but got no output.  I did a manual comparison and found 5 tables having different row counts, which means the datasets are different.

    Any ideas?

     

    Thanks.

  • I believe you'll have to go to the row level:

    Compare-Object -referenceobject $r1.tables[0].rows -differenceobject $r2.tables[0].rows

  • On second thought, because of the data you have, you may be better off rolling your own.

    $rows1 = $r1.tables[0].rows;
    $rows2 = $r2.tables[0].rows;
    foreach ($row in $rows1) {
       if ($rows2.name -notcontains $row.name) { $row; }
       else {
          foreach ($row2 in $rows2) {
             if ($row2.name -eq $row.name) {
                if ($row2.rows -ne $row.rows) {
                   "==> " + $row.dbname + "." + $row.sname + "." + $row.name + " -- " + $row.rows + " -- " + $row2.rows;
                }
             }
          }
       }
    }

    Then do the same thing comparing $row2 to $rows1. I think Compare-Object will eventually get you there, but you'll have to play with it some.

  • 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