如何从PowerShell调用SQL Server存储过程？

Question

如何从PowerShell调用SQL Server存储过程？

sql-serverpowershell

48

我有一个很大的CSV文件，想要对每一行执行存储过程。

从PowerShell中执行存储过程的最佳方法是什么？

- Andy J

8个回答

11

以下是我使用的函数（略有删节）。它允许输入和输出参数。我只实现了 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}
    }

- Mike Shepard

2

在 NVARCHAR 输出变量上，您必须指定 Size 属性，因此上面显示的代码将导致错误。 - yonsk

代码是做什么的？ - Golden Lion

对于NVARCHAR输出变量，请尝试将内部函数“put-outputparameters”替换为以下内容。正如yonsk所说，参数大小必须设置为非零以接收文本：function put-outputparameters($cmd, $outparams){ #newline-here foreach($outp in $outparams.Keys){ #newline-here $par = new-object System.Data.SqlClient.SqlParameter("@$outp", (get-paramtype $outparams[$outp])) #newline-here $par.Direction = [System.Data.ParameterDirection]::Output #newline-here $par.Size = 4000 #newline-here $cmd.Parameters.Add($par) } } - DimRasRecordset

8

这是我用来执行 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()
}

- santiiiii

1

$sqlCommand.Dispose() 和 $sqlConnection.Dispose() 怎么样？ - SBF

3

如果是2005数据库，请使用sqlcmd代替osql

- Galwegian

如果您在运行脚本的计算机上安装了所需工具，则此方法可行（并且是一个很好的简单解决方案）。否则，使用.NET的System.Data.SqlClient似乎是必要的。 - Scott Saad

2

考虑调用osql.exe（SQL Server的命令行工具），将每行调用存储过程的文本文件作为参数传递。

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

- imjorge

0

在 @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"

- Ash K

0

我从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脚本时，我发现这些工具非常有用，因为生成的脚本干净易读。

- Ken

0

我添加了超时功能，并展示了如何使用读取器读取标量或获取结果

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]
}

- Golden Lion

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Mark Schill · Accepted Answer

这个答案是从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]