How to compare to Stored Procedure outputs

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