VisiRepli.psm1
|
Function Get_RowCount { Param ( $data_set ) Process { [int] $row_count=-1 if ($data_set -eq $null ) { $row_count = 0 } else { if ($data_set.Count -eq $null) { $row_count=1 } else { $row_count = $data_set.Count } } return $row_count } } Function Verify_Publication { ############################################################# # # Function Verify_publication # -------------------------------------------------------------------- # # --------------------------------------------------------------------- # History: # 2017-12-19 MNI Creation # ############################################################# ## -------------------------------------------------------------------------------- ## Verify publication v1 ## -------------------------------------------------------------------------------- [cmdletbinding()] Param( [parameter(Mandatory=$true,position=0)] [ValidateScript({invoke-sqlcmd -serverInstance:$_ -query:"select 1" })] [string] $subscriber, [parameter(Mandatory=$true,position=1)] [ValidateNotNullOrempty()] [string] $publication , [parameter(Mandatory=$false,position=2)] [switch]$push_to_sub=$false , [parameter(Mandatory=$false,position=3)] [switch]$push_to_pub=$false , [parameter(Mandatory=$false,position=4)] [switch]$hide_zero_values=$false , [parameter(Mandatory=$false,position=5)] $exclude_tables = @() , [parameter(Mandatory=$false,position=6)] [switch]$bulk_fix = $false # use bulk fix ) # end param Process { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } # get publisher $publisher = Get-Publisher -srv $subscriber Test-EmptyString $publisher "Publisher not found" Write-Debug "Publisher: $publisher" # get dbn $dbn = (Get-Publications -publisher $publisher| ? Name -eq $publication ).Database Test-EmptyString $dbn "Table not found in any database (dbn could not be determined) " Write-debug "DBN: $dbn " #validate publication # to do later $articles_qry = "exec sp_visi_Get_repli_articles @publication = '$publication' " $articles = Invoke-Sqlcmd -ServerInstance $publisher -Database $dbn -Query $articles_qry foreach ($article in $articles | Where-Object {$_.article_name -inotin $exclude_tables} ) { Write-Debug $("`nArt: {0} Bidir = {1}" -f $article.article_name , $(-not $article.upload_options)) if ($bulk_fix.IsPresent) { Compare_publisher_subscriber_bulkFix -srv:$subscriber -publication:$publication -table:$article.article_name -check_missing_at_pub:$(-not $article.upload_options) -check_missing_at_sub -hide_zero_values:$hide_zero_values -push_to_pub:$push_to_pub -push_to_sub:$push_to_sub -ErrorAction Stop } else { Compare_publisher_subscriber -srv:$subscriber -publication:$publication -table:$article.article_name -check_missing_at_pub:$(-not $article.upload_options) -check_missing_at_sub -hide_zero_values:$hide_zero_values -push_to_pub:$push_to_pub -push_to_sub:$push_to_sub -ErrorAction Stop } } # end foreach article } #end Process } Set-Alias -Name Verify-Publication -Value Verify_publication function Compare_publisher_subscriber_bulkFix { ############################################################# # # # Function Compare_publisher_subscriber bulk fix # mni 2018-03-22 - createed ############################################################# [CmdletBinding()] Param( [parameter(Mandatory=$true,position=0)] [ValidateScript({invoke-sqlcmd -serverInstance $_ -query "select 1" -ConnectionTimeout 3 })] [string] $srv , [parameter(Mandatory=$false,position=1)] [ValidateNotNullOrEmpty()] [string] $publication , [parameter(Mandatory=$true,position=2)] [ValidateNotNullOrEmpty()] [string] $table , [parameter(Mandatory=$false,position=3)] [switch] $check_missing_at_pub = $false , # check rows missing at publisher (cm) [parameter(Mandatory=$false,position=4)] [switch] $check_missing_at_sub = $false , # check rows missign at subscriber(mag) [parameter(Mandatory=$false,position=5)] [switch] $push_to_pub = $false , # push rows to publisher (mdu @ sub ) [parameter(Mandatory=$false,position=6)] [switch] $push_to_sub = $false , # push rows to subscriber (mdu @ pub ) [parameter(Mandatory=$false,position=7)] [switch] $hide_zero_values = $false ) # end param Process { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } #check switches if (-not($check_missing_at_pub.IsPresent) -and -not($check_missing_at_sub.IsPresent)) { Write-Output "No check parameters have been specified.`nExiting routine" ; Return ; } #legacy var name -- too lazy to fix $pub = $publication #infer dbn $dbn = InferDbnFromPublication -publication $publication Write-debug "DBN: $dbn " if ([string]::IsNullOrEmpty($dbn)) { throw "Table not found in any database (dbn could not be determined) " ; exit 1; } #get publisher $publisher = Get-Publisher $srv -debug:$false if ([string]::IsNullOrEmpty($publisher)) { throw "Publisher not found" ; exit 1; } Write-Debug "Publisher: $publisher" #get objid at publisher ?? Is this still necessary ? $qry = "select object_id('$table', 'U') as objid " $objid = (Invoke-Sqlcmd -ServerInstance $publisher -Database $dbn -Query $qry ).objid Write-Debug $("Objid = {0} " -f $objid ) if ([string]::IsNullOrEmpty($objid)) { throw "Table objectId could not be determined" ; exit 1; } #get sub if ($dbn -eq 'ARIZONACASH') { #get current ou from CASH $qry = "SELECT cou.CR_organizational_unit_GUID as sub FROM CR_organizational_unit AS cou WHERE cou.CROU_locally_used = 1 ; " } else { #get current sub from AZ $qry = " declare @sub int ; select @sub = dbo.fn_get_Current_Sub() ; if @@servername like 'SRV-ACHATS%' select @sub = 1 select @sub as sub ; " } $sub = (Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop -Verbose).sub if ([string]::IsNullOrEmpty($sub)) { "Server = {0} ; Dbn = {1} ; Qry = {2} " -f $srv , $dbn, $qry throw "Subsidiary not found" ; exit 1; } # end if sub is empty Write-Debug "HostName override: $sub" #generate repli meta data @ pub $qry = "EXEC sp_get_repli_metadata @in_publication='$pub',@in_subsidiary_id='$sub',@in_table='$table'" Write-Debug $qry $repliData = (Invoke-Sqlcmd -ServerInstance $publisher -Database $dbn -Query $qry -ErrorAction Stop ) $pk = ($repliData | Where-Object {$_.object_id -eq $objid } ).pk $schema = ($repliData | Where-Object {$_.object_id -eq $objid } ).schema $tableLongName = ($repliData | Where-Object {$_.object_id -eq $objid } ).article_long_name $article = ($repliData | Where-Object {$_.object_id -eq $objid } ).article if([string]::IsNullOrEmpty($pk)) { Throw "Pk could not be determined for table $table" ; Exit 1; } Write-Debug "Table: $tableLongName" Write-Debug "Pk: $pk" $from = ($repliData | Where-Object {$_.object_id -eq $objid } ).from_clause if([string]::IsNullOrEmpty($from)) { Throw "From clause could not be determined for table $table" ; Exit 1; } Write-Debug "From clause: $from" # pull cm data 2 mag $qry = " ---------------------------- -- begin extract data query -- SET QUOTED_IDENTIFIER OFF; IF OBJECT_ID('tempdb.dbo.$article') IS NOT NULL DROP TABLE tempdb.dbo.$article; SELECT TOP 0 $pk INTO tempdb.dbo.$article FROM $dbn.$schema.$article; DECLARE @sql NVARCHAR(MAX) = `" SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; USE $dbn; SELECT $pk $from ;`" INSERT tempdb.dbo.$article EXEC(@sql) At ArizonaCASH; -- -- end extract data query ---------------------------- " $qry = $($qry -ireplace "INNER", "`nINNER" ) $qry = $($qry -ireplace " AND " , "`nAND " ) Write-Debug "Extract data qry=`n $qry " #proceed to execute @ mag Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop -QueryTimeout 100000 -verbose:$false Write-Debug "End pull cm data to mag" #get rows missing @ pub if ($check_missing_at_pub -eq $true) { Write-Debug "Check missing @ Pub" $qry = " ------------------------ -- begin missing @ Pub query -- ;WITH missing_at_cm AS ( SELECT $pk $from EXCEPT SELECT $pk FROM tempdb.dbo.$article ) SELECT x.ROWGUIDCOL as rgd_ , x.$pk as pk_ FROM missing_at_cm AS m JOIN $dbn.$schema.$article AS x ON m.$pk = x.$pk -- -- end missing @ Pub query -------------------------- " Write-Debug $qry $rows = $(Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop ) $rc = Get_RowCount($rows) if (-not( $hide_zero_values.IsPresent -and ($rc -lt 1 ))) { "[{0}].[{1}].[{2}] rows missing at [{3}]: {4} " -f $srv, $dbn, $table, $publisher, $rc } #push2pub (mdu@mag) if ($push_to_pub.IsPresent -and $rc -gt 0 ) { #select candidate col for update to avoid oop error $qry = " SET NOCOUNT ON ; DECLARE @table SYSNAME = '$tableLongName'; DECLARE @xc TABLE ( c SYSNAME) ; INSERT @xc SELECT CONCAT('%', ja.name, '%') FROM dbo.sysmergearticles AS a JOIN dbo.sysmergesubsetfilters AS f ON f.artid = a.artid JOIN dbo.sysmergearticles AS ja ON f.join_nickname = ja.nickname WHERE a.objid = OBJECT_ID(@table,'U') ; IF NOT EXISTS ( SELECT 1 FROM @xc) INSERT @xc SELECT COL_NAME(OBJECT_ID(@table,'U'),1); SELECT TOP 1 c.name as colName FROM sys.tables AS t JOIN sys.[columns] AS c ON t.[object_id] = c.[object_id] JOIN sys.schemas AS s ON s.[schema_id] = t.[schema_id] WHERE t.name LIKE PARSENAME(@table, 1) AND c.column_id > 1 AND c.name LIKE '%%' AND s.name LIKE IsNull(PARSENAME(@table,2),'dbo') AND t.is_ms_shipped = 0 AND c.name NOT LIKE ( SELECT c FROM @xc) " write-debug $qry $xcol = (Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -Verbose -ErrorAction Stop -QueryTimeout 10000 ).colName Write-Debug $( "col used for bulk update = {0}" -f $xcol ) # proceed to update @ subscriber $qry = " ------------------------------- -- bulk update query BEGIN TRAN ;WITH missing_at_cm AS ( SELECT $pk $from EXCEPT SELECT $pk FROM tempdb.dbo.$article ) UPDATE $tableLongName SET $xcol = $xcol FROM $tableLongName AS x JOIN missing_at_cm AS mac ON x.$pk = mac.$pk --ROLLBACK TRAN COMMIT TRAN ------------------------------- " write-debug $qry Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -Verbose -ErrorAction Stop -QueryTimeout 10000 } } #get rows missing @ Sub if ($check_missing_at_sub -eq $true ) { Write-Debug "Check missing @ SUB " $qry = " -- -- begin missing @ Sub query ----------------------------------------------------------- SELECT $pk as pk_ FROM tempdb.dbo.$article EXCEPT SELECT $pk from $table -- -- end missing @ Sub query -----------------------------------------------------------" Write-Debug $qry $rows = Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop -QueryTimeout 10000 $rc = Get_RowCount($rows) $rows_missing_at_subscriber = $rc #save for closing statements if (-not( $hide_zero_values.IsPresent -and ($rc -lt 1 )) ) { "[{0}].[{1}].[{2}] rows missing at [{3}]: {4} " -f $publisher, $dbn, $table, $srv, $rc } #push rows 2 sub (mdu@pub) ######################### if ($push_to_sub.IsPresent -and $rc -gt 0) { $qry = " DECLARE @table SYSNAME = '$tableLongName'; SELECT TOP 1 c.name as colName FROM sys.tables AS t JOIN sys.[columns] AS c ON t.[object_id] = c.[object_id] JOIN sys.schemas AS s ON s.[schema_id] = t.[schema_id] WHERE t.name LIKE PARSENAME(@table, 1) AND c.name LIKE '%%' AND s.name LIKE IsNull(PARSENAME(@table,2),'dbo') AND t.is_ms_shipped = 0 AND c.name NOT LIKE '$pk' AND c.name not like '%subsidiar%'" #$qry $xcol = (Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -Verbose -ErrorAction Stop ).colName Write-Debug $( "col used for bulk update = {0}" -f $xcol ) <# $qry = " BEGIN TRAN SET XACT_ABORT ON; ;WITH mx AS ( SELECT $pk FROM tempdb.dbo.$article EXCEPT SELECT $pk from $tableLongName ) UPDATE ArizonaCASH.$dbn.$tableLongName SET $xcol = $xcol FROM ArizonaCASH.$dbn.$tableLongName AS a JOIN mx ON a.$pk = mx.$pk --ROLLBACK TRAN COMMIT TRAN " write-debug $qry Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -Verbose -ErrorAction Stop -QueryTimeout 15000 #> # create temp table at publisher $qry = "IF OBJECT_ID('$tableLongName') IS NOT NULL DROP TABLE $tableLongName; CREATE TABLE $tableLongName ($pk SYSNAME) " write-debug $qry Invoke-Sqlcmd -ServerInstance $publisher -Database TempDB -Query $qry -Verbose -ErrorAction Stop -QueryTimeout 10000 # populate tenp table at publisher $qry = "INSERT ONECM.tempdb.$tableLongName SELECT $pk FROM tempdb.dbo.$article EXCEPT SELECT $pk from $tableLongName " write-debug $qry Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -Verbose -ErrorAction Stop -QueryTimeout 10000 # proceed to update at publisher $qry = " UPDATE $tableLongName SET $xcol = $xcol FROM $tableLongName AS a JOIN tempdb.$tableLongName as mx ON a.$pk = mx.$pk " write-debug $qry Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -Verbose -ErrorAction Stop -QueryTimeout 15000 } } #end if check_missing_at_mag ####################### #closing statements ####################### #$note = "Rows missing at publisher: $rows_missing_at_publisher; Rows missing at subscriber: $rows_missing_at_subscriber" #$qry = " #EXEC dbo.sp_Save_VisiSys_Log #@Id = '$log_id', #@note = '$note', #@status= '0' , #@rowCount = $($rows_missing_at_publisher + $rows_missing_at_subscriber) " #Invoke-Sqlcmd -ServerInstance $srv -Query $qry -Verbose -QueryTimeout 10000 -ErrorAction stop } # end process } # End function function Compare_publisher_subscriber { ############################################################# # # # Function Compare_publisher_subscriber # Now using sp_get_repli_metaData # mni 2017-03-20 # # mni 2018-03-22 - add publication parameter # - using infer dbn from publication function # - adapt to new pub Archive ( same table Name in 2 diff pubs) ############################################################# [CmdletBinding()] Param( [parameter(Mandatory=$true,position=0)] [ValidateScript({invoke-sqlcmd -serverInstance $_ -query "select 1" -ConnectionTimeout 3 })] [string] $srv , [parameter(Mandatory=$true,position=1)] [ValidateNotNullOrEmpty()] [string] $publication , [parameter(Mandatory=$true,position=2)] [ValidateNotNullOrEmpty()] [string] $table , [parameter(Mandatory=$false,position=3)] [switch] $check_missing_at_pub = $false , # check rows missing at publisher (cm) [parameter(Mandatory=$false,position=4)] [switch] $check_missing_at_sub = $false , # check rows missign at subscriber(mag) [parameter(Mandatory=$false,position=5)] [switch] $push_to_pub = $false , # push rows to publisher (mdu @ sub ) [parameter(Mandatory=$false,position=6)] [switch] $push_to_sub = $false , # push rows to subscriber (mdu @ pub ) [parameter(Mandatory=$false,position=7)] [switch] $hide_zero_values = $false ) # end param Process { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } Write-Debug "Compare_publisher_subscriber Version 3 " [int] $rc = -1 ####################### #check switches ####################### if (-not($check_missing_at_pub.IsPresent) -and -not($check_missing_at_sub.IsPresent)) { Write-Output "No check parameters have been specified.`nExiting routine" ; Return ; } ####################### #write log ####################### $qry = " DECLARE @log_id UNIQUEIDENTIFIER DECLARE @domain SYSNAME = 'Controle_replication' EXEC dbo.sp_Save_VisiSys_Log @Id = @log_id OUTPUT, @Domain = 'Controle_replication', @Module = '$table'; SELECT @log_id as log_id " $log_id = (Invoke-Sqlcmd -ServerInstance $srv -Database 'master' -Query $qry -Verbose -QueryTimeout 10000 -ErrorAction stop ).log_id Write-Debug "Log id = $log_id " ####################### #get publisher ####################### $publisher = Get-Publisher -srv $srv Test-EmptyString $publisher "Publisher not found" Write-Debug "Publisher: $publisher" ####################### # get dbn ####################### $dbn = (Get-Publications -publisher $publisher| ? Name -eq $publication ).Database Test-EmptyString $dbn "Table not found in any database (dbn could not be determined) " Write-debug "DBN: $dbn " ####################### #get publication ####################### $pub = $publication Write-Debug "Publication: $pub " ####################### #get objid at publisher ####################### $qry = "select object_id('$table', 'U') as objid " $objid = (Invoke-Sqlcmd -ServerInstance $publisher -Database $dbn -Query $qry ).objid Test-EmptyString $objid "Table objectId could not be determined" Write-Debug $("Objid = {0} " -f $objid ) ####################### #get sub ####################### #sub (ou if CASH) if ($dbn -imatch 'CASH') { #get current ou from CASH $qry = "SELECT cou.CR_organizational_unit_GUID as sub FROM CR_organizational_unit AS cou WHERE cou.CROU_locally_used = 1 ; " } else { if ($pub -imatch 'Arizona_OU') { $qry = "SELECT dbo.fn_Get_CV_Value('cvCurrentOrganizationalUnit', null) as sub " } else { #get current sub from AZ $qry = " declare @sub int ; select @sub = dbo.fn_get_Current_Sub() ; if @@servername like 'SRV-ACHATS%' select @sub = 1 select @sub as sub ; " } } #Write-Debug $qry $sub = (Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop -Verbose).sub if ([string]::IsNullOrEmpty($sub)) { "Server = {0} ; Dbn = {1} ; Qry = {2} " -f $srv , $dbn, $qry throw "Subsidiary not found" ; exit 1; } # end if sub is empty Write-Debug "HostName override: $sub" ####################### #generate repli meta data @ pub ####################### $qry = "EXEC sp_get_repli_metadata @in_publication='$pub',@in_subsidiary_id='$sub',@in_table='$table'" Write-Debug $qry $repliData = (Invoke-Sqlcmd -ServerInstance $publisher -Database $dbn -Query $qry -ErrorAction Stop ) #write-debug $($replidata |select * ) $pk = ($repliData | Where-Object {$_.object_id -eq $objid } ).pk $schema = ($repliData | Where-Object {$_.object_id -eq $objid } ).schema $tableLongName = ($repliData | Where-Object {$_.object_id -eq $objid } ).article_long_name $article = ($repliData | Where-Object {$_.object_id -eq $objid } ).article if([string]::IsNullOrEmpty($pk)) { Throw "Pk could not be determined for table $table" ; Exit 1; } Write-Debug "Table: $tableLongName" Write-Debug "Pk: $pk" $from = ($repliData | Where-Object {$_.object_id -eq $objid } ).from_clause if([string]::IsNullOrEmpty($from)) { Throw "From clause could not be determined for table $table" ; Exit 1; } Write-Verbose "From clause: $from" ####################### # pull cm data 2 mag ####################### #prepare extract data query $qry = " ---------------------------- -- begin extract data query -- SET QUOTED_IDENTIFIER OFF; IF OBJECT_ID('tempdb.dbo.$article') IS NOT NULL DROP TABLE tempdb.dbo.$article; SELECT TOP 0 $pk INTO tempdb.dbo.$article FROM $dbn.$schema.$article; DECLARE @sql NVARCHAR(MAX) = `" SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; USE $dbn; SELECT $pk $from ;`" INSERT tempdb.dbo.$article EXEC(@sql) At ArizonaCASH; -- -- end extract data query ---------------------------- " $qry = $($qry -ireplace "INNER", "`nINNER" ) $qry = $($qry -ireplace " AND " , "`nAND " ) Write-Debug "Extract data qry=`n $qry " #proceed to execute @ mag Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop -QueryTimeout 100000 -verbose:$false write-debug "End pull cm data to mag" ######################### #get rows missing @ pub ######################### if ($check_missing_at_pub -eq $true) { Write-Debug "Check missing @ Pub" $qry = " ------------------------ -- begin missing @ Pub query -- ;WITH missing_at_cm AS ( SELECT $pk $from EXCEPT SELECT $pk FROM tempdb.dbo.$article ) SELECT x.ROWGUIDCOL as rgd_ , x.$pk as pk_ FROM missing_at_cm AS m JOIN $dbn.$schema.$article AS x ON m.$pk = x.$pk -- -- end missing @ Pub query -------------------------- " Write-Debug $qry $rows = $(Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop ) $rc = Get_RowCount($rows) if (-not( $hide_zero_values.IsPresent -and ($rc -lt 1 ))) { "[{0}].[{1}].[{2}] rows missing at [{3}]: {4} " -f $srv, $dbn, $table, $publisher, $rc } $rows_missing_at_publisher = $rc #save for closing statements ######### #push2pub (mdu@mag) ######### if ( $push_to_pub -and $rc -gt 0 ) { foreach ($row in $rows) { $qry = "exec sp_mergeDummyUpdate '$table' , '$($row.rgd_)'" if ($rc -gt 1) { $rowIndex = $rows.IndexOf($row) + 1 } else { $rowIndex = 1 } "[{0}] {1}/{2} -- {3} " -f $srv, $rowIndex, $rc ,$qry ### proceed to mdu row Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop -Verbose } #end foreach row } #end if push_2_pub } #end if $check_missing_at_pub ######################### #get rows missing @ Sub ######################### if ($check_missing_at_sub -eq $true ) { Write-Debug "Start check missing @ SUB " $qry = " -- -- begin missing @ Sub query ----------------------------------------------------------- SELECT $pk as pk_ FROM tempdb.dbo.$article EXCEPT SELECT $pk from $table -- -- end missing @ Sub query -----------------------------------------------------------" Write-Debug $qry $rows = Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop $rc = Get_RowCount($rows) $rows_missing_at_subscriber = $rc #save for closing statements if (-not( $hide_zero_values.IsPresent -and ($rc -lt 1 )) ) { "[{0}].[{1}].[{2}] rows missing at [{3}]: {4} " -f $publisher, $dbn, $table, $srv, $rc } Write-Debug "End check missing @ SUB " ######################### #push rows 2 sub (mdu@pub) ######################### if ($push_to_sub -eq $true ) { foreach ($row in $rows) { $qry = "DECLARE @rgd UNIQUEIDENTIFIER;SELECT @rgd=ROWGUIDCOL FROM $table WHERE $pk='$($row.pk_)';IF @rgd IS NOT NULL EXEC sp_mergeDummyUpdate '$table' , @rgd " if ($rc -gt 1) { $rowIndex = $rows.IndexOf($row) + 1 } else { $rowIndex = 1 } "[{0}] {1}/{2} -- {3} " -f $publisher, $rowIndex, $rc ,$qry ### proceed to mdu row Invoke-Sqlcmd -ServerInstance $publisher -Database $dbn -Query $qry -ErrorAction Stop -Verbose } #end foreach row } #end if } #end if check_missing_at_mag ####################### #closing statements ####################### $note = "Rows missing at publisher: $rows_missing_at_publisher; Rows missing at subscriber: $rows_missing_at_subscriber" $qry = " EXEC dbo.sp_Save_VisiSys_Log @Id = '$log_id', @note = '$note', @status= '0' , @rowCount = $($rows_missing_at_publisher + $rows_missing_at_subscriber) " Invoke-Sqlcmd -ServerInstance $srv -Query $qry -Verbose -QueryTimeout 10000 -ErrorAction stop } # end process } # End function #New-Alias -Name Compare_publisher_subscriber -Value Compare_publisher_subscriber_v3 Set-Alias -Name Verify-Table -Value Compare_publisher_subscriber Export-ModuleMember -alias * -function * |