December 22, 2011 at 2:58 am
I am using the following stored procedure to zip txt files.
It is working fine apart from when the file is very large.
Do I need to put some wait for delays in somewhere? Glad of any help
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 = 'KYLE'
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
December 22, 2011 at 3:44 am
I have sorted this.
I had to put a waitfor delay before I ran the next stored proc which sends the file to the FTP site
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply