I have an application that has SQL Anywhere UNLOAD a file... the next step is to send it off to the world via SFTP.
It occurs to me that one way to do that is with a command line driven sFTP client, run from SQL anywhere's command shell (xp_cmdshell) from my own procedure.
Has anyone been down this path and want to share the pitfalls that I'm about to encounter?
asked 15 Apr '10, 23:20
I have scripted a file upload for a compliance check of our addresses. We used the free WinSCP as sFTP Client for the upload.
First Step was to created a Batch file that took the exported file and started the upload.
I was afraid that a long running task could block the server so I did not start the batch file with xp_cmdshell directly. Instead I created a scheduled task that has no repeating interval set. This scheduled task is then triggered by the server with the following command (W2003).
set cCommand = string('"%SYSTEMROOT%\SYSTEM32\SCHTASKS.EXE" /run /tn ', cTaskName, ' > ', 'TaskOutput.LOG'); @return_code = CALL xp_cmdshell( cCommand, 'no_output');
This has two advantages
answered 16 Apr '10, 06:33
After spending two days wrestling with what should be a simple problem, I'm still not sure I have a production-ready solution.
Yes, Breck, the unscheduled event is the way to go, but the problem is before that.
I tried Ipswitch WS_ftp and WinSCP both. While I far prefer the UI to WS_FTP, and I seems more robust all around, I could not for the life of me get it to work in the xp_cmdshell environment.
My best guess is that it is one of two problems: First, a permission problem -- my tests of the command string on a DOS prompt work fine. (These run on the Windows workstation UI, under my logged-in ID.) When I wrap the command string in xp_cmdshell, it runs on the server on top of dbsrv9.exe, which is running under the 'local system' account. Even though I have 'Allow Service to interact with desktop' checked, I get not desktop interactivity. There is no apparent logging anywhere, and even a ' > mylog.txt' tacked on the the end of the command string produced nothing but a 0 byte file.
A second possibility is that the WS_ftp code has something in there to detect that it is running on a command shell in a service, and shuts down. It may be a violation of their license to use it in this manner.
The reall problem is that a program running under xp_cmdshell has no way to communicate back to the caller (in my case a stored procedure) that tells what went wrong. I can only guess, and so far, none of my guesses have proven fruitful. While my problem du jour may well be solvable, I don't really want to put code into a production environment that is not easily manageable and maintainable -- which includes finding error messages.
WinSCP fared a little better. We were able to produce a command string that worked, but we don't have the ability to pass back to the calling procedure any indication that there was a problem.
So... I think the answer is.... the application should first have the database server do its thing, then handle the sFTP transfer. Asking the database to do this via a xp_cmdshell is not really a viable solution.
Somebody please make me wrong!
answered 20 Apr '10, 01:12
Ron, as to the command prompt logging problem, you might use the CMD "2>" redirection operator that redirects the stderr output to a file. This is useful to log shell command results to a file that can be checked by the database server afterwards. (Possibly in addition to redirect stdout to a file via the > operator.)
Following is a simple example taken from a newsgroup thread "Extend documentation for xp_cmdshell" in sybase.public.sqlanywhere.product_futures_discussion from 14 Aug. 2007:
We use similar code to check whether the server was able to (dis-)connect to network shares. In our case, the server is running on local system account and not allowed to interact with the desktop. As the error log is accessable to the server, it's quite easy to parse the log for errors lateron.
However, I can't claim whether this works well with FTP clients.