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!

asked 15 Apr '10, 23:20

Ron%20Hiner's gravatar image

Ron Hiner
880202427
accept rate: 9%


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

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

HTH Thomas

permanent link

answered 16 Apr '10, 06:33

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.6k243561
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

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!

permanent link

answered 20 Apr '10, 01:12

Ron%20Hiner's gravatar image

Ron Hiner
880202427
accept rate: 9%

Check out this thread, it contains lots of links related to SSH, SFTP and FTPS: http://social.msdn.microsoft.com/Forums/en-US/netfxnetcom/thread/f24fe285-6028-4821-b843-d3be6756308c

(20 Apr '10, 10:53) Breck Carter

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.

permanent link

answered 22 Apr '10, 10:53

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

edited 22 Apr '10, 11:00

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×7

question asked: 15 Apr '10, 23:20

question was seen: 3,200 times

last updated: 22 Apr '10, 11:00