Calling SQL Server stored procedure from powershell

Question

I'm trying to call a SQL Server stored procedure from PowerShell but I always get errors on parameters.

Stored procedure has 8 parameters, all with default values

@simchain   nvarchar
@idSimulation    int
@idCompany   varchar
@modelName  nvarchar
@simDate    datetime
@mySim           int
@statusFloor     int
@statusCap       int

From Management Studio I can call this procedure even without any parameter, so just executing EXEC [dbo].[E_simulations] works.

From PowerShell I create a connection and a command but I always get an error on missing parameters, for example

Procedure or function 'E_simulations' expects parameter '@simchain', which was not supplied.

Here is my test code (just to test proper execution)

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
    $SqlConnection.ConnectionString = $ConnectionString;
    $SqlCommand = $SqlConnection.CreateCommand();
    $SqlCommand.CommandText = "EXEC [dbo].[E_simulations]";
    $SqlConnection.Open();
    $returnedValue = $SqlCommand.ExecuteNonQuery();

Am I missing something?

Does the stored procedure have default values for all parameters? If not, I don't see how it could work from SSMS either. The proper way to call a proc from Powershell (or any app code) is to specify only the proc name in the CommnadText and specify CommandType = CommandType.StoredProcedure. You'll need to add required parameters. — Dan Guzman
– Dan Guzman, Commented Mar 6, 2015 at 13:14
Sure they have default values for each parameter. I Tried to add $SqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure but have I now to add all parameters even if I want to use default values? — Naigel
– Naigel, Commented Mar 6, 2015 at 13:18
You shouldn't need to add parameters that have default values in the proc header. I just ran a quick test of your code (StoredProcedure command type version) and it ran without error with no parameters added to the collection. I get the error only if I remove a parameter default value. Check to make sure you are connected to the same server and database as you did in SSMS. — Dan Guzman
– Dan Guzman, Commented Mar 6, 2015 at 13:35
So you are saying I am so stupid that I spend last 2 hours running this code against the production server instead of the test server where the procedure has been updated? Do you think I am so stupid?? Really??? Is that what you are saying???? Well... you're right... Thank you ;) — Naigel
– Naigel, Commented Mar 6, 2015 at 14:09

Darka · Accepted Answer · 2015-03-06 13:48:53Z

2

I have made quick test. I hope this will help

Made test proc:

CREATE PROC doTest (
    @param1 INT = 1,
    @param2 VARCHAR(10) = 'xxx' 
)
AS
BEGIN

PRINT 'THIS ONE'
SELECT 1 As Data

END

Find PowerShell code, which executes proc:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=ForTests;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "doTest"
$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]

Execute result gave this

Data
----
1

I done everything same what you wrote and I have result without assigning params

answered Mar 6, 2015 at 13:48

Darka

2,7681 gold badge17 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Naigel Over a year ago

thank you for the complete test, finally the code seemed to be fine but I was testing wrong

HappyLee Over a year ago

how to execute the stored procedure if param1 and param2 are to be supplied explicitly?

GoldBishop Over a year ago

@HappyLee use the Parameters property $sqlCmd.Parameters.Add("@hdr", $hdr). $hdr can be the value you want to pass and of course modify @hdr for the parameter expected

Collectives™ on Stack Overflow

Calling SQL Server stored procedure from powershell

1 Answer 1

Made test proc:

Find PowerShell code, which executes proc:

Execute result gave this

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Made test proc:

Find PowerShell code, which executes proc:

Execute result gave this

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related