SAP SQL Anywhere

Question

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?

Thanks!

Answer 1

3

Dear Ron.

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

The sFTP Process is competely separated from the Server Task
You can modify the Upload process without touching your server.

HTH Thomas

permanent link

answered 16 Apr '10, 06:33

Thomas Dueme...
2.7k●28●39●65
accept rate: 17%

3

An alternative is to fire sftp via xp_cmdshell from within an unscheduled EVENT using TRIGGER EVENT. Events run on their own connection, and only that connection will wait; everything else inside SQL Anywhere will proceed normally. This has the advantage of control being returned to the event when the task is complete, and the event could record that fact in a table for other processes to check. Caveat: I have not personally done this with (s)ftp.

(16 Apr '10, 08:44) Breck Carter

Thanks Thomas & Breck.

(16 Apr '10, 14:57) Ron Hiner

flat view

Answer 2

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!

Answer 3

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:

-- Create a directory on Windows with error handling
begin
   declare strCmdErrorLog varchar(255);
   declare nRetCode int;
   declare strMsg varchar(255);

set strCmdErrorLog = 'C:\\TEMP\\CmdError.Log';

-- try to create a directory
   nRetCode = call xp_cmdshell('MD %TMP%\Test 2> ' || strCmdErrorLog, 'no_output');
   if nRetCode = 0 then
      message 'CMD succeeded';
      -- clear empty CmdError.Log afterwards
      call xp_cmdshell('DEL ' || strCmdErrorLog, 'no_output');
  else
      -- read and log the error message
      strMsg = call xp_read_file(strCmdErrorLog);
      set strMsg = 'CMD failed with error: ' || strMsg;
      message strMsg type warning;
   end if;
end;

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.

SAP SQL Anywhere

Has anyone used xp_cmdshell('some sftp client') ?

Follow this question

Related questions