批量執行SQL指令檔

用Windows PowerShell  批量執行SQL指令檔 小工具

<#批量執行SQL指令檔#><#===========================================#>$serverInstance="114.80.119.202" $userName="sa"$password="****"$ScriptPath="D:\work\config\張志明\2012-2-21\"$ScriptPathError="D:\work\doc\SQL Server Management Studio\Projects\20120220\"$ScriptList=""#格式化文本  轉成unicode$path=$ScriptPath$items=Get-ChildItem $path -Recurse -Force -Include *.sqlforeach ($item in $items){$path=Join-Path $item.Directory $item.name$content=get-content $pathRemove-Item  $path$content |out-file -filepath $path -encoding Unicode}<#===========================================#>$n="`n"$r="`r"While ($ScriptList.IndexOf($n) -gt 0)    {$ScriptList=$ScriptList.Replace($n,";")}While ($ScriptList.IndexOf($r) -gt 0)    {$ScriptList=$ScriptList.Replace($r,";")}    While ($ScriptList.IndexOf(" ") -gt 0)        {$ScriptList=$ScriptList.Replace(" ","")}While ($ScriptList.IndexOf(",") -gt 0)        {$ScriptList=$ScriptList.Replace(",","")}If ($ScriptList.IndexOf(".sql") –le 0){    $ScriptList=""    [System.IO.DirectoryInfo]$DirectoryInfo=New-Object System.IO.DirectoryInfo $ScriptPath | Sort-Object    foreach( $f In ($DirectoryInfo.GetFiles("*.sql")))     {        $ScriptList=$ScriptList+";"+$f.Name    }}Try{    [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') |out-null    $ServerConnection =new-object Microsoft.SqlServer.Management.Common.ServerConnection $serverInstance,$userName, $password    try    {        $ServerConnection.BeginTransaction()        Write-Host "BeginTransaction ."                [System.Text.StringBuilder]$Sql=""        Foreach($File In $ScriptList.Split(";"))        {                    if($File -ne "")            {                $Sql=$Sql.AppendLine(([System.Io.File]::OpenText($ScriptPath+$File)).ReadToEnd())                 $ServerConnection.ExecuteNonQuery($Sql)|out-null                $Sql=""                                Write-Host $ScriptPath$File  " ...OK!"            }        }        $ServerConnection.CommitTransaction()                Write-Host "CommitTransaction ."          }    Catch    {        If ($ServerConnection.TransactionDepth -gt 0)            {                $ServerConnection.RollBackTransaction()                Write-Host "RollBackTransaction ."            }                             Write-Error $_          Write-Host $ScriptPath$File  " ...執行失敗!"         Move-Item -Path $ScriptPath$File -Destination $ScriptPathError -PassThru –Force       }    }Catch{    Write-Error $_   }