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?