如何从PowerShell调用SQL服务器存储过程?

I have a large CSV file and I want to execute a stored procedure for each line.

我有一个大型的CSV文件，我想为每一行执行一个存储过程。

What is the best way to execute a stored procedure from PowerShell?

从PowerShell执行存储过程的最佳方式是什么?

6 个解决方案

#1

This answer was pulled from http://www.databasejournal.com/features/mssql/article.php/3683181

这个答案来自http://www.databasejournal.com/features/mssql/article.php/3683181

This same example can be used for any adhoc queries. Let us execute the stored procedure “sp_helpdb” as shown below.

同样的示例可以用于任何特殊查询。让我们执行存储过程“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]

#2

Here is a function that I use (slightly redacted). It allows input and output parameters. I only have uniqueidentifier and varchar types implemented, but any other types are easy to add. If you use parameterized stored procedures (or just parameterized sql...this code is easily adapted to that), this will make your life a lot easier.

这是我使用的一个函数(稍作修改)。它允许输入和输出参数。我只实现了独特的识别器和varchar类型，但是任何其他类型都很容易添加。这段代码很容易适应)，这将使您的生活更加轻松。

To call the function, you need a connection to the SQL server (say $conn),

要调用该函数，需要连接到SQL服务器(比如$conn)，

$res=exec-storedprocedure -storedProcName 'stp_myProc' -parameters @{Param1="Hello";Param2=50} -outparams @{ID="uniqueidentifier"} $conn

$res=exec-storedprocedure -storedProcName 'stp_myProc' -parameters @{Param1="Hello";Param2=50} -outparams @{ID=" uniqufier "} $conn

retrieve proc output from returned object

从返回的对象中检索proc输出

$res.data #dataset containing the datatables returned by selects

res美元。包含select返回的数据

$res.outputparams.ID #output parameter ID (uniqueidentifier)

res.outputparams美元。唯一输出参数ID (eidentifier)

The function:

功能:

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

#3

Here is a function I use to execute sql commands. You just have to change $sqlCommand.CommandText to the name of your sproc and $SqlCommand.CommandType to CommandType.StoredProcedure.

下面是我用来执行sql命令的函数。您只需更改$sqlCommand。命令文本到您的sproc和$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()
}

#4

Use sqlcmd instead of osql if it's a 2005 database

如果是2005年的数据库，使用sqlcmd而不是osql

#5

Consider calling osql.exe (the command line tool for SQL Server) passing as parameter a text file written for each line with the call to the stored procedure.

考虑调用osql。exe (SQL Server的命令行工具)作为参数传递给每一行的文本文件，并调用存储过程。

SQL Server provides some assemblies that could be of use with the name SMO that have seamless integration with PowerShell. Here is an article on that.

SQL Server提供了一些可以与名称SMO一起使用的程序集，这些程序集与PowerShell无缝集成。这是一篇关于这方面的文章。

http://www.databasejournal.com/features/mssql/article.php/3696731

There are API methods to execute stored procedures that I think are worth being investigated. Here a startup example:

有一些API方法可以执行存储过程，我认为这些方法值得研究。这里一个创业的例子:

http://www.eggheadcafe.com/software/aspnet/29974894/smo-running-a-stored-pro.aspx

#6

I include invoke-sqlcmd2.ps1 and write-datatable.ps1 from http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx. Calls to run SQL commands take the form:
Invoke-sqlcmd2 -ServerInstance "<sql-server>" -Database <DB> -Query "truncate table <table>"
An example of writing the contents of DataTable variables to a SQL table looks like:
$logs = (get-item SQLSERVER:\sql\<server_path>).ReadErrorLog() Write-DataTable -ServerInstance "<sql-server>" -Database "<DB>" -TableName "<table>" -Data $logs
I find these useful when doing SQL Server database-related PowerShell scripts as the resulting scripts are clean and readable.

我包括invoke-sqlcmd2。ps1和write-datatable。ps1从http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx。调用运行SQL命令的形式为:Invoke-sqlcmd2 -ServerInstance“< SQL -server>”-Database -Query " truncatetable

). readerrorlog()写- datatable -ServerInstance " " -Database " " -TableName " "将DataTable变量的内容写入SQL表的示例如下:$logs = (get-item SQLSERVER:\sql\

" -Data $logs我发现，当执行sql Server数据库相关的PowerShell脚本时，这些日志非常有用，因为生成的脚本是干净且可读的。

#1