question

ChristopherJack-1763 avatar image
0 Votes"
ChristopherJack-1763 asked LimitlessTechnology-2700 answered

Passing paramater to sql server stored procedure using Powershell

Hi,

I am trying to pass a paramater to a sql server stored procedure using

$SqlCmdItem.CommandText = $("EXEC [zoomfs].[LandmarkParcelChecker]" $Shipmentreferences[$y] );

However i am getting the error


Line |

19 | … $("EXEC [zoomfs].[LandmarkParcelChecker]" $Shipmentreferences[$y] );
| ~~~~~~~~~~~~~~~~~~~
| Unexpected token '$Shipmentreferences' in expression or statement.


THE SP Looks like

ALTER PROCEDURE [ZoomFS].[LandmarkParcelChecker]
-- Add the parameters for the stored procedure here
@PackageID varchar(24)
AS
BEGIN

If I have

$SqlCmdItem.Parameters.AddWithValue("@PackageID", "85800000000001252552");

I get the error

28 | $SqlAdapterItem.Fill($DataSetItem);
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Exception calling "Fill" with "1" argument(s): "Procedure or function 'LandmarkParcelChecker'
| expects parameter '@PackageID', which was not supplied."

If I check the value in the variable it looks set, so I am confused as to why it is saying there is nothing there?



126718-image.png


sql-server-generalsql-server-transact-sqlwindows-server-powershell
image.png (203.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered OlafHelper-2800 edited

$SqlCmdItem.CommandText = $("EXEC [zoomfs].[LandmarkParcelChecker]");

You defined the command type as StoredProcedure, so SqlCommand expects only the SP name, nothing else.
But you wrote a EXEC in front, so it looks mor like a SQL statement and then you have to define the parameter in the command text.

Remove the EXEC or add the parameter name like

 $SqlCmdItem.CommandText = $("EXEC [zoomfs].[LandmarkParcelChecker] @PackageID");

See SqlCommand.CommandType Property => Remarks:
When you set the CommandType property to StoredProcedure, you should set the CommandText property to the name of the stored procedure.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LimitlessTechnology-2700 avatar image
0 Votes"
LimitlessTechnology-2700 answered

Hello,

Thank you for your question.


Please follow this :

$commands = $SQLResult | foreach-object -process { $_.output }>> output.ps1


If the reply was helpful, please don't forget to upvote or accept as answer.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.