[sql-server] How do I call a SQL Server stored procedure from PowerShell?

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

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

This question is related to sql-server powershell

The answer is


This answer was pulled from 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.

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

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.

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

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

retrieve proc output from returned object

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

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

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

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.

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

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


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.

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.

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:

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


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.


Questions with sql-server tag:

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd SQL Server IF EXISTS THEN 1 ELSE 2 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to add a boolean datatype column to an existing table in sql? How to import an Excel file into SQL Server? How to use the COLLATE in a JOIN in SQL Server? Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Drop view if exists Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object How to update large table with millions of rows in SQL Server? Could not find server 'server name' in sys.servers. SQL Server 2014 How to create a Date in SQL Server given the Day, Month and Year as Integers Select Rows with id having even number A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233) SQL Server: Error converting data type nvarchar to numeric How to add LocalDB to Visual Studio 2015 Community's SQL Server Object Explorer? Using DISTINCT along with GROUP BY in SQL Server Rebuild all indexes in a Database How to generate Entity Relationship (ER) Diagram of a database using Microsoft SQL Server Management Studio? The target principal name is incorrect. Cannot generate SSPI context How Stuff and 'For Xml Path' work in SQL Server? How to view the roles and permissions granted to any database user in Azure SQL server instance? How do I create a local database inside of Microsoft SQL Server 2014? Format number as percent in MS SQL Server MSSQL Regular expression How to select all the columns of a table except one column? SQL count rows in a table EXEC sp_executesql with multiple parameters SQL Server : How to test if a string has only digit characters Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query Remove decimal values using SQL query How to drop all tables from a database with one SQL query? How to get last 7 days data from current datetime to last 7 days in sql server Get last 30 day records from today date in SQL Server Using Excel VBA to run SQL query No process is on the other end of the pipe (SQL Server 2012) How to subtract 30 days from the current date using SQL Server Calculate time difference in minutes in SQL Server How to join two tables by multiple columns in SQL? The database cannot be opened because it is version 782. This server supports version 706 and earlier. A downgrade path is not supported

Questions with powershell tag:

Why powershell does not run Angular commands? How do I install the Nuget provider for PowerShell on a unconnected machine so I can install a nuget package from the PS command line? How to print environment variables to the console in PowerShell? Check if a string is not NULL or EMPTY The term 'ng' is not recognized as the name of a cmdlet VSCode Change Default Terminal 'Connect-MsolService' is not recognized as the name of a cmdlet Powershell Invoke-WebRequest Fails with SSL/TLS Secure Channel Install-Module : The term 'Install-Module' is not recognized as the name of a cmdlet Change directory in PowerShell ps1 cannot be loaded because running scripts is disabled on this system How do I force Robocopy to overwrite files? How to use the curl command in PowerShell? Changing PowerShell's default output encoding to UTF-8 Upload file to SFTP using PowerShell Convert a string to datetime in PowerShell Catching FULL exception message The response content cannot be parsed because the Internet Explorer engine is not available, or Extract the filename from a path Making a PowerShell POST request if a body param starts with '@' If strings starts with in PowerShell Powershell: A positional parameter cannot be found that accepts argument "xxx" Powershell script to check if service is started, if not then start it How to convert string to integer in PowerShell Read file line by line in PowerShell How open PowerShell as administrator from the run window A better way to check if a path exists or not in PowerShell Check if a file exists or not in Windows PowerShell? Ternary operator in PowerShell Splitting a string into separate variables Simple InputBox function How can I find the product GUID of an installed MSI setup? Running PowerShell as another user, and launching a script How do I check if a PowerShell module is installed? How do I assign a null value to a variable in PowerShell? How to Correctly Check if a Process is running and Stop it Convert a secure string to plain text Use Invoke-WebRequest with a username and password for basic authentication on the GitHub API How to unzip a file in Powershell? How to switch to another domain and get-aduser You cannot call a method on a null-valued expression Out-File -append in Powershell does not produce a new line and breaks string into characters User Get-ADUser to list all properties and export to .csv How can I verify if an AD account is locked? How can you test if an object has a specific property? Powershell: count members of a AD group Powershell folder size of folders without listing Subdirectories How to overwrite files with Copy-Item in PowerShell Using "-Filter" with a variable How do I use Join-Path to combine more than two strings into a file path?