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
SET QUOTED_IDENTIFIER ON
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(
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
--select @DBname, @Sname, @Tname
set @s = 'USE ' + @DBname + CHAR(13) + CHAR(10) + 'exec sp_spaceused N''' + @Sname + '.' + @Tname + ''''
INSERT #temp
EXEC sp_executesql @s
select @RID = MIN(RID) 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
select [Dbname], [Sname], [Name],[Rows] from #stats order by Dbname, Sname, Name
SET NOCOUNT OFF
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.
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