Comment appeler une procédure stockée sur un serveur SQL à partir de PowerShell?

, Voici une fonction que j'utilise (légèrement expurgée). Il permet les paramètres d'entrée et de sortie. Je n'ai que les types uniqueidentifier et varchar implémentés, mais tous les autres types sont faciles à ajouter. Si vous utilisez des procédures stockées paramétrées (ou tout simplement paramétré sql...ce code est facilement adapté à cela), cela vous rendra la vie beaucoup plus facile.

pour appeler la fonction, vous avez besoin d'une connexion au serveur SQL (disons $conn),

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

récupérer la sortie proc de l'objet retourné

$res.données #jeu de données, contenant les tables de données retourné par sélectionne

$res.outputparams.ID #paramètre de sortie ID (type uniqueidentifier)

la fonction:

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

, Voici une fonction que j'utilise pour exécuter des commandes sql. Vous avez juste à changer $ sqlCommand.CommandText au nom de votre sproc et $ 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()
}

utilisez sqlcmd au lieu de osql si c'est une base de données de 2005

songez à appeler osql.exe (l'outil de ligne de commande pour SQL Server) passant comme paramètre un fichier texte écrit pour chaque ligne avec l'appel à la procédure stockée.

SQL Server fournit quelques assemblages qui pourraient être utiles avec le nom SMO qui ont une intégration transparente avec PowerShell. Voici un article sur ça.

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

Il existe des méthodes API pour exécuter des procédures stockées qui, je pense, méritent d'être étudiées. Voici un exemple de démarrage:

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

je comprend invoke-sqlcmd2.ps1 et write-datatable.ps1 à partir de http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx . Les appels à exécuter des commandes SQL prennent la forme:

Invoke-sqlcmd2 -ServerInstance "<sql-server>" -Database <DB> -Query "truncate table <table>"

un exemple d'écriture du contenu des variables datable à une table SQL ressemble à:

$logs = (get-item SQLSERVER:\sql\<server_path>).ReadErrorLog() Write-DataTable -ServerInstance "<sql-server>" -Database "<DB>" -TableName "<table>" -Data $logs

je les trouve utiles quand faire des scripts PowerShell relatifs à la base de données SQL Server comme les scripts résultants sont propres et lisibles.