If you need to make a job fail via t-sql, simply use the throw statement:
THROW 51000, 'Failure', 1;
In fact, throwing ‘Failure’ from any program (powershell included) will cause the job to fail:
if($ret -eq 1) { throw "Failure" }
If you need to make a job fail via t-sql, simply use the throw statement:
THROW 51000, 'Failure', 1;
In fact, throwing ‘Failure’ from any program (powershell included) will cause the job to fail:
if($ret -eq 1) { throw "Failure" }
Unfortunately, this doesn’t compare the columns included in the index, just the indexes by name. I’ll bang one together that does that latter. You’ll need sqlpsx in order to run this.
cls $srcTables = Get-SqlTable $(Get-SqlDatabase -sqlserver "SourceServer" -dbname "SrcDB") $targetTables = Get-SqlTable $(Get-SqlDatabase -sqlserver "TargetServer" -dbname "TargetDB") foreach($table in $srcTables) { $targetTable = $targetTables | where{$_.Name -eq $table.Name} $targetIDX = $targetTable.Indexes; $srcIDX = $table.Indexes; $diff = Compare-Object -ReferenceObject $srcIDX -DifferenceObject $targetIDX -Property Name if($diff -ne $null) { Write-Host $diff } }
This script does compare the index columns, but I haven’t put the two together yet. I need to clean up my scripts.
function CheckIndexColumns($table, $index) { $columns = $index.IndexedColumns# | Sort-Object -Property Name foreach($idx in $table.Indexes | where{$_.Name -ne $index.Name}) { #if($idx.IndexedColumns.Count -ne $index.IndexedColumns.Count){continue;} #$sourceCols = $index.IndexedColumns | Sort-Object -Property Name $targetCols = $idx.IndexedColumns #| Sort-Object -Property Name $diff = Compare-Object -ReferenceObject $columns -DifferenceObject $targetCols -SyncWindow 1000 if($diff -eq $null) { Write-Host "Possible duplicate index found on table Table: $($table.Name)" -ForegroundColor Red write-Host "Index [$($index.Name)] and index [$($idx.Name)] have the same columns: $($idx.IndexedColumns)" -ForegroundColor DarkCyan } } } cls $db = Get-SqlDatabase -sqlserver ServerName -dbname DatabaseName foreach($table in $db.Tables) { Write-Host "Checking table $($table.Name)..." -ForegroundColor Gray foreach($idx in $table.Indexes) { CheckIndexColumns $table $idx } }
Posting this for Michelle Uffort (@sqlfool).
$saUser = 'sa' $saPwd = get-content "C:\Powershell\Monitoring\sacred.txt" | convertto-securestring $targetSrvCn = 'TargetServer' $srcSrvCn = 'SourceServer' $srcDBName = 'srcDB' $targetDBName = 'TargetDB' $targetConn = New-Object ('Microsoft.SqlServer.Management.Common.ServerConnection') ('TargetServer', $saUser, $saPwd) $targetServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $targetConn $targetDB = $targetServer.Databases | where {$_.Name -eq $targetDBName} $targetTables = $targetDB.Tables | where{$_.Name -notlike "*QueueCacheData*"} $sourceConn = New-Object ('Microsoft.SqlServer.Management.Common.ServerConnection') ('SourceServer', $saUser, $saPwd) $sourceServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sourceConn $sourceDB = $sourceServer.Databases | where {$_.Name -eq $srcDBName} $sourceTables = $sourceDB.Tables | where{$_.Name -notlike "*QueueCacheData*"} $diff = Compare-Object -DifferenceObject $targetTables -ReferenceObject $sourceTables -SyncWindow 300 -Property Name $diff foreach($table in $sourceTables) { $targetTable = $targetTables | where{$_.Name -eq $table.Name} if($targetTable -eq $null) { Write-Host "Table $($table.Name) does not exist in target database." -ForegroundColor Red continue; } write-host $table.Name $targetTable.Name $targetColumns = $targetTable.Columns | sort $_.Name $srcColumns = $table.Columns | sort $_.Name $diff = Compare-Object -ReferenceObject $srcColumns -DifferenceObject $targetColumns -Property Name -SyncWindow 100 $diff }