VisiRepli

0.9.8

VisiRepli.psm1

Function Export-StagingToDb{ 
    [CmdletBinding(SupportsShouldProcess)] 
    Param( 
        [parameter(Mandatory=$true,position=0)]
        [ValidateNotNullOrempty()]
        [string]$Destination 
        ,
        [parameter(Mandatory=$true,position=1)]
        [ValidateNotNullOrempty()]
        [string]$StagingDatabase 
        ,
        [parameter(Mandatory=$false,position=2)]
        [string]$tableRegex = '.*' 
        ,
        [parameter(Mandatory=$false,position=2)]
        [string]$destinationDatabase 
    ) # end param
    Begin { 
            If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                $DebugPreference = "Continue" 
            } 
            else { 
                $DebugPreference = "SilentlyContinue" 
            } 

            if([string]::IsNullOrEmpty($destinationDatabase)){
                $destinationDatabase = Get-PublicationDbn -Publication $($StagingDatabase -replace 'Staging','') 
            }
            Write-Verbose "Destination database = $destinationDatabase " 
    
            ($logFile  = Join-Path -Path "$env:LOCALAPPDATA\Log" -ChildPath "StagingToDB - $stagingDatabase to $destination - $(get-date -UFormat %Y-%m-%dT%H-%M-%S).log" ) 
            try{stop-transcript|out-null}
            catch [System.InvalidOperationException]{}
            if ($VerbosePreference -eq "Continue" ) { 
                Start-Transcript $logFile } 
            else { 
                Start-Transcript $logFile | Out-Null 
            } 
        } 
    Process {
        $params =@{
            SQlInstance  = $destination   
            Database = $StagingDatabase
        }
        if ($PSCmdlet.ShouldProcess($StagingDatabase,"Copy [$tableRegex] to [$destination].[$destinationDatabase]")){ 
            Get-DbaDbTable @params | Where-Object name -match $tableRegex | Copy-DbaDbTableData @params -DestinationDatabase $destinationDatabase -KeepNulls -KeepIdentity   
        } 
    }
    End {
        ## stop transcript
        try{
            stop-transcript|out-null
        }
        catch [System.InvalidOperationException]{}
    }
} 






Function Get-RepliHostName{ 
[cmdletbinding()] 
Param( 
    [parameter(Mandatory=$true,position=0)]
    [ValidateNotNullOrempty()]
    [string] $Subscriber 
    ,
    [parameter(Mandatory=$true,position=1)]
    [ValidateNotNullOrempty()]
    [string] $Publication 
) # end param
    Begin { 
        If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
            $DebugPreference = "Continue" 
        } 
        else { 
            $DebugPreference = "SilentlyContinue" 
        } 
    } 
    Process {
        Write-Verbose "Subscriber = $subscriber" 
        Write-Verbose "Publication = $publication" 
        switch -regex ($Publication) { 
            '(ItemComp|ConfigComp)' {$qry = 'SELECT Arizona.dbo.fn_Get_Company(abs(Arizona.dbo.fn_get_current_sub())) as hst_name'} 
            '(Stock|Archive|Document|Address|Azconfig|ConfigSub|Inventory)' { $qry = "select Abs(Arizona.dbo.fn_get_current_sub()) as hst_name"} 
            '(CashOu)' {$qry = 'SELECT cr_organizational_unit_guid as hst_name FROM ArizonaCASH.dbo.CR_organizational_unit cou WHERE crou_locally_used =1'} 
            '(CashSub)' {$qry = 'SELECT CROU_subsidiary as hst_name FROM ArizonaCASH.dbo.CR_organizational_unit cou WHERE crou_locally_used =1'} 
            '(CashComp)' {$qry = 'SELECT crsub.CRSUB_company as hst_name FROM ArizonaCash.dbo.CR_subsidiary AS crsub WHERE crsub.CR_subsidiary_GUID = ArizonaCash.dbo.fn_Get_current_sub_CASH()'} 
            '(VisiSys)' {$qry = 'SELECT @@servername'} 
            default {Throw "Publication name $Publication not valid"; exit 1; }
        } # end switch
        Write-Debug $qry 
        (invoke-sqlcmd -ServerInstance:$subscriber -Query:$qry).hst_name 
    }
}         



function Export-Publication {
    [CmdLetbinding()]
        Param( 
            [parameter(Mandatory=$false,position=0,ValueFromPipeline=$true)]
            [string]$Publication
            ,
            [parameter(Mandatory=$true,position=1)]
            [ValidateNotNullOrEmpty()]
            [string]$Destination 
            ,
            [parameter(Mandatory=$true,position=2)]
            [ValidateNotNullOrEmpty()]
            [string]$repliHostName  
            ,
            [parameter(Mandatory=$false,position=3)]
            [ValidateNotNullOrEmpty()]
            [string]$Source 
            ,
            [parameter(Mandatory=$false,position=4)]
            [ValidateNotNullOrEmpty()]
            [switch]$DisableRemoveDuplicates 
            ,
            [parameter(Mandatory=$false,position=5)]
            [ValidateNotNullOrEmpty()]
            [string]$TableRegex='.*'

        ) 
        Begin{
            If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                $DebugPreference = "Continue" 
            } 
            else { 
                $DebugPreference = "SilentlyContinue" 
            }
        }
        Process {
            ## start transcription
            ($logFile  = Join-Path -Path "$env:LOCALAPPDATA\Log" -ChildPath "Export publication - $publication to $destination - $(get-date -UFormat %Y-%m-%dT%H-%M-%S).log" ) 
            try{stop-transcript|out-null}
            catch [System.InvalidOperationException]{}
            if ($VerbosePreference -eq "Continue" ) { 
                Start-Transcript $logFile } 
            else { 
                Start-Transcript $logFile | Out-Null } 

            # get source
            if ([STRING]::IsNullOrEmpty($source)) { 
                $source = Get-Publisher -srv $Destination
            } 
            # get src & tgt dbn
            $sourceDbn = Get-PublicationDbn -Publisher $source -Publication $Publication 
            Write-Verbose "Publication Dbn = $sourceDbn" 
    
            # drop and recreate target staging db
            $destinationDbn = "Staging$Publication"
            Write-Verbose "Destination dbn = $destinationDbn" 
            $qryCreateDb = "
            IF DB_ID('$destinationDbn') IS NOT NULL
            BEGIN
                EXEC sp_killAll '$destinationDbn' ;
                DROP DATABASE [$destinationDbn];
            END
            EXEC sys.sp_executeSQL N'CREATE DATABASE [$destinationDbn]'
            EXEC [$destinationDbn].dbo.sp_visi_create_user @user = 'GPPH\SSI-DevSQL', @dbRoleList = 'db_dataReader';"
            Write-Debug $qryCreateDb 
            Invoke-Sqlcmd -ServerInstance $Destination -Query $qryCreateDb 
            
            # get tables to export
            $tablesQry = "EXEC sp_get_repli_metadata @in_subsidiary_id = '$repliHostName', @in_Publication = '$Publication', @in_table = '%', @repli_meta_data_version = 1, @in_debug = 0" 
            Write-Debug "Tablesqry = $tablesQry" 
            $tables = invoke-sqlcmd -ServerInstance $source -Database $sourceDbn -Query $tablesQry  
    
            # export schemas
            $srvx = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $source -ErrorAction Stop 
            $schemas = $srvx.databases[$sourceDBN].Schemas | Where-Object IsSystemObject -eq $False | Where-Object Name -in $tables.Schema 
            $schemas | Export-DbaScript -Passthru | ForEach-Object { Invoke-Sqlcmd -ServerInstance $Destination -Database $destinationDbn -Query $_ } 
    
            # process tables
            foreach ($tbl in $tables | Where-Object article -match $TableRegex | Where-Object az_type -gt -1 | Select-Object -first 200 | Sort-Object article_long_name ) {
                write-box $tbl.article_long_name
                ($xQry = "SELECT $($sourceDbn + '.' + $tbl.article_long_name + '.*') `n" +  $($tbl.from_clause -replace $tbl.schema , $($sourceDbn + '.' +$tbl.schema) -replace ' dbo\.' , $(' ' + $sourcedbn +'.'+ 'dbo.')))
                $params = @{
                    SQLInstance = $Source
                    Destination = $Destination 
                    Database = $sourceDbn 
                    DestinationDataBase = $destinationDbn
                    Table = $($tbl.article_long_name)
                    Truncate = $true 
                    AutoCreateTable = $true 
                    query = $xQry 
                    BulkCopyTimeout = 1000
                    KeepNulls = $true 
                } 
                Copy-DbaDbTableData @params 
                Clear-DbaConnectionPool 
    
                # clear duplicates
                if ($DisableRemoveDuplicates.IsPresent) { 
                    #Do nothing
                    Write-Debug "Remove duplicates has beendisabled" 
                }
                else { 
                    $removeDuplicatesQry = "
                    DELETE $destinationDbn.$($tbl.article_long_name)
                    FROM $destinationDbn.$($tbl.article_long_name) AS a
                    JOIN $sourceDbn.$($tbl.article_long_name) AS x ON x.$($tbl.pk) = a.$($tbl.pk) " 
                    write-debug $removeDuplicatesQry 
                    Invoke-Sqlcmd -ServerInstance $Destination -Database $destinationDbn -Query $removeDuplicatesQry -QueryTimeout 10000 
                } 
            }
            ## stop transcript
            try{stop-transcript|out-null}
            catch [System.InvalidOperationException]{}
        } 
    } 

    Function New-Publication { 
    [cmdletbinding()] 
    Param( 
        [parameter(Mandatory=$true,position=0)]
        [ValidateScript({invoke-sqlcmd -serverInstance:$_ -query:"select 1" })]
        [string] $Publisher , 
    
        [parameter(Mandatory=$true,position=1)]
        [ValidateNotNullOrempty()]
        [string] $Publication 
        ,
        [parameter(Mandatory=$false,position=3)]
        [boolean]$dynamic_filters=$true  
    ) # end param
    
        Begin { 
            If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                $DebugPreference = "Continue" 
            } 
            else { 
                $DebugPreference = "SilentlyContinue" 
            } 
            $sw = [Diagnostics.Stopwatch]::StartNew()
        } 
    
        Process {
            Write-Box "Create Publication $Publication at $Publisher " 
            # get dbn
            switch -regex ($Publication) { 
                'cash' { $dbn = 'ArizonaCASH';break }
                'VisiSys' { $dbn = 'VisiSystem';break } 
                default { $dbn = 'Arizona' } 
            } 
            Write-debug "DBN: $dbn " 

            # build query
            $qry = "EXEC sp_visi_create_Publication @Publication = '$Publication' , @drop = 0" 
            if ( $dynamic_filters -eq $false ) { 
                $qry = $qry  + ', @dynamic_filters = ''false'' '       
            }
            Write-Debug $qry  
            # create Publication
            Invoke-Sqlcmd -ServerInstance $Publisher -Database $dbn -Query $qry -QueryTimeout 100000
            #start snapshot agent
            Invoke-DbaQuery -SqlInstance $Publisher -Database $dbn -Query "exec sp_startPublication_snapshot @Publication = '$Publication'" 
        } 
        End { 
            #collect time stats
            $sw.Stop(); 
            "Total Time elapsed: {0} " -f $($sw.Elapsed) 
        } 
    } 
    
    Function Add-Subscription { 
    <#
    .SYNOPSIS
    Adds a subscriber to a Publication
     
    .Description
     
    .PARAMETER
    Publication
     
    .PARAMETER
    Subscriber (pipeline)
     
    .PARAMETER
    Publisher
     
    .PARAMETER
    start_job
    default Value = $true
     
    .EXAMPLE
    Get-VisiCaisses DEV1
     
    .EXAMPLE
    Add-Subscription -srv SRV-MAG2 -Publication Address -Publisher SRV-ONECMR
     
    .EXAMPLE
    Get-VisiServers DEV2 | Add-subscription -Publication Address -Publisher SRV-SQLDEV2\DEV2OCM
      
    #> 
    
    [cmdletbinding(SupportsShouldProcess)]
        Param( 
            [parameter(Mandatory=$true,position=0,ValueFromPipeline=$true)]
            [ValidateNotNullOrEmpty()]
            [string] $Subscriber , 
    
            [parameter(Mandatory=$true,position=1)]
            [ValidateNotNullOrempty()]
            [string] $Publication , 
        
            [parameter(Mandatory=$true,position=2)]
            [ValidateNotNullOrempty()]
            [string] $Publisher 
        ) # end param
    
        Begin { 
            If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                $DebugPreference = "Continue" 
            } 
            else { 
                $DebugPreference = "SilentlyContinue" 
            } 
        }     
        Process { 
            # get dbn
            $dbn = Get-PublicationDbn -Publisher $publisher -Publication $publication 
    
            # resolve overloaded host_name
            <#
            write-debug 'start get hostname'
            switch -regex ($Publication) {
                '(ItemComp|ConfigComp)' {$qry = 'SELECT Arizona.dbo.fn_Get_Company(abs(Arizona.dbo.fn_get_current_sub())) as hst_name'}
                '(Stock|Archive|Document|Address|Azconfig|ConfigSub|Inventory)' { $qry = "select Abs(Arizona.dbo.fn_get_current_sub()) as hst_name"}
                '(CashOu)' {$qry = 'SELECT cr_organizational_unit_guid as hst_name FROM ArizonaCASH.dbo.CR_organizational_unit cou WHERE crou_locally_used =1'}
                '(CashSub)' {$qry = 'SELECT CROU_subsidiary as hst_name FROM ArizonaCASH.dbo.CR_organizational_unit cou WHERE crou_locally_used =1'}
                '(CashComp)' {$qry = 'SELECT crsub.CRSUB_company as hst_name FROM ArizonaCash.dbo.CR_subsidiary AS crsub WHERE crsub.CR_subsidiary_GUID = ArizonaCash.dbo.fn_Get_current_sub_CASH()'}
                '(VisiSys)' {$qry = 'SELECT @@servername'}
                default {Throw "Publication name $Publication not valid"; exit 1; }
            } # end switch
            #>
            $overLoadHostName = Get-RepliHostName -Subscriber $Subscriber -Publication $Publication 

            #check $hst_name
            if ([string]::IsNullOrEmpty($overLoadHostName) ) {
                Throw "Host name could not be determined"; Exit 1; 
            } 
            write-box $("Add-subscription [{0}] to [{1}].[{2}] - HostName {3} " -f $subscriber, $Publisher ,$Publication, $overLoadHostName ) 
    
            #build query
            $freq = 17 
            $qry_add_sub = "
            exec sp_addmergesubscription @Publication = '$Publication'
                , @subscriber = '$subscriber'
                , @subscriber_db = '$dbn'
                , @subscription_type = N'Push'
                , @sync_type = N'None'
                , @subscriber_type = N'local'
                , @subscription_priority = 75
                , @description = null
                , @use_interactive_resolver = N'False'
                , @hostname = '$overLoadHostName';
            exec sp_addmergepushsubscription_agent @Publication = '$Publication'
                , @subscriber = '$subscriber'
                , @subscriber_db = '$dbn'
                , @job_login = null
                , @job_password = null
                , @subscriber_security_mode = 1
                , @Publisher_security_mode = 1
                , @frequency_type = 4
                , @frequency_interval = 1
                , @frequency_relative_interval = 1
                , @frequency_recurrence_factor = 1
                , @frequency_subday = 4
                , @frequency_subday_interval = " + $freq +"
                , @active_start_time_of_day = 100
                , @active_end_time_of_day = 215959
                , @active_start_date = 20121219
                , @active_end_date = 99991231
                , @enabled_for_syncmgr = N'False'" 

            
            # add subscription and start synchro job
            if ($PSCmdlet.ShouldProcess($subscriber)) {
                # add subscription
                invoke-sqlcmd -Serverinstance $Publisher -Database $dbn -Query $qry_add_sub -QueryTimeout 1500 -ConnectionTimeout 15
                # start job
                $distributor = (Invoke-Sqlcmd -ServerInstance $Publisher -Query "SELECT data_source FROM sys.servers WHERE [name] = 'repl_distributor'" ).data_source
                $job=(invoke-sqlcmd -ServerInstance $distributor -Query "SELECT ma.name FROM Distribution.dbo.MSmerge_agents AS ma WHERE Publication = '$Publication' AND subscriber_name = '$subscriber'").Name
                Invoke-Sqlcmd -ServerInstance $distributor -Query "exec msdb.dbo.sp_start_job @job_name = '$job'"
            } 
            ## stop transcript
            #try{stop-transcript|out-null}
            #catch [System.InvalidOperationException]{}
    } 
} 
    
    Function Remove-Publication { 
    [cmdletbinding()] 
        Param( 
            [parameter(Mandatory=$true,position=0)]
            [ValidateScript({invoke-sqlcmd -serverInstance:$_ -query:"select 1" })]
            [string] $Publisher 
            , 
            [parameter(Mandatory=$true,position=1)]
            [ValidateNotNullOrempty()]
            [string] $Publication 
        ) 
        
        Begin { 
            If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                $DebugPreference = "Continue" 
            } 
            else { 
                $DebugPreference = "SilentlyContinue" 
            } 
        } 
    
        Process { 
            #$sw = [Diagnostics.Stopwatch]::StartNew()
            Write-Debug "Attempting to drop Publication [$Publication] @ [$Publisher]..." 
    
            # get dbn
            $dbn = (Get-DbaRepPublication -PublicationType Merge -SqlInstance $publisher | Where-Object publicationName -eq $publication ).database
            Test-EmptyString $dbn "Publication not found" 
            Write-debug "DBN: $dbn " 
    
            # proceed to dropping Publication
            $qry = "if exists (select 1 from sysmergePublications where name ='$Publication')
                    EXEC sp_dropmergePublication @Publication='$Publication'"
            Write-Debug $qry 
            Invoke-Sqlcmd -ServerInstance:$Publisher -Database:$dbn -Query: $qry -ErrorAction Stop -verbose -QueryTimeout:10000
        } #end Process
    }
    
    Function Update-RepliJobSchedule {
    <#
    .SYNOPSIS
    Stagger repli job schedule
     
    .Description
     
     
    .PARAMETER Publication
    Value from Pipeline = $true
     
    .PARAMETER Freq
    Default = 13
     
    .PARAMETER Publisher
    Default = SRV-ONECM
     
    .PARAMETER Start
    Timespan - first execution of the day
     
    .PARAMETER Stop
    Timespan - last execution of the day
     
    .EXAMPLE
    #
    Update-RepliJobSchedule -Publication Document -freq 17
     
    .EXAMPLE
    #
    $params = @{
        start = $(New-Object System.TimeSpan(0,0,0))
        stop = $(New-Object System.TimeSpan(23,59,0))
        Publication = 'VisiSys'
        freq = 60
        Publisher = 'SRV-ONECM'
    }
    Update-RepliJobSchedule @params
     
    .EXAMPLE
    # default Freq = 13
    (Get-Publications -Publisher SRV-ONECMR).name | Update-RepliJobSchedule -Publisher SRV-ONECMR
     
    #> 
    
    [CmdLetbinding()]
        Param (
                [parameter(Mandatory=$true,position=0,ValueFromPipeline=$true)]
                [ValidateNotNullOrEmpty() ]
                [string] $Publication 
                ,
                [parameter(Mandatory=$false,position=1)]
                [int] $freq  = -1 #frequence in minutes
                , 
                [parameter(Mandatory=$false,position=2)]
                [string] $Publisher='SRV-ONECM' 
                ,
                [parameter(Mandatory=$false,position=3)]
                [timespan] $start  = $(New-Object System.TimeSpan(5,0,0))
                , 
                [parameter(Mandatory=$false,position=4)]
                [timespan] $stop  = $(New-Object System.TimeSpan(22,59,0))
        )
        Begin {
                If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                    $DebugPreference = "Continue" 
                } 
                else { 
                    $DebugPreference = "SilentlyContinue" 
                } 
        } # end begin
    
        Process { 
            Write-Debug "Publication : $Publication " 
            
            # get dbn
            $dbn = (Get-PublicationDbn -Publisher $publisher -Publication $publication) 
            Write-debug "DBN: $dbn " 
    
            # get distributor
            $distributor = Get-Distributor -Publisher $Publisher 
            Test-EmptyString $distributor "Distributor not found"
            Write-Debug $("Distributor = {0}" -f $distributor ) 
    
            # get job names
            $qry = "SELECT s.application_name as job_name
            FROM dbo.sysmergesubscriptions AS s
            JOIN dbo.sysmergePublications AS p ON p.pubid = s.pubid
            WHERE p.name = '$Publication'
            AND s.subscriber_server <> @@servername " 
            #Write-Debug $("Qry = {0}" -f $qry )
            $jobNames = (Invoke-Sqlcmd -ServerInstance $Publisher -Database $dbn -Query $qry -Verbose -ErrorAction Stop ).job_name
    
            # freq by publication
            if ($freq -eq -1) { 
                switch ($publication) {
                    'Stock' { $freq = 7 ; break }
                    Default { $freq = 13 ; break }
                }
            }
            <#
            Address
            ConfigComp
            ConfigSub
            Document
            ItemComp
            OneDoc
            Stock
            CashComp
            CashOU
            CashSub
            #> 



            # get smo jobs
            $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $distributor -ErrorAction Stop 
            $jobs = $SMOserver.JobServer.Jobs| Where-Object {$_.Name -in $jobNames } 
            Write-Debug $("Jobs found = {0}" -f $($jobs).count) 
    
            $delta = New-Object System.TimeSpan (0,0,$(Get-Random -Maximum 30))
            $ActiveStartTimeOfDay = $start
    
            foreach ($job in $jobs) { 
                $ActiveStartTimeOfDay = $ActiveStartTimeOfDay.Add($delta) 
                $sch = $job.JobSchedules[0]
                $sch.FrequencySubDayTypes = [Microsoft.SqlServer.Management.Smo.Agent.FrequencySubDayTypes]::Minute
                $sch.ActiveStartTimeOfDay = $ActiveStartTimeOfDay  
                $sch.FrequencySubDayInterval = $freq 
                $sch.ActiveEndTimeOfDay = $stop
                $sch.IsEnabled = $true 
                $sch.ActiveStartDate = Get-Date
                $sch.Alter() 
                "Job:{4}`n`tStart:{0} `tEnd:{1} `tFrequency:{2} `tFrequency unit:{3} `tDelta:{5} `n" -f $sch.ActiveStartTimeOfDay, $($sch.ActiveEndTimeOfDay),$($sch.FrequencySubDayInterval) ,$sch.FrequencySubDayTypes, $job.Name , $delta
            } #end foreach job
        } # end process
    } # end function
    Set-Alias -Name Stagger-RepliJobSchedule -Value Update-RepliJobSchedule
    
    
    Function Get-RepliCmd { 
    <#
    .SYNOPSIS
    Get repli command
     
    .Description
    Get repli command
     
    .PARAMETER $subscriber_name_patter
    Enough to match a subscriber
     
     
    .EXAMPLE
    Bla bla bla
    #> 
    
        Param ( 
            [parameter(Mandatory=$true,position=0)]
            [ValidateNotNullOrEmpty() ]
            [string]$subscriber
            ,
            [parameter(Mandatory=$true,position=1)]
            [ValidateNotNullOrEmpty() ]
            [string] $Publication
            , 
            [parameter(Mandatory=$false,position=3)]
            [switch] $high_verbose_level = $false 
            , 
            [parameter(Mandatory=$false,position=4)]
            [switch] $high_query_timeout = $false 
    
        ) # end param
        Begin {
            If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                $DebugPreference = "Continue" 
            } 
            else { 
                $DebugPreference = "SilentlyContinue" 
            } 
        } 
    
        Process { 
            # get Publisher
            ($Publisher = Get-Publisher $subscriber)
            Write-Debug $("Publisher = {0}" -f $Publisher)  
    
            # get distributor
            ($distributor = (Get-DbaRepDistributor -SqlInstance $publisher -debug:$false ).DistributionServer)
            Write-Debug $("Distributor = {0}" -f $distributor)  
    
            # get dbn
            ($dbn = Get-PublicationDbn -Publisher $publisher -Publication $publication -debug:$false )
            Write-Debug $("Dbn = {0}" -f $dbn) 
    
            # get job_name
            $qry = "SELECT s.subscriber_server, p.name, s.application_name
            FROM dbo.sysmergesubscriptions AS s
            JOIN dbo.sysmergePublications AS p ON p.pubId = s.pubId
            WHERE s.subscriber_server = '$subscriber'
            and p.name = '$Publication'" 
            Write-Debug $qry 
            ($job_name = (Invoke-Sqlcmd -ServerInstance $Publisher -Database $dbn -Query $qry ).application_name)
            Write-Debug $("Job name = {0}" -f $job_name) 
    
            # get command line
            $smoSrv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $distributor 
            Write-Debug $("SMO job count : {0}" -f $($($smoSrv.JobServer.Jobs).Count))
            $job = $smoSrv.JobServer.Jobs| Where-Object Name -eq $job_name 
            $job_command = $job.JobSteps["Run agent."].Command 
            #Test-EmptyString $job_command
            Write-Debug $("job_command = {0}" -f $job_command) 
    
            # process final string to be returned
            $str = "c:`r`ncd\replmerg`r`nreplmerg.exe $job_command " 
    
            # add output and timeout options
            if ($high_verbose_level.isPresent) { 
                $str += " -outputVerboseLevel 2 " 
            } 
            else { 
                $str += " -outputVerboseLevel 1 " 
            }     
        
            if ($high_query_timeout -eq $true ) { 
                $str += " -QueryTimeout 15000 " 
            } 
        
            # copy to clipboard
            [Windows.forms.Clipboard]::SetText($str)
    
            Write-Host `n$str `n 
    
        } # end process
    } 
    Set-Alias -Name grc -Value Get-RepliCmd -Force 
    
    Function Get-Publications{
    [CmdLetbinding()]
        Param (
            [string]$Publisher = 'SRV-ONECM' , 
            [string]$dbn = 'Arizona' 
        )
        Begin { 
                if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                    $DebugPreference = "Continue" 
                } 
                else { 
                    $DebugPreference = "SilentlyContinue" 
                } 
        } 
        Process{ 
            (Get-DbaRepPublication -SqlInstance $publisher -PublicationType Merge -debug:$false).PublicationName 
        }
    } 
    
    Function Enable-RepliJobSchedule {
    <#
    .SYNOPSIS
    Enables schedules for replication jobs
     
    .Description
     
    .PARAMETER
    Publication (pipeline)
    Value from Pipeline
     
    .PARAMETER
    Publisher
    default value = SRV-ONECM
     
     
    .EXAMPLE
    Enable-RepliJobSchedule -Publication Address -Publisher SRV-ONECMR
     
    .EXAMPLE
    Get-Publications | Enable-RepliJobSchedule
     
    .EXAMPLE
    Get-Publications | ? {$_ -match 'Cash'} | Enable-RepliJobschedule
      
    #> 

    [CmdLetbinding()]
        Param (
            [parameter(Mandatory=$true,position=0,ValueFromPipeline=$true)]
            [string]$Publication ,
            [string]$Publisher = 'SRV-ONECM'
        )
        Begin { 
                if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                    $DebugPreference = "Continue" 
                } 
                else { 
                    $DebugPreference = "SilentlyContinue" 
                } 
        } 
        Process{ 
            Write-Debug $("Publication = {0}" -f $Publication ) 
            Alter_RepliJobSchedule -Publication $Publication -Publisher  $Publisher -isEnable $true 
       }
    } 
    
Function Disable-RepliJobSchedule {
<#
.SYNOPSIS
Disables schedules for replication jobs
 
.Description
Disables schedules for replication jobs
 
.PARAMETER Publication
Publication Name (pipeline)
 
.PARAMETER Publisher
Publisher Name
default value = SRV-ONECM
 
.EXAMPLE
Disable-RepliJobSchedule -Publication Address -Publisher SRV-ONECMR
 
.EXAMPLE
Get-Publications | Disable-RepliJobSchedule
 
.EXAMPLE
Get-Publications | ? {$_ -match 'Cash'} | Disable-RepliJobschedule -Publisher SRV-ONECMR
     
#> 
[CmdLetbinding()]
    Param (
        [parameter(Mandatory=$true,position=0,ValueFromPipeline=$true)]
        [string]$Publication ,
        [string]$Publisher = 'SRV-ONECM'
    )
    Begin { 
            if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                $DebugPreference = "Continue" 
            } 
            else { 
                $DebugPreference = "SilentlyContinue" 
            } 
    } 
    Process{ 
        Write-Debug $("Publication = {0}" -f $Publication ) 
        Alter_RepliJobSchedule -Publication $Publication -Publisher  $Publisher -isEnable $false     
    }
} 
    
    Function Alter_RepliJobSchedule {
    [CmdLetBinding()]
        Param (
            [string] $Publication ,
            [string] $Publisher = 'SRV-ONECM',
            [boolean]$isEnabled 
        )
        Begin { 
                if (-not $PSBoundParameters.ContainsKey('DebugPreference')) { 
                    $debugPreference = $PSCmdlet.GetVariableValue('DebugPreference')
                } 
        } 
        
        Process{ 
            Write-Verbose "Publisher = $publisher" 
            # get dbn
            $dbn = Get-PublicationDbn -Publisher $Publisher -Publication $Publication 
            Write-Verbose "dbn = $dbn"
            #get distributor
            $distributor = (Get-DbaRepDistributor -SqlInstance $publisher ).DistributionServer
            Write-Verbose "Distributor = $distributor" 

            #get job names
            $qry = "
            SELECT s.application_name as jobName
            FROM dbo.sysmergePublications AS p
            join dbo.sysmergesubscriptions AS s ON s.pubid = p.pubid
            WHERE p.name = '$Publication'
            AND s.application_name IS NOT NULL "
            $jobNames = (invoke-sqlcmd -ServerInstance $Publisher -Database $dbn -Query $qry).jobName
            Write-Debug $qry 

            # get repli jobs
            $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $distributor -ErrorAction Stop 
            foreach ($job in $SMOserver.JobServer.Jobs | Where-Object {$_.Name -iin $jobNames} )  {
                #$job.Name
                foreach ($sched in $job.JobSchedules) { 
                    $sched.IsEnabled = $isEnabled
                    $sched.Alter() 
                    $("Job = {0} Schedule = {1} IsEnabled = {2} " -f $job.Name , $sched.Name , $sched.IsEnabled) 
                }
            } 
        }
    } 
    
    Function Get-Distributor {
    <#
    .SYNOPSIS
    Simplified dbaTools version
     
    .Description
    Get distributor
     
    .PARAMETER $Publisher
    Publisher
     
    .EXAMPLE
    Get-Distributor
    #> 
    [CmdLetbinding()]
        Param( 
            [parameter(Mandatory=$true,position=0,ValueFromPipeline=$True)]
            [ValidateNotNullOrempty()]
            [string] $Publisher 
            ) 
        Process { 
            (Get-DbaRepDistributor -SqlInstance $publisher).DistributionServer
        }    
    } 
    
    Function Get-Publisher {
    <#
    .SYNOPSIS
    Get Publisher
     
    .Description
    Get Publisher
     
    .PARAMETER srv
    Server for which the Publisher will be returned
     
    .EXAMPLE
    Get-Publisher 'SRV-MAG1'
    #> 
    [CmdLetbinding()]
    
        Param( 
            [parameter(Mandatory=$false,position=0,ValueFromPipeline=$True)]
            [ValidateScript({invoke-sqlcmd -serverInstance $_ -query "select 1" -ConnectionTimeout 10 })] 
            [string] $srv = $env:COMPUTERNAME
            ) # end Param
    
        Begin {
            If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                $DebugPreference = "Continue" 
            } 
            else { 
                $DebugPreference = "SilentlyContinue" 
            } 
        } 
        Process { 
            Write-Debug $("Srv = {0} " -f $srv ) 
            $qry = "
            BEGIN TRY
                EXEC ('select @@serverName as server_name') at ArizonaCASH ;
            END TRY
            BEGIN CATCH
                RAISERROR ('Checking Publisher using linked server Arizona CASH failed', 16, -1)
            END CATCH "; 
            Write-debug $qry; 
            (Invoke-Sqlcmd -ServerInstance $srv -Query $qry -ErrorAction stop).server_name 
        } # end process
    } 
    
    
Function Remove-Subscription { 
    <#
    .Synopsis
        Remove a subscription
    .Description
        Remove subscription
        0.9.5 - sql agentjob to cleanup locally async
    .Parameter Subscriber
    Subscriber name (pipeline)
    .Parameter Publication
    Publication Name
    .Parameter Publisher
    Optional, will get resolved by calling Get-Publisher $subscriber
        .Parameter AsyncCleanup
        Default TRUE
        When FALSE, will execute the cleanup in sync mode @ subscriber
    .Example
    Remove-Subscription -Subscriber SRV-MAG1 -Publication Address
    .Example
    @('SRV-MAG1','SRV-MAG2') | Remove-Subscription -Publication Document
    #>
    [cmdletbinding()] 
    Param( 
        [parameter(Mandatory=$false,position=0,ValueFromPipeline=$true)]
        [ValidateNotNullOrempty()]
        [string] $subscriber , 
        [parameter(Mandatory=$true,position=1)]
        [ValidateNotNullOrempty()]
        [string] $Publication  , 
        [parameter(Mandatory=$false,position=2)]
        [ValidateNotNullOrempty()]
        [string] $Publisher , 
        [parameter(Mandatory=$false,position=3)]
        [ValidateSet('Async','Sync', 'NoCleanup', ignorecase=$True)]
        [string] $SubscriberCleanupMethod = 'Async'
    ) 
    Begin {
        If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
            $DebugPreference = "Continue" 
        } 
        else { 
            $DebugPreference = "SilentlyContinue" 
        } 
    } 
    Process { 
            $sw = [Diagnostics.Stopwatch]::StartNew()
            write-verbose "subscriber = $subscriber" 
            # get publisher if not supplied
            if ([string]::IsNullOrEmpty($publisher)) { 
                ($publisher = Get-Publisher -srv $subscriber ) 
            } 
            Write-Box "Remove subscription to $Publisher.$Publication on $subscriber " 

            # get dbn
            $dbn = Get-PublicationDbn -Publisher $publisher -Publication $publication -debug:$false
            Write-Debug "dbn = $dbn" 
        
            # drop subscription @ publisher
            $qry = "
            IF EXISTS (
                SELECT p.name, *
                FROM sysmergesubscriptions s
                JOIN sysmergePublications p ON s.Pubid = p.Pubid
                WHERE subscriber_server = '$subscriber'
                AND p.name = '$Publication'
                AND [DB_NAME] = '$dbn'
                AND s.[status] <> 2 )
             
                EXEC dbo.sp_dropmergesubscription @Publication = '$Publication'
                    , @subscriber = '$subscriber'
                    , @subscriber_db = '$dbn'
                    , @subscription_type = 'both' "
            Write-Debug $qry 
            Invoke-Sqlcmd -ServerInstance:$Publisher -Database:$dbn -Query:$qry -QueryTimeout:4500 
            
            # cleanup subscription @ subscriber
                ($qry= "exec sp_mergesubscription_cleanup @Publisher='$Publisher', @Publisher_db='$dbn',@Publication ='$Publication'")
                Write-Debug "`n$qry "
                switch ($SubscriberCleanupMethod)
                {
                    'Sync' {
                        Invoke-Sqlcmd -ServerInstance $subscriber -Database:$dbn -Query:$qry -Verbose -QueryTimeout:4500  
                    }
                    'NoCleanup' { 
                        Write-Warning "No subscriber cleanup performed; please run $qry at $subscriber" 
                    } 
                    'Async' {
                        # create cleanup job
                        $jobName = "## Repli cleanup after remove subscription $publication"
                        $jobParams = @{ 
                            SQLInstance = $subscriber
                            Job =  $jobName
                            EmailOperator = 'DBA' 
                            EmailLevel = 2 
                            DeleteLevel = 1 
                        } 
                        New-DbaAgentJob @jobParams 
                        
                        $jobStepParams = @{ 
                            SQLInstance = $subscriber 
                            Job = $jobName 
                            StepName = 'Step 1' 
                            Database = $dbn  
                            Command = $qry 
                        } 
                        New-DbaAgentJobStep @jobStepParams -force  
                        Start-DbaAgentJob -SqlInstance $subscriber -Job $jobName                     
                    }
                }
            #collect time stats
            $sw.Stop(); 
            "Total Time elapsed: {0} " -f $($sw.Elapsed) 
        } 
    }

    
    
    Function Get-AllSubscribers { 
    [CmdLetbinding()]
        Param (
            [parameter(Mandatory=$true,position=0)]
                [ValidateScript({invoke-sqlcmd -serverInstance $_ -query "select 1" })]
                [string] $Publisher , 
    
            [parameter(Mandatory=$true,position=1)]
                [ValidateNotNullOrEmpty() ]
                [string] $Publication 
        ) #end param
    
        Begin { 
            If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                $DebugPreference = "Continue" } 
            else { 
                $DebugPreference = "SilentlyContinue" } 
        } 
        Process{ 
            Write-Debug $("Publisher = {0} Publication = {1} " -f $Publisher, $Publication ) 
            
            # get dbn
            $dbn = Get-PublicationDbn -Publisher $Publisher -Publication $Publication 
            Write-debug "DBN: $dbn " 
    
            # start RMO server
            $RMOserver = New-Object ('Microsoft.SqlServer.Replication.ReplicationServer') -argumentlist $Publisher 
            write-debug "RMOServer = $($RMOserver.Name)" 
            write-debug "Publications = $($RMOserver.Publications)" 
            # get subscribers
            $RMOserver.ReplicationDatabases[$dbn].MergePublications[$Publication].MergeSubscriptions | Select-Object SubscriberName 
    
        } # end process
    } # end function
    
Function Get-PublicationDbn { 
    [CmdLetbinding()]
        Param (
            [string]$Publisher = 'SRV-ONECM' , 
            [string]$Publication = 'Arizona' 
        ) #end param
        Process { 
            #(Get-Publications -Publisher $Publisher | ? name -eq $Publication ).DataBase
            (Get-DbaRepPublication -SqlInstance $publisher -PublicationType Merge | Where-Object PublicationName -eq $publication ).Database
        } 
    } 
    
Function Get_RowCount { 
Param ( 
    $data_set 
    )
Process {
    [int] $row_count=-1 
    if ($null -eq $data_set ) { 
            $row_count = 0 
        } 
        else { 
            if ($null -eq $data_set.Count)  { 
                $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" 
        } 
        
        Clear-DbaConnectionPool 
        
        # get publisher
        $publisher = Get-Publisher -srv $subscriber 
        #Test-EmptyString $publisher "Publisher not found"
        Write-Debug "Publisher: $publisher"     



        # get dbn
        $dbn = Get-PublicationDbn -publisher $publisher -publication $publication 
        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
    #######################
    if ($srv -match '044' ) { 
        $publisher = 'SRV-ONECM'
    }
    else { 
        $publisher = Get-Publisher -srv $srv 
    }
    
    Test-EmptyString $publisher  "Publisher not found" 
    Write-Debug "Publisher: $publisher"     

    #######################
    # get dbn
    #######################
    $dbn = Get-PublicationDbn -publisher $publisher -publication $publication 
    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 )    


    #######################
    # resolve overloaded host_name
    $subscriber = $srv 
    switch -regex ($publication) { 
        '(ItemComp|ConfigComp)' { 
            $overLoadHostName = (invoke-sqlcmd -ServerInstance:$subscriber -Database:$dbn -Query:'SELECT Arizona.dbo.fn_Get_Company(abs(Arizona.dbo.fn_get_current_sub())) as hst_name' ).hst_name
            break } 
        '(Stock|Archive|Document|Address|Azconfig|ConfigSub|Inventory)' { 
            $overLoadHostName = [math]::Abs((invoke-sqlcmd -ServerInstance:$subscriber -Database:$dbn -Query:'select Arizona.dbo.fn_get_current_sub() as hst_name' ).hst_name)
            break } 
        '(CashOu)' { 
            $qry_cash = 'SELECT cr_organizational_unit_guid as hst_name FROM ArizonaCASH.dbo.CR_organizational_unit cou WHERE crou_locally_used =1 ; ' 
            $overLoadHostName = (invoke-sqlcmd -ServerInstance:$subscriber -Query:$qry_cash ).hst_name
            break } 
        '(CashSub)' { 
            $qry_cash = 'SELECT CROU_subsidiary as hst_name FROM ArizonaCASH.dbo.CR_organizational_unit cou WHERE crou_locally_used =1 ; ' 
            $overLoadHostName = (invoke-sqlcmd -ServerInstance:$subscriber -Query:$qry_cash ).hst_name
            break } 
        '(CashComp)' { 
            $qry_cash = 'SELECT crsub.CRSUB_company as hst_name FROM ArizonaCash.dbo.CR_subsidiary AS crsub WHERE crsub.CR_subsidiary_GUID = ArizonaCash.dbo.fn_Get_current_sub_CASH() ; ' 
            $overLoadHostName = (invoke-sqlcmd -ServerInstance:$subscriber -Query:$qry_cash ).hst_name
            break } 
        '(VisiSys)' { 
            $overLoadHostName = $subscriber ; 
            break } 
        default {Throw "Publication name $Publication not valid"; exit 1; }
    } # end switch
    $sub = $overLoadHostName 
    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 *