SQL Server stored procedures suddenly cannot run WinSCP

Advertisement

mcolthurst
Joined:
Posts:
2
Location:
Seattle

SQL Server stored procedures suddenly cannot run WinSCP

WinSCP version 5.1.7.0
Using it for SFTP

I have built some stored procedures that calls WinSCP from a .BAT file.
The script it references downloads some marker files and loads them into a table.
I then loop through those marker files to know which files to download next.
My next stored procedure references the first marker file in the list, calls a different .BAT file which references a script that then downloads two files based on two parameters. The script then closes and exits.
I loop through this process until all files are complete.
Between a span of 5 days, I processed probably at least 20,000 files using this job.

Today, the SQL job was hung at the step to download the marker files.
I stopped the job. And tried to run it manually. Two results could happen:
1. The job just runs and runs and does nothing.
2. The job quickly finishes with success.
However, WinSCP is not being called. None of the files are downloaded.

I have tried to run each stored procedure with no luck. Nothing is written to the log file either.
At this point I tried building a new procedure, calling a new .BAT file and script that downloads just one file. No luck though.

I can run the .BAT file from a Windows Command line and it runs just fine.

I also tried copying over the latest beta version and it's still not getting executed.

The one thing that fixes the problem is if I reboot my database server.

After the reboot the jobs will run successfully for awhile processing thousands of files.

Anyone else see this behavior?

Thanks!

Reply with quote

Advertisement

shunter
Joined:
Posts:
1

I'm a co-worker of the post author, mcolthurst. We've also seen the following behavior (related or unrelated):

- The WinSCP.exe file that we'd been using successfully for a couple of weeks was all of a sudden showing a file size of "0" bytes. We're not sure why, and copying a new WinSCP.exe file in place of it did not fix our issue. We've seen this twice.

- Upon occasion, we'll see up to (4) WinSCP.exe processes running via TaskManager, and they don't appear to ever end or clear themselves out. Manually closing those processes is possible, but WinSCP still doesn't work when run from a SQL stored procedure.

- While our process is "broken", we can successfully run "ftp.exe" from the same stored procedure instead of WinSCP. It works just fine.

- As mcolthurst shared, while the process is "broken", we can successfully run the same processes MANUALLY without issue. It's broken when trying to run it via stored procedure.

- This same behavior is happening on two different servers -- dev and prod.

- While our process is "broken", running WinSCP with the /debug (logging) switch doesn't appear to log anything at all.

Any help out there? This issue is seriously client-impacting for us right now.

Thank you.[/list]

Reply with quote

mcolthurst
Joined:
Posts:
2
Location:
Seattle

Great. I'll run with the new version and will reply if the problem is NOT resolved. But, I was able to verify that I was also getting the Exit Code 3, so I'm guessing this will fix it.

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,271
Location:
Prague, Czechia

mcolthurst wrote:

Great. I'll run with the new version and will reply if the problem is NOT resolved. But, I was able to verify that I was also getting the Exit Code 3, so I'm guessing this will fix it.
Note that you still need to restart the system to recover resources exhausted by the buggy version.

Reply with quote

Advertisement

You can post new topics in this forum