powershell - kicking off 10 invoke-sqlcommand simultaneously RRS feed

  • Question

  • hi 

    i've been trying to kick off 10 invoke-sqlcommand with no lock

    what i have is the following

    For ($i=1; $i -le $Threads; $i++) 

     $send = 'Invoke-Sqlcmd  -QueryTimeout 0 -ServerInstance '+$APSServer+' -Database '''+$APSsource+'''+ -Username +'+$APSUser+' -Password '+$APSpassword+' -Query '+'@'''+"`n"+ $ctas+"`n"+'''@'

    $command =[scriptblock]::Create("cmd /c start powershell -Command {"+ $send +'}')

    $JobName = 'Worker'+$i

        $this = start-job  $command -name $JobName
        Receive-Job -Job $this


    The ctas is a large sql command that has new lines and ' in it 

    any ideas how i can get around this?



    • Moved by Bill_Stewart Monday, March 12, 2018 9:32 PM This is not "teach me basics step-by-step" forum
    Wednesday, February 7, 2018 9:20 PM

All replies

  • Use jobs and not CMD.

    Help Start-Job -Online

    You need to start by learning PowerShell. Almost every line is a bad guess. No telling how you were led to this syntax but it is not PowerShell.

    1..10 |
        $jobs += Start-Job -ScriptBlock { Invoke-Sqlcmd -Query $ctas -ServerInstance $APSServer -Database $APSsource -Username $APSUser -Password $APSpassword }
    $jobs | Wait-Job | Receive-Job

    A query can be a multiline string or you can use a file.

    Invoke-SqlCmd -InputFile c:\test\query.sql...


    • Edited by jrv Wednesday, February 7, 2018 9:44 PM
    • Marked as answer by Eddy.a Wednesday, March 14, 2018 1:31 AM
    • Unmarked as answer by Eddy.a Wednesday, March 14, 2018 1:32 AM
    Wednesday, February 7, 2018 9:41 PM
  • hi yes i am a novice when it comes to PS.... 

    the issue with the code though is that i am passing the query as a param ... which doesnt seem like it's being passed to the job's thread.

    Cannot validate argument on parameter 'Query'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.

    how can i fix that?


    Wednesday, February 7, 2018 9:49 PM
  • Post the query.  Is it a file?  You have to wake up your head and think about what you are doing and what the help says you need.

    Do NOT use your code.  It will never work without extensive repair and is completely unnecessary.  Jobs are separate copies of PowerShell.


    Wednesday, February 7, 2018 9:54 PM
  • wow you must be someone people love to work with/hire .... 

    my code worked and the "extensive repair" took 2 minutes 
    for whoever is looking for an answer please see below

        1..$Threads |
        $jobs += Start-Job -ScriptBlock { Invoke-Sqlcmd -Query  $args[0] -ServerInstance $args[1] -Database $args[2] -Username $args[3] -Password $args[4] }  -ArgumentList  $ctas,$APSServer, $APSsource,$APSUser,$APSpassword
        $jobs | Wait-Job | Receive-Job

    arguments allows vars defined to be passed to the other side.


    Wednesday, February 7, 2018 10:08 PM
  • That is my code.  You didn't show any of that in your question.

    Can you see now why, how Start-Job can do the whole thing.  If you would take the time to actually learn PowerShell this would all be obvious.  Trying to learn PS by guesswork and copying will take forever and you will never learn it correctly.


    Wednesday, February 7, 2018 10:11 PM