批量执行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 *.sql
foreach ($item in $items)
{
$path=Join-Path $item.Directory $item.name
$content=get-content $path
Remove-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 $_
   
}


您可以选择一种方式赞助本站

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

图片 表情