December 15, 2009 at 4:25 pm
I am trying to copy files from a sftp location to my local drive. i am not able to do it.
i have created twp SP.
here is the first SP
@FTPSite varchar(1000),
@UserName varchar(1000),
@Password varchar(1000),
@Filetoget varchar(2000),
@FileToharddrive varchar(2000),
@ScriptLocation varchar(2000),
SELECT @Exec='echo lcd e:\Data\test\download_Reports\ >> "' + @ScriptLocation + '"' --get
EXEC master..xp_cmdshell @Exec, NO_OUTPUT
SELECT @Exec='echo get ' + @FileToharddrive + @Filetoget + '>> "' + @ScriptLocation + '"' --get
EXEC master..xp_cmdshell @Exec, NO_OUTPUT
SELECT @Exec='echo quit >> "' + @ScriptLocation + '"' --QUIT
EXEC master..xp_cmdshell @Exec, NO_OUTPUT
SELECT @Exec='c:\ppp\putty\psftp ' + @username + '@' + @FTPSite + ' -b "' + @ScriptLocation + '" -pw ' + @password--open command
print @exec
EXEC master..xp_cmdshell @Exec --Execute the FTP command
Print 'psFTP completed.'
Then i call the above SP in my new SP
--@filename is the file that is on the clinetftp website.
--@harddrive is where i want the @filename to be downloaded.
EXEC sptestFTP
DECLARE @FileName VARCHAR(255)
DECLARE @harddrive VARCHAR(255)
DECLARE @DateName VARCHAR(255)
SELECT @DateName=Cast(DatePart(yyyy, @Date) AS VARCHAR(10)) +
RIGHT('00000' + Cast(DatePart(mm, @Date) AS VARCHAR(10)), 2) +
RIGHT('00000' + Cast(DatePart(dd, @Date) AS VARCHAR(10)), 2)
--CS-csinterval11242009_0502.csv
SELECT @FileName='CS-csinterval11242009_0502.csv'
SELECT @harddrive='e:\test\data\download_Reports\'
'Sftp.test.com',
'jtest',
'hello',
@FileName,
@harddrive,
'\\test\data\download_Reports\download.txt',
'Reports'
December 15, 2009 at 8:01 pm
Assuming those commands are proper for PuTTY, I think the problem is that PuTTY doesn't take separate commands from the command line. Once you invoke putty, you're in it's shell, not the command shell. each time to run xp_cmdshell here, it's a new command prompt, not the same one.
What I believe you need to do is put those commands on separate lines in a file and use that file as input to PuTTY. How you build that file might be a challenge from T-SQL. Likely I'd look to do this with VBScript instead.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply