Runs fine in the ISE but not when ran as a script?

This topic contains 6 replies, has 3 voices, and was last updated by  H Man 3 years, 5 months ago.

  • Author
    Posts
  • #15993

    H Man
    Participant

    $date = Get-Date -Format MM.d.yyyy
    $OutputDir = "C:\Reports\SQL"
    $File = "Sql_" + "$date" + ".csv"
    $OutputFile = Join-Path $OutputDir $file

    if(!(Test-Path -Path $OutputDir))
    {mkdir $OutputDir}
    else {continue}

    #SQl Data—————————————————————————————————————–
    #Connection Strings
    $Database = "db1"
    $Server = "server11"

    # Connect to SQL and query data, extract data to SQL Adapter
    $SqlQuery = "DECLARE @PreviousDay Date, @toDay Date
    SET @PreviousDay = GETDATE() – 1
    SET @toDay = GETDATE()
    PRINT @PreviousDay
    PRINT @toDay

    SELECT Phone1

    FROM db1.dbo.Lead (NOLOCK)
    WHERE ClientrelationshipId IN (4963,118, 4964, 5109)
    AND CreatedDate >= @PreviousDay
    AND CreatedDate < @toDay ORDER BY LeadId " $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $SqlQuery $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $nRecs = $SqlAdapter.Fill($DataSet) $nRecs | Out-Null #Populate Hash Table $objTable = $DataSet.Tables[0] #Export Hash Table to CSV File $objTable |Export-CSV $OutputFile -NoTypeInformation #SQl Data—————————————————————————————————————– #ftp server upload——————————————————————————————————– $Dir= $OutputFile $ftp = "ftp://mysite.com/dir" $user = "user" $pass = "password" $webclient = New-Object System.Net.WebClient $webclient.Credentials = New-Object System.Net.NetworkCredential($user,$pass) #list every sql server trace file foreach($item in (dir $Dir)){ "Uploading $item..." $uri = New-Object System.Uri($ftp+$item.Name) $webclient.UploadFile($uri, $item.FullName) } #ftp server upload——————————————————————————————————–

  • #15995

    H Man
    Participant

    When ran a portion at a time everything executes perfectly! when ran all at once i get nothing no error no out at all

  • #15996

    Don Jones
    Keymaster

    So... What's it do? Error messages? Different behavior? We can't easily run your script so understanding what's happening would be useful in helping you.

  • #15997

    H Man
    Participant

    nothing no errors nothing it just completes. when I high light portions and run in the ISE works fine. connects to sql grabs query and exports data to csv and uploads. when ran a scheduled task, or the whole script at once it just complete with no output at all. I would like to make this a scheduled job

  • #15998

    H Man
    Participant

    it just returns back to the prompt no erros

  • #16036

    H Man
    Participant

    Well Done Martin! your advise was correct and now the script runs!
    I was using the continue the wrong way
    Thank you

  • #16001

    Martin Nielsen
    Participant

    That would be because you have


    if(!(Test-Path -Path $OutputDir))
    {mkdir $OutputDir}
    else {continue}

    Continue starts a new iteration of the current loop. An if statement is not a loop, so in your case, when you're running it as a script, continue exits the script.

    What you want is to do away with the else { continue } part entirely. It's not necessary.

You must be logged in to reply to this topic.