我有一个很大的CSV文件,想要对每一行执行存储过程。
从PowerShell中执行存储过程的最佳方法是什么?
我有一个很大的CSV文件,想要对每一行执行存储过程。
从PowerShell中执行存储过程的最佳方法是什么?
这个答案是从http://www.databasejournal.com/features/mssql/article.php/3683181获取的。
同样的例子适用于任何特定查询。让我们执行下面展示的存储过程“sp_helpdb”。
$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 服务器(假设为 $conn),
$res=exec-storedprocedure -storedProcName 'stp_myProc' -parameters @{Param1="Hello";Param2=50} -outparams @{ID="uniqueidentifier"} $conn
从返回的对象中检索存储过程输出
$res.data #包含由 SELECT 返回的数据表的数据集
$res.outputparams.ID #输出参数 ID(uniqueidentifier)
该函数代码如下:
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 更改为您的存储过程名称,将 $SqlCommand.CommandType 更改为 CommandType.StoredProcedure。
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()
}
$sqlCommand.Dispose()
和 $sqlConnection.Dispose()
怎么样? - SBF如果是2005数据库,请使用sqlcmd代替osql
SQL Server提供了一些名为SMO的程序集,可以与PowerShell无缝集成。这是一篇相关文章。
http://www.databasejournal.com/features/mssql/article.php/3696731
有API方法可以执行存储过程,我认为值得调查。这里是一个初始示例:http://www.eggheadcafe.com/software/aspnet/29974894/smo-running-a-stored-pro.aspx
在 @Santiago Cepas 的回答中添加了 CommandType
和 Parameters
:
function Execute-Stored-Procedure
{
param($server, $db, $spname)
$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.CommandType= [System.Data.CommandType]::StoredProcedure
# If you have paramters, add them like this:
# $sqlCommand.Parameters.AddWithValue("@paramName", "$param") | Out-Null
$sqlCommand.CommandText= $spname
$text = $spname.Substring(0, 50)
Write-Progress -Activity "Executing Stored Procedure" -Status "Executing SQL => $text..."
Write-Host "Executing Stored Procedure => $text..."
$result = $sqlCommand.ExecuteNonQuery()
$sqlConnection.Close()
}
# Call like this:
Execute-Stored-Procedure -server "enter-server-name-here" -db "enter-db-name-here" -spname "enter-sp-name-here"
我从http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx中引用了invoke-sqlcmd2.ps1
和write-datatable.ps1
。运行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脚本时,我发现这些工具非常有用,因为生成的脚本干净易读。
我添加了超时功能,并展示了如何使用读取器读取标量或获取结果
function exec-query( $storedProcName,$parameters=@{},$conn,$timeout=60){
$cmd=new-object system.Data.SqlClient.SqlCommand
$cmd.CommandType=[System.Data.CommandType]'StoredProcedure'
$cmd.Connection=$conn
$cmd.CommandText=$storedProcName
$cmd.CommandTimeout=$timeout
foreach($p in $parameters.Keys){
[Void] $cmd.Parameters.AddWithValue("@$p",$parameters[$p])
}
#$id=$cmd.ExecuteScalar()
$adapter=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
$dataset=New-Object system.Data.DataSet
$adapter.fill($dataset) | Out-Null
#$reader = $cmd.ExecuteReader()
#$results = @()
#while ($reader.Read())
#{
# write-host "reached" -ForegroundColor Green
#}
return $dataSet.Tables[0]
}
$sqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
。 - Hutch$SqlCmd.Parameters.AddWithValue("@paramName", "$param") | Out-Null
。 - LoMaPh