question

Walkerx-7944 avatar image
0 Votes"
Walkerx-7944 asked Walkerx-7944 commented

Powershell Invoke-WebRequest not timing out

I've created a script that downloads files from various sources and save these to a local network share for processing

If I run the script directly from powershell it works and reports error if cannot find the file needed

But if I run the powershell script through SQL Server, if the web file is not found it just hangs the job and does not timeout or report back the error 404

using the command

$Response = Invoke-WebRequest -Uri $source -Outfile $destination -Credential $cred
$StatusCode = $Response.StatusCode

I've tried using the -TimeoutSec and -MaximumRetryCount but these don't work either

Any ideas

Thanks

sql-server-generalwindows-server-powershell
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Walkerx-7944 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
Walkerx-7944 avatar image
0 Votes"
Walkerx-7944 answered Walkerx-7944 commented

Found the issue, its when it can't overwrite the file during the download process it fails and just hangs

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Walkerx-7944

If you're only downloading a file, using Invoke-WebRequest isn't the best way to do it. It's "easier" but you're still parsing the HTML (and risking another "hang" if the HTML isn't somehow acceptable to the parser).

A faster way is to use .Net directly:

 $url = "http://download.windowsupdate.com/c/msdownload/update/software/updt/2021/05/windows10.0-kb5003537-x64-ndp48_1fbdca72e07a18937ae2c81c032afecbb7fabe45.msu" 
 $SaveTO = "C:\Updates\" + (Split-Path -Path $url -Leaf)
    
 Try{
     (New-Object System.Net.WebClient).DownloadFile($url, $SaveTO)
 }
 Catch{
     Write-Host "Couldn't download $(Split-Path -Path $url -Leaf)"
     $_
 }

Now exceptions will be caught!

0 Votes 0 ·

thanks i'll see what i can do with that as we use a proxy, authenication and security

0 Votes 0 ·
RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered

You should add a SQL server tag to your question. There isn't enough information in your question to know what's going on in your script, or how you're tasking the SQL agent to run the script.

Since the script functions normally when it's run from PowerShell, the problem is likely to be in how the end of the script is handled by SQL agent.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Walkerx-7944 avatar image
0 Votes"
Walkerx-7944 answered RichMatheisen-8856 commented

the script is running using SQL Server Agent as a daily job.

the script will exit fine if the file is found and does not receive a 404 error

if it receives a 404 error then i've set the script to exit with -1 which informs sql to treat the job as failed

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If the script is terminated by using "Exit -1" then ERRORLEVEL should be set to "-1". The SQL Agent should be able to see that.

I have no SQL server to try anything with, though. I know it works if I run PowerShell from CMD.exe because an "ECHO ERRORLEVEL" displays a "-1". I really don't think this is a PowerShell problem.

0 Votes 0 ·
Walkerx-7944 avatar image
0 Votes"
Walkerx-7944 answered

SQL Agent see's an error if it is required, the issue is that invoke-webrequest just hangs if the url returns a page not found, all other links in the script work.

the powershell script is working fine when not running via the sql server agent, just because things work from cmd does not mean they will work when running through their own environment within sql server agent.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @Walkerx-7944,

Please refer to this article. Quote from the bottom of it.
To bubble up Windows PowerShell errors to SQL Server Agent, you’ll need to do one of the following:
A. Set your $ErrorActionPreference = “Stop”
B. Set ErrorAction at the cmdlet-level (more granular)
C. Use Try/Catch with ErrorActionPreference or ErrorAction
D. Continue, and fail the SQL Server Agent job

Best regards,
Seeya


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Walkerx-7944 avatar image
0 Votes"
Walkerx-7944 answered

ignore the final errors being returned back to sql server agent, this is not the problem

i already use try/catch but it doesn't get to the catch when the url is not found which is why i'm confused

i have it downloading multiple files and if any fails it sets the marker to say the process has failed but via sql server agent it is not doing this step correctly as it just hangs, but if i run the same script via powershell ise the catch works and returns the 404 which i then set flag to say the process has failed.

ie

try {
$Response = Invoke-WebRequest -Uri "https://mywebsite.com/myfiletodownload.zip"-Outfile "c:\mydownloads\downloadedfile.zip" -Credential $cred
$StatusCode = $Response.StatusCode
}
catch
{
$StatusCode = $.Exception.Response.StatusCode.value_
}


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MotoX80 avatar image
0 Votes"
MotoX80 answered MotoX80 edited

i have it downloading multiple files

Build a test script that all it does is download one file that does not exist. That way you can verify that your error checking works and other code that you have in the script isn't causing the hang.

but if i run the same script via powershell ise the catch works

The issue with ISE is that it doesn't reset all variables when you hit F5 to run the script. So it you run the script multiple times, you may be referencing variables that were set during a prior execution. Are you running ISE on the server or on your desktop?

In the testing that I did in PS 5.1, this statement...

 $StatusCode = $.Exception.Response.StatusCode.value_

...should be

 $StatusCode = $_.Exception.Response.StatusCode

Also add a transcript. Review the contents of the 2 log files after you have SQL run it.

This should be the entire script.



     start-transcript -Path c:\temp\ps.log
     try {
         # add your code to build $cred here
        
         "************** Downloading the file **************" | out-file c:\temp\MyScript.log
         $Response = Invoke-WebRequest -Uri "https://www.microsoft.com/myfiletodownload.zip" -Outfile "c:\temp\downloadedfile.zip" -Credential $cred
         "Invoke-webrequest has finished" | out-file c:\temp\MyScript.log -Append
         $StatusCode = $Response.StatusCode
     }
     catch
     {
         "************** We crashed ******************" | out-file c:\temp\MyScript.log -Append 
         $_.exception.message | out-file c:\temp\MyScript.log -Append
         $StatusCode = $_.Exception.Response.StatusCode
     }
     "************** Complete **********************" | out-file c:\temp\MyScript.log -Append
     "Statuscode = {0}" -f $StatusCode | out-file c:\temp\MyScript.log -Append
     Stop-Transcript

Assuming that works, then test a successful download.








5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered

Can you identify the URLs that cause the problem? If so, do those URLs cause the same problem when run from PowerShell?

I've had problems (hanging) similar to this due to problems with the HTML returned from a site (including Microsoft). The problem isn't with PowerShell but with the HTML parser in the O/S.

Try adding "-UseBasicParsing" to the Invoke-WebRequest. The HTML code will still be still available in the Content property of your $Response object.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.