Welcome Forums General PowerShell Q&A Run multiple sql scripts in powershell

Viewing 1 reply thread
  • Author
    Posts
    • #52932
      Participant
      Topics: 1
      Replies: 0
      Points: 0
      Rank: Member

      I am trying to create a PowerShell script that runs multiple scripts on a database.
      here is what I have so far. It seems to only work when there is one script but when I add the second script starts to generate errors. Any help will be much appreciated.

      $crlf = [System.Environment]::NewLine
      [string] $TNS = 'LINKED_SERVER'
      $sqlfile = "c:\temp\script1.sql", "c:\temp\script2.sql"
      $connection=New-Object DATA.OracleClient.OracleConnection("Data Source=$TNS;User Id=TEST;Password=XXXXXXXX")
      
          foreach ($sqlfiles in $sqlfile){
      
          $FileLines = Get-Content $sqlfile
          $query = [string]::Join($crlf,$FileLines)
          #log start time
          [datetime]$Now = Get-Date
          [string] $TSbegin = $Now.ToString("yyyy-MM-dd HH:mm:ss")
          echo "`n`nExecuting script $sqlfiles Started at: $TSbegin"
          [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null
          
          $connection.Open()
          $res = (new-Object DATA.OracleClient.OracleCommand($query,$connection))
      	$output = $res.ExecuteScalar()
           
          #log end time
          [string]$TSend = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
          $Duration = New-TimeSpan -Start ($TSbegin) -End (Get-Date)
          echo "`n$('{0:N0}' -f $output) Rows returned"
          echo "`nExecuting script $sqlfiles completed at: $TSend"
          $Duration |Format-Table -AutoSize 
          # Remove-Variable res
         }
      
      $connection.Close()
      
    • #52946
      Moderator
      Topics: 2
      Replies: 525
      Points: 24
      Team Member
      Rank: Member

      Hi Danny,

      I think you’ve got your variable names mixed up (plural vs. singular).

      Line 3 you create an array of file names as $sqlfile
      $sqlfile = “c:\temp\script1.sql”, “c:\temp\script2.sql”

      Line 6 you iterate over the $sqlfile array and each value in the array gets assigned to $sqlfiles
      foreach ($sqlfiles in $sqlfile){

      Line 8 you don’t use the $sqlfiles variable but $sqlfile which contains the array with all filenames. That means Get-Content will return the lines of all files into your $FileLines variable.
      $FileLines = Get-Content $sqlfile

      I believe it would be best if you would do the following:

      Line 3: Change $sqlfile to $sqlfiles
      $sqlfiles = “c:\temp\script1.sql”, “c:\temp\script2.sql”

      Line 6: Swap the variable names around
      foreach ($sqlfile in $sqlfiles){

      Example with the suggested modifications:
      https://gist.github.com/Dan1el42/78d344e873ec1aba85207e0dbfa80c63

Viewing 1 reply thread
  • The topic ‘Run multiple sql scripts in powershell’ is closed to new replies.