1

Essentially what I'm trying to run is this: 

cd /d W:\RemoteMirror\ && winscp.com /ini=nul /script=Remote_Mirror_WinSCP.txt

or this:

cd /d \\fileServer\RemoteMirror\ && winscp.com /ini=nul /script=Remote_Mirror_WinSCP.txt

Of course, with SQL Server, I can't use UNC paths. But I'm not sure how to do it otherwise. I'm not too familiar with PowerShell yet, but that'd be my best guess.

Martin Prikryl
  • 188,800
  • 56
  • 490
  • 992
Blake Norwood
  • 63
  • 1
  • 8

1 Answers1

1

UNC path cannot be a current working directory (that's a Windows limitation, it has nothing to do with SQL server).

As @lit already commented, you can temporarily allocate a drive letter for a UNC path and change working directory to it at once, using pushd command.

Alternatively, modify your WinSCP script not to rely on the current working directory.

Just use full UNC paths in the script, like:

get "/remote/path/*" "\\fileServer\RemoteMirror\"

See also Using batch file and WinSCP to download files from the FTP server to file server (shared folder)

Or, if you need to make the script working with different directories, use a parameterized script:

get "/remote/path/*" "%1%\"

And run it like:

winscp.com /ini=nul /script=Remote_Mirror_WinSCP.txt /parameter \\fileServer\RemoteMirror

See a full example for using parameterized scripts.

Martin Prikryl
  • 188,800
  • 56
  • 490
  • 992
  • I am able to run the script now but the files don't move and the step seems to run infinitely as if the prompt window is awaiting user input. Is there a way to show the cmd window while running it as a scheduled task? @martin-prikryl – Blake Norwood Nov 16 '17 at 17:17
  • Enable logging using `/log=“C:\path\log.log”` command-line switch. – Martin Prikryl Nov 16 '17 at 21:43
  • So redirect output of `winscp.com` to a file: `winscp.com ... > c:\path\winscp.log`. Do you even have `winscp.com` in `PATH`? Does the script even work, if you execute it manually? – Martin Prikryl Nov 17 '17 at 07:33
  • It does work when executing it manually and it logged when ran manually. It is in the PATH variables. This is what I have in my file that's being called: `# Connect open sftp://xxx:xxx@xxx.com/ -hostkey="ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx" # Force binary mode transfer option transfer binary # Download file to the local directory synchronize -filemask="*.pgp|*/" local %1% / # Disconnect close # Exit WinSCP exit` – Blake Norwood Nov 17 '17 at 15:43
  • `cmd.exe /k "winscp.com /ini=nul /log="C:\tools\log.log" /script=C:\tools\Mirror_WinSCP.txt /parameter \\fs1\dataprocessing\RemoteMirror` That's what I have in the SQL task. And I just realized that you're the developer for WinSCP. Thank you SO much for helping me with this! – Blake Norwood Nov 17 '17 at 15:55
  • Did you try the redirecting? – Martin Prikryl Nov 17 '17 at 16:02
  • Like this? `cmd.exe /k "winscp.com /ini=nul /log="C:\tools\log.log" /script=C:\tools\Mirror_WinSCP.txt /parameter \\fs1\dataprocessing\RemoteMirror\ > C:\tools\log.log`? – Blake Norwood Nov 17 '17 at 16:11
  • Yes. Though why the `cmd /k`? – Martin Prikryl Nov 17 '17 at 17:45
  • I actually don't remember why I had that in there... I guess that would cause the infinite pause in the scheduled task, too, actually... Updated to this: `cd C:\tools\ && winscp.com /ini=nul /script=C:\tools\Mirror_WinSCP.txt /parameter \\fs1\dataprocessing\RemoteMirror\ > C:\tools\log.log` New error is "winscp.com is not recognized as an internal or external command" so it seems like it's not picking up the PATH var even though I installed it for all users... – Blake Norwood Nov 17 '17 at 19:13
  • With the help of google and your answer in the forums, I found the answer and everything works! https://winscp.net/forum/viewtopic.php?t=24359 – Blake Norwood Nov 17 '17 at 19:18