|
Kyle Doouss
Starting Member
United Kingdom
37 Posts |
 Posted - 11/28/2011 : 07:02:22
|
I am using the following stored proc to zip a file.
It is puting a little square box in for a null. Is there a way to stop it from doing this.
I would take my hat off for any one who can sort this one!
/****** Object: StoredProcedure [dbo].[UDEF_KD_CompressFile] Script Date: 11/28/2011 11:12:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 = '8609'
--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 |
|
Stop a zip file from having null characters
