April 22, 2011 at 12:10 pm
This is VERY frustrating as I actually have this working on several servers but it is not working in my current code. I KNOW I am missing something stupid and need a second pair of eyes.
Here is the code:
declare @FTPHostIP varchar(50),
@FTPLogin varchar(50),
@FTPPassword varchar(50),
@FTPSourceDirectory varchar(255),
@FTPDestinationDirectory varchar(255),
@FTPMethod varchar(50),
@FTPPort int,
@FTPFlags varchar(50),
@FTPCommand varchar(8000),
@CoreFTPExeDirectory varchar(255)
set @FTPHostIP = 'jcsql.com'
set @FTPLogin = 'test'
set @FTPPassword = 'password'
set @FTPSourceDirectory = '/outgoing'
set @FTPDestinationDirectory = 'R:\'
set @FTPMethod = 'ftp://'
set @FTPPort = 21
set @FTPFlags = '-O'
set @CoreFTPExeDirectory = 'C:\Program Files (x86)\CoreFTP\coreftp.exe'
SET @FTPCommand = '"' + @CoreFTPExeDirectory + 'coreftp.exe" -O -d ' + @FTPMethod + @FTPLogin + ':' + @FTPPassword + '@' + @FTPHostIP + ':' + convert(varchar, @FTPPort) + @FTPSourceDirectory + '/*.* -p "' + @FTPDestinationDirectory + '"'
PRINT @FTPCommand
EXEC master..xp_cmdshell @FTPCommand
When I run this it prints:
"C:\Program Files (x86)\CoreFTP\coreftp.exe" -O -d ftp://test:password@jcsql.com:21/outgoing/*.* -p "R:\"
The Error I get is:
"'C:\Program' is not recognized as an internal or external command,"
As you can see it is failing on the first space. I have encoutered this several times and putting it in double quotes fixes it. If I cut and paste the above in the command line directly on the server it runs fine.
Thanks for any help!
April 22, 2011 at 12:15 pm
Here appears to be the problem:
"C:\Program Files (x86)\CoreFTP\coreftp.execoreftp.exe" -O
You set
set @CoreFTPExeDirectory = 'C:\Program Files (x86)\CoreFTP\coreftp.exe'
And then in your string you add coreftp.exe again without a space.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 22, 2011 at 12:19 pm
JCSQL (4/22/2011)
This is VERY frustrating as I actually have this working on several servers but it is not working in my current code. I KNOW I am missing something stupid and need a second pair of eyes.Here is the code:
declare @FTPHostIP varchar(50),
@FTPLogin varchar(50),
@FTPPassword varchar(50),
@FTPSourceDirectory varchar(255),
@FTPDestinationDirectory varchar(255),
@FTPMethod varchar(50),
@FTPPort int,
@FTPFlags varchar(50),
@FTPCommand varchar(8000),
@CoreFTPExeDirectory varchar(255)
set @FTPHostIP = 'jcsql.com'
set @FTPLogin = 'test'
set @FTPPassword = 'password'
set @FTPSourceDirectory = '/outgoing'
set @FTPDestinationDirectory = 'R:\'
set @FTPMethod = 'ftp://'
set @FTPPort = 21
set @FTPFlags = '-O'
set @CoreFTPExeDirectory = 'C:\Program Files (x86)\CoreFTP\coreftp.exe'
SET @FTPCommand = '"' + @CoreFTPExeDirectory + 'coreftp.exe" -O -d ' + @FTPMethod + @FTPLogin + ':' + @FTPPassword + '@' + @FTPHostIP + ':' + convert(varchar, @FTPPort) + @FTPSourceDirectory + '/*.* -p "' + @FTPDestinationDirectory + '"'
PRINT @FTPCommand
EXEC master..xp_cmdshell @FTPCommand
When I run this it prints:
"C:\Program Files (x86)\CoreFTP\coreftp.exe" -O -d ftp://test:password@jcsql.com:21/outgoing/*.* -p "R:\"
The Error I get is:
"'C:\Program' is not recognized as an internal or external command,"
As you can see it is failing on the first space. I have encoutered this several times and putting it in double quotes fixes it. If I cut and paste the above in the command line directly on the server it runs fine.
Thanks for any help!
If I recall correctly, when there's a space in a folder name you either have to use a % instead of a space or trunate to 6 characters and follow it with ~1
So you'd have either
"C:\Program%Files%(x86)\CoreFTP\coreftp.exe"
or
"C:\Progra~1\CoreFTP\coreftp.exe"
The problem with the latter is if you have more than one folder that could be "Progra~1" Then you'd have to make it
"C:\Progra~2\CoreFTP\coreftp.exe"
where the number after Progra~ would be the numbered position where it appears in your tree. If there are 3 before it in the tree, it'd be ~4.
Hope it is one of those!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 22, 2011 at 12:24 pm
Sorry,
I made a last minute change and did not run it before I pasted it in the window.
Here is the code without the double coreftp with the same error. I will try the % in the meantime.
declare @FTPHostIP varchar(50),
@FTPLogin varchar(50),
@FTPPassword varchar(50),
@FTPSourceDirectory varchar(255),
@FTPDestinationDirectory varchar(255),
@FTPMethod varchar(50),
@FTPPort int,
@FTPFlags varchar(50),
@FTPCommand varchar(8000),
@CoreFTPExeDirectory varchar(255)
set @FTPHostIP = 'jcsql.com'
set @FTPLogin = 'test'
set @FTPPassword = 'password'
set @FTPSourceDirectory = '/outgoing'
set @FTPDestinationDirectory = 'R:\'
set @FTPMethod = 'ftp://'
set @FTPPort = 21
set @FTPFlags = '-O'
set @CoreFTPExeDirectory = 'C:\Program Files (x86)\CoreFTP\coreftp.exe'
SET @FTPCommand = '"' + @CoreFTPExeDirectory + '" -O -d ' + @FTPMethod + @FTPLogin + ':' + @FTPPassword + '@' + @FTPHostIP + ':' + convert(varchar, @FTPPort) + @FTPSourceDirectory + '/*.* -p "' + @FTPDestinationDirectory + '"'
PRINT @FTPCommand
EXEC master..xp_cmdshell @FTPCommand
April 22, 2011 at 12:28 pm
I tried the DOS shortname and it did not work as well as the %
April 22, 2011 at 12:39 pm
Try the following idea:
declare @cmdvarchar(256)
,@Drivevarchar(10)
,@Pathvarchar(256)
Set @cmd = 'dir'
Set @drive = 'C:\'
Set @Path = 'Program Files\Common Files'
if exists (select CHARINDEX(@path,' ',1))
Begin
Set @Path = '"' + REPLACE(@path,'\','"\"') + '"'
End
Set @cmd = @cmd + ' ' + @drive + @Path
print @cmd
exec xp_cmdshell @cmd
Putting quotes around each directory seems to work better for me.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 22, 2011 at 6:23 pm
Well I found the problem yet I have no idea why it would be an issue. The last part of the command line has where to put the file from the FTP pull ("R:\"). I removed those double quotes and it worked. I have NO idea what that would be a problem and it was a total shot in the dark to remove them. The frustraing thing was the error was suggesting to that old time directory space problem so I did not think to look beyond that (AKA "error on line 128" but the error is actually 5 lines above it 🙂 ).
I appreciate the quick responses and suggestions!
Thanks guys!
April 22, 2011 at 7:24 pm
Wow, glad to hear you figured it out.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply