Change file name on FTP site once up loaded

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Change file name on FTP site once up loaded

Author

Topic

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-11-27 : 16:29:23

I am using the following to create a file from a view, then zip it up and FTP the file. They have requested that they would like me to send the file up with a T at the front and once it is completed up loading change it to a R at the front.

Any ideas???

exec master..xp_cmdshell 'bcp "SELECT[RecordType],[CustomerGroup],[User],[CustomerNumber],[AlfaDebtors],[CodeAssortment],[Authorisation],[ManualMaintenance],[AdressGroup],[Supplier],[CustomerCodeAlfa],[DeleteFlag],[AdressGroup_Long],[CodeAssortment_Long],[ERPCustomerGroup],[FreeForUse]FROM [HBSLIVE].[dbo].[UDEF_WEB_UPDATE_RASS_ASR]" queryout "D:\WebFiles\Web Update File Out\ASS Update Files\RASS_ASR_ALL.txt" -c -Uusername -Ppassword -SAppserver'
EXEC UDEF_KD_CompressFile 'D:\WebFiles\Web Update File Out\ASS Update Files\RASS_ASR_ALL.zip', 'D:\WebFiles\Web Update File Out\ASS Update Files\RASS_ASR_ALL.txt'

EXEC UDEF_KD_CompressFile proc as follows

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[UDEF_KD_CompressFile]
@ZipFile VARCHAR(255),
@FileToZip VARCHAR(255)
AS
--author: dee-u of CodeGuru, vbforums
DECLARE @hr INT,
@folderObject INT,
@shellObject INT,
@src VARCHAR(255),
@desc VARCHAR(255),
@command VARCHAR(255),
@password VARCHAR(255),
@username VARCHAR(255)

SET @username = '*****'
SET @password = '*****'

--Create table to save dummy text to create zip file
CREATE TABLE ##DummyTable ( [DummyColumn] [VARCHAR](255))

--header of a zip file
DECLARE @zipHeader VARCHAR(22)
SET @zipHeader = CHAR(80) + CHAR(75) + CHAR(5) + CHAR(6) + REPLICATE(CHAR(0),18)

--insert zip header
INSERT INTO ##DummyTable (DummyColumn) VALUES (@zipHeader)

--save/create target zip
SET @command = 'bcp "..##DummyTable" out "' + @ZipFile + '" -c -U "' + @username + '" -P "' + @password + '"'
EXEC MASTER..xp_cmdshell @command

--Drop used temporary table
DROP TABLE ##DummyTable

--get shell object
EXEC @hr = sp_OACreate
'Shell.Application' ,
@shellObject OUT

IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @shellObject , @src OUT , @desc OUT

SELECT hr = convert(VARBINARY(4),@hr),
Source = @src,
DESCRIPTION = @desc

RETURN
END

--get folder
SET @command = 'NameSpace("' + @ZipFile + '")'
EXEC @hr = sp_OAMethod @shellObject , @command , @folderObject OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @shellObject , @src OUT , @desc OUT

SELECT hr = convert(VARBINARY(4),@hr),
Source = @src,
DESCRIPTION = @desc

RETURN
END

--copy file to zip file
SET @command = 'CopyHere("' + @FileToZip + '")'
EXEC @hr = sp_OAMethod @folderObject , @command
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @folderObject , @src OUT , @desc OUT

SELECT hr = convert(VARBINARY(4),@hr),
Source = @src,
DESCRIPTION = @desc

RETURN
END

--Destroy the objects used.
EXEC sp_OADestroy @shellObject
EXEC sp_OADestroy @folderObject

GO

SET NOCOUNT OFF
DECLARE @MyFileName varchar(50)

set @MyFileName = 'RASS_ASR_ALL_' + CAST(DATEPART(YYYY, GETDATE()) AS varCHAR(4)) +'_'+ CAST(DATEPART(MM, GETDATE()) AS varCHAR(2)) +'_'+ CAST(DATEPART(DD, GETDATE()) AS varCHAR(2))+'_'+CAST(DATEPART(HOUR, GETDATE()) AS varCHAR(2)) +'_'+ CAST(DATEPART(MINUTE, GETDATE()) AS varCHAR(2))
+'_'+ CAST(DATEPART(SECOND, GETDATE()) AS varCHAR(2))+'.zip'
exec s_ftp_PutFile
@FTPServer = 'customerftp.' ,
@FTPUser = 'user' ,
@FTPPWD = 'password' ,
@FTPPath = '/' ,
@FTPFileName = @MyFileName,
@SourcePath = '"D:\WebFiles\Web Update File Out\ASS Update Files\"' ,
@SourceFile = 'RASS_ASR_ALL.zip' ,

s_ftp_PutFile proc below

ALTER procedure [dbo].[s_ftp_PutFile]
@FTPServer varchar(128) ,
@FTPUser varchar(128) ,
@FTPPWD varchar(128) ,
@FTPPath varchar(128) ,
@FTPFileName varchar(128) ,

@SourcePath varchar(128) ,
@SourceFile varchar(128) ,

@workdir varchar(128)
as
/*
exec s_ftp_PutFile
@FTPServer = 'myftpsite' ,
@FTPUser = 'username' ,
@FTPPWD = 'password' ,
@FTPPath = '/dir1/' ,
@FTPFileName = 'test2.txt' ,
@SourcePath = 'c:\vss\mywebsite\' ,
@SourceFile = 'MyFileName.html' ,

@workdir = 'c:\temp\'
*/

declare @cmd varchar(1000)
declare @workfilename varchar(128)

select @workfilename = 'ftpcmd.txt'

-- deal with special characters for echo commands
select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
select @FTPPWD = replace(replace(replace(@FTPPWD, '|', '^|'),'<','^<'),'>','^>')
select @FTPPath = replace(replace(replace(@FTPPath, '|', '^|'),'<','^<'),'>','^>')

select @cmd = 'echo ' + 'open ' + @FTPServer
+ ' > ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPUser
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPPWD
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit'
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd

select @cmd = 'ftp -s:' + @workdir + @workfilename

create table #a (id int identity(1,1), s varchar(1000))
insert #a
exec master..xp_cmdshell @cmd

select id, ouputtmp = s from #a

@workdir = 'c:\temp\'

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-27 : 18:46:21

The put file looks like it came from my site. Have you tried just adding a rename at the end?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-11-28 : 02:30:36

Yes it did. Thanks for sharing it. Execellent bit of sql!

In which bit would you put the rename?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-28 : 05:31:54

Just add it to the created bat file - or you can crate a new one with another generic SP to do a rename.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-11-28 : 07:34:47

Thanks Nigelrivett.

I have not use a rename before. Any chance you could show me where to put it? Sorry to be so thick!

Cheers

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-29 : 06:10:22

Directly after the put.
A rename is just
rename filenameold, filenamenew

Test to see whether filenameold takes a path

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-11-29 : 06:44:26

Nigel, Note where I have put it. Is this along the right lines.

exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName
+ ' >> ' + @workdir + @workfilename

exec master..sp_rename @FTPFileName,'HELLO'

exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit'
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-29 : 07:31:48

No - the rename command needs to be sent to the ftp site - sp_rename will try to rename an object in the database.

exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName
+ ' >> ' + @workdir + @workfilename
select @cmd = 'echo ' + 'rename ' + @FTPPath + @FTPFileName + ' HELLO'
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd

might work - but as I said, check the rename manually to see if it accepts a path/filename.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-11-29 : 09:47:48

Niglel thanks very much for this much appreciated!!!!

It works, just had to put the +' '+ in between @ftpfilename and @RenameFile.

Thanks again.

my final set up it below

SET NOCOUNT OFF
DECLARE @MyFileName varchar(50)

set @MyFileName = 'PASS_ASR_ALL_' + CAST(DATEPART(YYYY, GETDATE()) AS varCHAR(4)) +'_'+ CAST(DATEPART(MM, GETDATE()) AS varCHAR(2)) +'_'+ CAST(DATEPART(DD, GETDATE()) AS varCHAR(2))+'_'+CAST(DATEPART(HOUR, GETDATE()) AS varCHAR(2)) +'_'+ CAST(DATEPART(MINUTE, GETDATE()) AS varCHAR(2))
+'_'+ CAST(DATEPART(SECOND, GETDATE()) AS varCHAR(2))+'.zip'
DECLARE @MyFileRename varchar(50)
set @MyFileRename = 'RASS_ASR_ALL_' + CAST(DATEPART(YYYY, GETDATE()) AS varCHAR(4)) +'_'+ CAST(DATEPART(MM, GETDATE()) AS varCHAR(2)) +'_'+ CAST(DATEPART(DD, GETDATE()) AS varCHAR(2))+'_'+CAST(DATEPART(HOUR, GETDATE()) AS varCHAR(2)) +'_'+ CAST(DATEPART(MINUTE, GETDATE()) AS varCHAR(2))
+'_'+ CAST(DATEPART(SECOND, GETDATE()) AS varCHAR(2))+'.zip'

exec s_ftp_PutFile
@FTPServer = 'customerftp.accessnet.co.uk' ,
@FTPUser = 'ftphbswarren' ,
@FTPPWD = 'yO82U9F3' ,
@FTPPath = '/' ,
@FTPFileName = @MyFileName,
@SourcePath = '"D:\WebFiles\Web Update File Out\ASS Update Files\"' ,
@SourceFile = 'RASS_ASR_ALL.txt' ,
@RenameFile = @MyFileRename,
@workdir = 'c:\temp\'

s_ftp_PutFile proc below

ALTER procedure [dbo].[s_ftp_PutFile]
@FTPServer varchar(128) ,
@FTPUser varchar(128) ,
@FTPPWD varchar(128) ,
@FTPPath varchar(128) ,
@FTPFileName varchar(128) ,
@SourcePath varchar(128) ,
@SourceFile varchar(128) ,
@RenameFile varchar(128) ,
@workdir varchar(128)
as
/*
exec s_ftp_PutFile
@FTPServer = 'myftpsite' ,
@FTPUser = 'username' ,
@FTPPWD = 'password' ,
@FTPPath = '/dir1/' ,
@FTPFileName = 'test2.txt' ,
@SourcePath = 'c:\vss\mywebsite\' ,
@SourceFile = 'MyFileName.html' ,
@RenameFile = 'rename',
@workdir = 'c:\temp\'
*/

declare @cmd varchar(1000)
declare @workfilename varchar(128)

select @workfilename = 'ftpcmd.txt'

-- deal with special characters for echo commands
select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
select @FTPPWD = replace(replace(replace(@FTPPWD, '|', '^|'),'<','^<'),'>','^>')
select @FTPPath = replace(replace(replace(@FTPPath, '|', '^|'),'<','^<'),'>','^>')

select @cmd = 'echo ' + 'open ' + @FTPServer
+ ' > ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPUser
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPPWD
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName
+ ' >> ' + @workdir + @workfilename

exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'rename ' + @FTPPath + @FTPFileName + ' '+ @RenameFile
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd

select @cmd = 'echo ' + 'quit'
+ ' >> ' + @workdir + @workfilename

exec master..xp_cmdshell @cmd
select @cmd = 'ftp -s:' + @workdir + @workfilename

create table #a (id int identity(1,1), s varchar(1000))
insert #a
exec master..xp_cmdshell @cmd

select id, ouputtmp = s from #a
GO

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-11-29 : 16:41:37

I though we had it sort but it appears as soon as I put the rename bit in it move the file on the ftp server back one file but does rename it. eg the path is /in/ and it renames and moves back to /

Any ideas why this might be?

select @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName
+ ' >> ' + @workdir + @workfilename

exec master..xp_cmdshell @cmd
--select @cmd = 'echo ' + 'rename ' + @FTPPath + @FTPFileName + ' '+ @RenameFile
--+ ' >> ' + @workdir + @workfilename
--exec master..xp_cmdshell @cmd

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-01 : 05:57:12

What happened when you manually renamed the file - you just have to replicate that.
Maybe

select @cmd = 'echo ' + 'rename ' + @FTPPath + @FTPFileName + ' ' + @FTPPath + @RenameFile

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-12-02 : 02:55:37

quote:
Originally posted by nigelrivett

What happened when you manually renamed the file - you just have to replicate that.
Maybe

select @cmd = 'echo ' + 'rename ' + @FTPPath + @FTPFileName + ' ' + @FTPPath + @RenameFile

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

When you say manually rename it do you mean fo on to the ftp site and rename it or hard code the name in the script.

Sorry for the missunderstanding

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-02 : 04:41:33

I mean test what you are going to do by connecting to the ftp site and trying the commands manually. Then you just have to replicate that in the .bat file.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-12-03 : 06:09:52

Thanks for your help. Yes sorted it. I needed a ftp path before the renamefile

select @cmd = 'echo ' + 'rename ' + @FTPPath + @FTPFileName + ' '+ @FTPPath + @RenameFile
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources