Run multiple sql scripts in powershell

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Daniel Krebs Daniel Krebs 3 months ago.

  • Author
    Posts
  • #52932
    Profile photo of Danny Ngo
    Danny Ngo
    Participant

    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
    Profile photo of Daniel Krebs
    Daniel Krebs
    Participant

    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:

You must be logged in to reply to this topic.