I am a newbie to PowerShell and trying to a build a script that does the following functionality. Could someone please help me with this?
I would like to setup a new PowerShell script that invokes my Database Stored Procedure concurrently. I am currently having a Control table that has a Job_ID column and a Code column. There might be more than one Job_ID for a code value in the Control table. Based on the code value I pass in the PowerShell along with a date, I would like the PowerShell to trigger the Stored Procedure which is expecting “Job_ID” and “MyDate” as input parameters.
FYI, I am using SQL Server 2016.
PS C:\PowerShell> $PSVersionTable.PSVersion Major Minor Build Revision ----- ----- ----- -------- 2 0 -1 -1
Here is some sample data for your reference:
CREATE TABLE control_table(JOB_ID INT, CODE VARCHAR(5)); INSERT INTO control_table(1, 'ABC'); INSERT INTO control_table(2, 'ABC'); INSERT INTO control_table(3, 'ABC'); INSERT INTO control_table(1, 'DEF'); INSERT INTO control_table(1, 'GHI'); CREATE PROCEDURE myschema.run_job (@JOB_ID INT, @MyDate DATE) AS BEGIN -- Do Something END
When I run the PowerShell script by passing 'ABC" as code, it should execute all the three jobs concurrently by reading the control table.
.\test.ps1 –MyCode “ABC” –Dt “12/27/2018”