December 2, 2011 at 8:44 am
I am using the following script along with stored procedures to rename a file, zip the file and sent it to an ftp site.
It appears that it works if the file is small but as soon as the file is bigger it is sending an empty zip file to the ftp site.
Is there anyway to ensure the file is fully zipped before sending to the ftp site other an running a separate stored proc in the shedule. The complication with this would be that I am saving the file down witht the date and time and then picking it up.
I would be very glad of help.
USE [HBSLIVE]
GO
/****** Object: StoredProcedure [dbo].[UDEF_WEB_ZIP_FTP_FILE_RPRD_PRD_ALL] Script Date: 12/02/2011 15:26:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[UDEF_WEB_ZIP_FTP_FILE_RPRD_PRD_ALL]
as
SET NOCOUNT OFF
DECLARE @MyFileName varchar(50)
set @MyFileName = 'PPRD_PRD_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 @MyFileNametxt varchar(100)
DECLARE @cmd as varchar(1000)
set @MyFileNametxt = 'RPRD_PRD_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))+'.txt'
SET @cmd = 'RENAME "D:\WebFiles\Web Update File Out\PRD Update Files\RPRD_PRD_ALL.txt" ' + @MyFileNametxt
EXEC master..xp_cmdshell @cmd
DECLARE @cmd1 varchar(1000)
SET @cmd1 = 'D:\WebFiles\Web Update File Out\PRD Update Files\'+ @MyFileName
DECLARE @cmd2 varchar(1000)
SET @cmd2 = 'D:\WebFiles\Web Update File Out\PRD Update Files\'+ @MyFileNametxt
EXEC UDEF_KD_CompressFile @cmd1, @cmd2
exec UDEF_KD_s_ftp_PutFile
@FTPServer = 'ftp.easyorder.eu' ,
@FTPUser = 'hbsgroup' ,
@FTPPWD = 'H85gtop' ,
@FTPPath = '/in/' ,
@FTPFileName = @MyFileName,
@SourcePath = '"D:\WebFiles\Web Update File Out\PRD Update Files\"' ,
@SourceFile = @MyFileName,
@workdir = 'c:\temp\'
GO
December 2, 2011 at 9:02 am
Add a waitfor delay? I know sometimes the zip procs seem to complete, but there's still some work in Explorer to rewrite the file and update the NTFS stuff.
December 2, 2011 at 9:11 am
Thanks for the pointer. I guess you put in like below.
EXEC UDEF_KD_CompressFile @cmd1, @cmd2
waitfor delay '00:01:00'
December 2, 2011 at 9:23 am
I have tested this and it works. Thanks very much. I guess that it thinks the exec has finished but like you say it still needs to do a bit in explorer.
I have put in '00:00:20' this works fine.
December 2, 2011 at 9:49 am
Make sure you test a file that's 10x or 50X what you expect. Data grows, and while :20 seems reasonable, not sure it hurts to test and be sure you don't need :40 with a large file.
Wouldn't think so, but try it.
December 2, 2011 at 9:53 am
Yes I had a play. Quite a large file and it was fine in 20 seconds. Good point though
Thanks for your help!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply