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 Profile photo of H Man H Man 2 years, 6 months ago.

  • Author
    Posts
  • #15993
    Profile photo of H Man
    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
    Profile photo of H Man
    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
    Profile photo of Don Jones
    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
    Profile photo of H Man
    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
    Profile photo of H Man
    H Man
    Participant

    it just returns back to the prompt no erros

  • #16036
    Profile photo of H Man
    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
    Profile photo of Martin Nielsen
    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.