这一篇，我们来实现PowerShell 2.0在SQL Server中的一个应用,是批量执行SQL脚本。在接下来的内容，将使用到下面的命令或类库。
- Sort-Object
- out-null
- Write-Error
- $_
- System.IO.DirectoryInfo
- Microsoft.SqlServer.Management.Common.ServerConnection
创建测试环境
为了更能说明PowerShell脚本的应用，我们这里创建个测试环境，模拟一个要升级的SQL脚本文件；首先，要创建两个数据库『TestingDB01』和『TestingDB02』：
View Code
在Microsoft SQL Server Management Studio(MSSMS)中执行上面的创建数据库SQL语句。接下来我们创建三个SQL脚本文件：
1. 01_ TestingDB_CreateTB&InitializeData.sql
2. 02_ TestingDB_Procedures_0001.sql
3. 03_ TestingDB_Procedures_0002.sql
第1个脚本，应用于创建数据表和初始化数据使用，第2、3个脚本，只要是存储过程的脚本文件，其中有1个存储过程包含有动态的SQL语句，每一个脚本都包含有对数据库『TestingDB01』和『TestingDB02』的脚本。这些脚本制作是模拟真实环境中的升级脚本，列举常见的脚本内容样本。下面是描述这三个SQL脚本文件的具体内容。
[01_ TestingDB_CreateTB&InitializeData.sql]文件內容：
View Code [02_ TestingDB_Procedures_0001.sql]文件內容：
View Code
[03_ TestingDB_Procedures_0002.sql]文件內容：
View Code 我们把上面的三个SQL脚本文件存储在本机的某一文件路径下面，如“E:\ExecuteSQLScript”
这里提示下，对于这三个脚本，需根据实际场景，按执行先后顺序，对SQL脚本文件名作编号，如格式“01_…”,”02_…”,”03_…”.
编写PowerShell脚本
在前边我们创建好了测试环境，接下来就开始编写PowerShell来实现执行SQL脚本文件功能。编写PowerShell脚本的时候，我们会考虑这几点问题：
- 如何连接到SQL Server实例，如何执行SQL脚本.
- 如何读取SQL脚本文件内容，如何按SQL脚本编号读取.
- 如何处理错误
如何连接到SQL Server实例，如何执行SQL脚本
PowerShell基于.NET Framework上构建，我们可以根据.NET Framework提供的丰富的类库实现我们需要的功能。这里我们引用到Microsoft.SqlServer.Management.Common命名空間下的ServerConnection类，它可以让我们连接到某一SQL Server实例，而且在ServerConnection类中提供有方法ExecuteNonQuery()，可执行T-SQL语句功能。
e.g.
$serverInstance="WINSERVER01\SQL2008DE01" $userName="sa" $password="sql20081" [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') |out-null $ServerConnection =new-object Microsoft.SqlServer.Management.Common.ServerConnection $serverInstance,$userName, $password $ServerConnection.ExecuteNonQuery(“use test Update myTable Set Data='test' Where ID=3”)
上面的例子，我们更新数据库test中的表myTable数据，从PowerShell控制台返回的信息”1”可以判断更新脚本已经在实例”WINSERVER01\SQL2008DE01”上执行。当然我们可以在MSSMS上查询验证所更新的数据。
如何读取SQL脚本文件内容，如何按SQL脚本编号读取.
读SQL脚本文件，需要获得文件路径下的文件名，把脚本文件内容按文件编号读取出来，把数据转换成字符串类型String。这样我们才能调用应用到上边的ServerConnection类的ExecuteNonQuery()方法中。这里我们System.IO.DirectoryInfo类来实现。
e.g.
$ScriptPath="E:\ExecuteSQLScript\" [System.IO.DirectoryInfo]$DirectoryInfo=New-Object System.IO.DirectoryInfo $ScriptPath | Sort-Object foreach( $f In ($DirectoryInfo.GetFiles("*.sql"))) { $f.Name }
我们下面定义1个类型为[System.Text.StringBuilder]的变量$Sql,调用类System.Io.File中的方法OpenText()，获取上面三个SQL脚本文件的内容，
e.g.
$ScriptPath="E:\ExecuteSQLScript\" [System.Text.StringBuilder]$Sql="" [System.IO.DirectoryInfo]$DirectoryInfo=New-Object System.IO.DirectoryInfo $ScriptPath | Sort-Object foreach( $f In ($DirectoryInfo.GetFiles("*.sql"))) { $Sql=$Sql.AppendLine(([System.Io.File]::OpenText($ScriptPath+$f.Name)).ReadToEnd()) } $Sql.ToString()
如何处理错误
我们无法保证我们所写的PowerShell脚本完全能正常运行，或在运行中发生错误，我们需要作一些特殊的错误处理，如自定义错误提示等。在PowerShell脚本为我们提供类似C#或SQL Server 2005\SQL Server 2008的”Try …Catch”用法。
e.g.
Try { $0=0 $value=1/$0 } Catch { Write-Error $_ }
主要考虑的几个问题，我们已经一一针对它们去解决了，下面我们写成完整的PowerShell脚本，实现批量执行SQL脚本功能：
<#批量执行SQL脚本文件 Andy 2011-10-25 #> <#===========================================#> $serverInstance="WINSERVER01\SQL2008DE01" $userName="sa" $password="sql20081" $ScriptPath="E:\ExecuteSQLScript\" $ScriptList=" " <#===========================================#> $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 $_ } } Catch { Write-Error $_ } 运行脚本结果如图：
提示：
以上脚本测试，使用的SQL Server & Windows环境是：
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)
Sep 16 2010 20:09:22 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1)
另外在PowerShell 2.0+SQL Server 2005(sp4) + Windows 20003(Windows XP)上测试通过。