Powershell only executes first sql statement in the sql file

Welcome Forums General PowerShell Q&A Powershell only executes first sql statement in the sql file

Viewing 4 reply threads
  • Author
    Posts
    • #277392
      Participant
      Topics: 1
      Replies: 0
      Points: 12
      Rank: Member

      Hello Everyone,

      I am trying execute two simple SELECT statements placed in a sql file and then trying to execute the sql file via powershell and dumping the results in a text file using the below code. However it only executes the first select statement.

      $SQL = Get-Content -Path “C:\SQL Reports\Daily Checks.sql”

      Invoke-Sqlcmd -Username “*****” -Password “*****” -ServerInstance “*****” -Database “*****” -Query $SQL | Out-File -FilePath “C:\SQL Reports\powershelloutput.txt”

       

      It is just printing the results of the first sql in the output file and not the second one.

      Can someone please guide what may be wrong or if the above method is not supposed to execute multiple sqls in a sql file.

      The simple sql statements are like below

      SELECT * FROM TABLE1

      SELECT * FROM TABLE2

       

      Thanks,

      AK

    • #277461
      Participant
      Topics: 9
      Replies: 706
      Points: 2,837
      Helping Hand
      Rank: Community Hero

      Just taking a guess, what if you add -Raw to the Get-Content command?

    • #277569
      Participant
      Topics: 5
      Replies: 252
      Points: 999
      Helping Hand
      Rank: Major Contributor

      To add to Doug’s comment.  By default Get-Content would return an array of strings where each line of the text file is a member of that array.  When you add the -Raw switch it returns a single string with the entire contents of the file including line breaks.

      I believe the -Query parameter expects a single SQL statement.  If that is true then you shouldn’t use the -Raw switch and iterate over the array like this:

       

    • #277575
      Participant
      Topics: 17
      Replies: 1951
      Points: 3,987
      Helping Hand
      Rank: Community Hero

      Or…per the documentation…

      Example 2: Invoke commands in a script file and save the output in a text file
      PowerShell

      This command reads a file containing Transact-SQL statements and SQLCMD commands, runs the file, and writes the output to another file.

      The output file may contain proprietary information, so you should secure the output files with the appropriate NTFS permissions.

    • #277581
      Participant
      Topics: 5
      Replies: 252
      Points: 999
      Helping Hand
      Rank: Major Contributor

      Good call Rob:

      Get-Help Invoke-Sqlcmd

      Get-Help … it works every time.  If you don’t believe me, ask the god of thunder.  🙂

       

Viewing 4 reply threads
  • You must be logged in to reply to this topic.