Pass parameter to SQL Server stored procedure in Powershell

Christopher Jack 1,611

Hi,

I have the following code.

$SqlCmd.CommandText = $("EXEC [ZoomFS].[ReboundHeader] @SalesDocNum = $comps[i] ");

The error I am receiving is

Exception calling "Fill" with "1" argument(s): "Could not find stored procedure ''."ps1:55 char:1

$SqlAdapter.Fill($DataSet);

~~~~~~~~~~~~~~~~~~~~~~~~~~

CategoryInfo : NotSpecified: (:) [], MethodInvocationException

FullyQualifiedErrorId : SqlException

I dont think the syntax is correct.

Any help appreciated.

Erland Sommarskog 101K Reputation points MVP

2021-02-10T22:49:09.483+00:00

The snippet you post looks good to me, at least from an SQL Server perspective. (I'm not terribly good at PowerShell. Could you post the full code?
Dan Guzman 9,211 Reputation points

2021-02-10T23:04:42.52+00:00

Add the relevant code that creates the command and sets the properties to your question.

I suspect you may have specified $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure. In that case, the CommandText should contain only the proc name with the parameter added to the command separately.

That said, you should be able run the proc with an execute statement like in your question by specifying $SqlCmd.CommandType = [System.Data.CommandType]::Text.

2 answers

Jason Kohlhoff 21 Reputation points

2021-02-11T16:20:13.127+00:00

@Christopher Jack The following stackoverflow post should help you pass parameters and fill a SqlDataAdapter.

https://stackoverflow.com/questions/15340496/pass-parameters-from-powershell-to-stored-procedure
Please sign in to rate this answer.

1 person found this answer helpful.
Christopher Jack 1,611 Reputation points

2021-02-11T16:35:41.573+00:00

Thanks jkw6 I had looked at that but could figure out how to apply it in this instance.

Will have another look.
Sign in to comment
Mico Mi 1,921 Reputation points

2021-02-10T07:19:44.193+00:00

Hi,
Powershell scripts for specific products, such as SQL or Exchange, are out of the scope of WS PS support. Appreciate your understanding.

Thanks for your time!
Best Regards,
Mico Mi

-----------------------------

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.

1 person found this answer helpful.
Erland Sommarskog 101K Reputation points MVP

2021-02-10T22:45:54.827+00:00

Wonderful answer Mico Mi, but I think that the original poster would have appreciated if you had stayed silent. After all, you had nothing to say, had you?

(And don't blame the poster for the tags. There was an SQL Server tag originally. That's why I saw the post.)
Sign in to comment