我有一个很大的CSV文件，我想为每一行执行一个存储过程。
从PowerShell执行存储过程的最佳方法是什么？
此答案来自http://www.databasejournal.com/features/mssql/article.php/3683181
同一示例可用于任何临时查询。让我们执行存储过程" sp_helpdb"，如下所示。
1
2
3
4
5
6
7
8
9
10
11
| $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString ="Server=HOME\\SQLEXPRESS;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText ="sp_helpdb"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] |
这是我使用的功能(略作编辑)。它允许输入和输出参数。我仅实现了uniqueidentifier和varchar类型，但其他任何类型都易于添加。如果您使用参数化的存储过程(或仅使用参数化的sql ...此代码很容易适应该问题)，这将使您的工作变得更加轻松。
要调用该函数，您需要连接到SQL Server(例如$ conn)，
$res=exec-storedprocedure -storedProcName 'stp_myProc' -parameters @{Param1="Hello";Param2=50} -outparams @{ID="uniqueidentifier"} $conn
retrieve proc output from returned object
$res.data #dataset containing the datatables returned by selects
$res.outputparams.ID #output parameter ID (uniqueidentifier)
功能：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
| function exec-storedprocedure($storedProcName,
[hashtable] $parameters=@{},
[hashtable] $outparams=@{},
$conn,[switch]$help){
function put-outputparameters($cmd, $outparams){
foreach($outp in $outparams.Keys){
$cmd.Parameters.Add("@$outp", (get-paramtype $outparams[$outp])).Direction=[System.Data.ParameterDirection]::Output
}
}
function get-outputparameters($cmd,$outparams){
foreach($p in $cmd.Parameters){
if ($p.Direction -eq [System.Data.ParameterDirection]::Output){
$outparams[$p.ParameterName.Replace("@","")]=$p.Value
}
}
}
function get-paramtype($typename,[switch]$help){
switch ($typename){
'uniqueidentifier' {[System.Data.SqlDbType]::UniqueIdentifier}
'int' {[System.Data.SqlDbType]::Int}
'xml' {[System.Data.SqlDbType]::Xml}
'nvarchar' {[System.Data.SqlDbType]::NVarchar}
default {[System.Data.SqlDbType]::Varchar}
}
}
if ($help){
$msg = @"
Execute a sql statement. Parameters are allowed.
Input parameters should be a dictionary of parameter names and values.
Output parameters should be a dictionary of parameter names and types.
Return value will usually be a list of datarows.
Usage: exec-query sql [inputparameters] [outputparameters] [conn] [-help]
"@
Write-Host $msg
return
}
$close=($conn.State -eq [System.Data.ConnectionState]'Closed')
if ($close) {
$conn.Open()
}
$cmd=new-object system.Data.SqlClient.SqlCommand($sql,$conn)
$cmd.CommandType=[System.Data.CommandType]'StoredProcedure'
$cmd.CommandText=$storedProcName
foreach($p in $parameters.Keys){
$cmd.Parameters.AddWithValue("@$p",[string]$parameters[$p]).Direction=
[System.Data.ParameterDirection]::Input
}
put-outputparameters $cmd $outparams
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[Void]$da.fill($ds)
if ($close) {
$conn.Close()
}
get-outputparameters $cmd $outparams
return @{data=$ds;outputparams=$outparams}
} |
这是我用来执行sql命令的函数。您只需将$ sqlCommand.CommandText更改为sproc的名称，将$ SqlCommand.CommandType更改为CommandType.StoredProcedure。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| function execute-Sql{
param($server, $db, $sql )
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = 'server=' + $server + ';integrated security=TRUE;database=' + $db
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 120
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText= $sql
$text = $sql.Substring(0, 50)
Write-Progress -Activity"Executing SQL" -Status"Executing SQL => $text..."
Write-Host"Executing SQL => $text..."
$result = $sqlCommand.ExecuteNonQuery()
$sqlConnection.Close()
} |
如果它是2005数据库，请使用sqlcmd而不是osql
考虑调用osql.exe(SQL Server的命令行工具)，将为每一行编写的文本文件作为参数传递给存储过程。
SQL Server提供了一些可以与PowerShell无缝集成的，名称为SMO的程序集。这是有关此的文章。
http://www.databasejournal.com/features/mssql/article.php/3696731
有一些执行存储过程的API方法，我认为值得研究。这是一个启动示例：
http://www.eggheadcafe.com/software/aspnet/29974894/smo-running-a-stored-pro.aspx
我在http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write中包含了invoke-sqlcmd2.ps1和write-datatable.ps1 -to-sql.aspx。运行SQL命令的调用采用以下格式：Invoke-sqlcmd2 -ServerInstance"<sql-server>" -Database <DB> -Query"truncate table <table>"将DataTable变量的内容写入SQL表的示例如下：$logs = (get-item SQLSERVER:\\sql\\<server_path>).ReadErrorLog()
Write-DataTable -ServerInstance"<sql-server>" -Database"<DB>" -TableName"<table>" -Data $logs在执行与SQL Server数据库相关的PowerShell脚本时，由于生成的脚本很干净，因此我发现这些命令很有用且可读。
