Import-Module SQLDATA -Force -DisableNameChecking function EXwConn { param( $targetHost , $targetcmd ) Exec-MSSQLData -CONNECTION "Data Source=$targetHost;Initial Catalog=master;Persist Security Info=True;Integrated Security=SSPI;" ` -sql $targetcmd } function GTwConn { param( $targetHost , $targetcmd , $RTNIDX = 0..0 ) $DS = Get-MSSQLData -CONNECTION "Data Source=$targetHost;Initial Catalog=master;Persist Security Info=True;Integrated Security=SSPI;" ` -sql $targetcmd RETURN $DS.TABLES[$RTNIDX] } function Qlog { param( $str ) Write-Host $str -BackgroundColor Yellow -ForegroundColor Red $str >> $global:logfile } function getItemQ { param( $qArr = $global:tblBatch , [ref] $itmQ ) $X = 0; $Y = 0 WHILE($qArr.Count -NE 0){ try{ $itmQ.value.EnQueue($(,@($y, $qArr[$y].Dequeue()))) } catch [exception]{ $qArr.RemoveAt($y) } IF($Y -EQ ($qArr.count - 1)){$y = 0} else {$y++} } $itmQ.value = [collections.queue]::Synchronized($itmQ.value) } function init{ $global:logfile = "U:\PA2013\PA2013_ERR.TXT" $logfi = New-Object io.fileinfo $logfile if($logfi.Exists){ $logfi.Delete() $logfi.Create() } else { $logfi.Directory.Create() $logfi.Create() } $global:instances = "dc20gtdb02.colocation.channelwave.local\POD2", "10.20.9.169\DEV_POD", #dc20gtdevdb03.Colocation.ChannelWave.local "DC20GTODS.colocation.channelwave.local\GTODS" $global:instNM= "[POD2@dc20gtdb02].", "[DEV_POD@dc20gtdevdb03].", "[GTODS@DC20GTODS]." $global:tblBatch = New-Object collections.arraylist $instances | %{ $ins = $_ $q = New-Object collections.queue $dbs = GTwConn -targetHost $ins -targetcmd "select name from sys.databases where database_id > 4" $dbs.rows | %{ $r = $_.name TRY{ $schs = GTwConn -targetHost $ins -targetcmd " select '[' + sch.name + '].[' + obj.name + ']' obj from $r.sys.schemas sch inner join $r.sys.tables obj on sch.schema_id = obj.schema_id" try{ $schs.rows | %{ try{ $q.Enqueue($("[$r]." + $_.OBJ)) } catch { Qlog "Q '$($_.OBJ)' Failed!" } } } catch { Qlog "Q '$r' Failed!" } } CATCH { Qlog "Q '$INS' Failed!" } } #$sfq = [collections.queue]::Synchronized($q) [void] $tblBatch.add($q) #$sfq) } $global:iq = New-Object collections.queue getItemQ -itmQ $([ref] $global:iq) Register-EngineEvent -SourceIdentifier qnext -Action { $cid = $args[0] doConc $cid } } $sample = { param( $insID , $QRY ) try{ $INST = $instances[$insID] $INST2 = $instNM[$insID] $flnm = "U:\PA2013\$INST2$QRY.csv" $TBLDATA = GTwConn -targetHost $INST -targetcmd "select TOP(200) * FROM $QRY" if($TBLDATA -ne $null){ $csv = $TBLDATA | ConvertTo-Csv -Delimiter "," -NoTypeInformation [System.IO.File]::WriteAllLines($flnm, $csv) } else { $cnt = (GTwConn -targetHost $INST -targetcmd "select count(*) cnt FROM $QRY").ROWS[0].CNT QLOG "$QRY in $INST2 has no data, count $cnt" } } catch [exception]{ write-host $($_ | out-string) -ForegroundColor Yellow QLOG "$QRY in $INST2 not exists" } } function doConc { param( $concID , $DESC ) try{ $qitm = $Global:iq.Dequeue() } catch {break} Write-Host "$concID : $($qitm[1]) start" $jb = Start-Job -ArgumentList $sample, $concID, $qitm, $instances, $instNM, $global:logfile, $Function:Qlog, $Function:GTwConn -ScriptBlock { param( $sample, $concID, $qitm, $instances, $instNM, $global:logfile, $Qlog, $GTwConn ) Import-Module SQLDATA -Force -DisableNameChecking iex "function Qlog {$($Qlog.ToString())}" iex "function GTwConn {$($GTwConn.ToString())}" . $([scriptblock]::Create($sample)) $qitm[0] $qitm[1] } $posh = "Register-ObjectEvent -InputObject `$jb -EventName StateChanged -SupportEvent -Action { Write-Host `"$concID : $($qitm[1]) done`" New-Event -SourceIdentifier qnext -EventArguments $concID }" iex $posh } init doConc 00 doConc 01 doConc 02 doConc 03 doConc 04 doConc 05 doConc 06 doConc 07 doConc 08 doConc 09 doConc 10 doConc 11 doConc 12 doConc 13 doConc 14 doConc 15 doConc 16 doConc 17 doConc 18 doConc 19 doConc 20 |
posh | Powershell > posh | event-driven concurrently auto archive/split files by 7z and copy to target >