sql-server - salida - retornar valor stored procedure sql server

¿Cómo puedo llamar a un procedimiento almacenado de SQL Server desde PowerShell?

Question

Tengo un gran archivo CSV y quiero ejecutar un procedimiento almacenado para cada línea.

¿Cuál es la mejor manera de ejecutar un procedimiento almacenado desde PowerShell?

Answer 1

Aquí hay una función que uso (redactada ligeramente). Permite los parámetros de entrada y salida. Solo tengo implementados los tipos uniqueidentifier y varchar, pero cualquier otro tipo es fácil de agregar. Si utiliza procedimientos almacenados parametrizados (o simplemente sql parametrizado ... este código se adapta fácilmente a eso), esto hará que su vida sea mucho más fácil.

Para llamar a la función, necesita una conexión al servidor SQL (por ejemplo $ conn),

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

recuperar la salida de proc del objeto devuelto

$ res.data #dataset que contiene las tablas de datos devueltas por selects

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

La función:

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

Answer 2

Aquí hay una función que uso para ejecutar comandos sql. Solo tiene que cambiar $ sqlCommand.CommandText por el nombre de su sproc y $ SqlCommand.CommandType por 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()
}

Answer 3

Considere llamar a osql.exe (la herramienta de línea de comandos para SQL Server) pasando como parámetro un archivo de texto escrito para cada línea con la llamada al procedimiento almacenado.

SQL Server proporciona algunos ensambles que podrían ser de utilidad con el nombre SMO que tiene una integración perfecta con PowerShell. Aquí hay un artículo sobre eso.

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

Existen métodos API para ejecutar procedimientos almacenados que creo que vale la pena investigar. Aquí un ejemplo de inicio:

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

Answer 4

Esta respuesta fue extraída de http://www.databasejournal.com/features/mssql/article.php/3683181

Este mismo ejemplo se puede usar para cualquier consulta adhoc. Vamos a ejecutar el procedimiento almacenado "sp_helpdb" como se muestra a continuación.

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

Answer 5

Use sqlcmd en lugar de osql si es una base de datos 2005

Answer 6

invoke-sqlcmd2.ps1 y write-datatable.ps1 desde http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx . Las llamadas para ejecutar comandos SQL toman la forma:
Invoke-sqlcmd2 -ServerInstance "<sql-server>" -Database <DB> -Query "truncate table <table>"
Un ejemplo de cómo escribir el contenido de las variables de DataTable en una tabla SQL es como sigue:
$logs = (get-item SQLSERVER:/sql/<server_path>).ReadErrorLog() Write-DataTable -ServerInstance "<sql-server>" -Database "<DB>" -TableName "<table>" -Data $logs
Encuentro estos útiles al hacer scripts de PowerShell relacionados con la base de datos de SQL Server, ya que los scripts resultantes son limpios y legibles.