Stop a zip file from having null characters

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

Stop a zip file from having null characters

Author

Topic

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-11-28 : 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 07:17:15

use ISNULL() or COALESCE to convert NULLs to ''

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-11-28 : 07:31:58

I have used isnull() before in selects etc.

Where abouts in the stored proc would you put these.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

quote:
Originally posted by Kyle Doouss

I have used isnull() before in selects etc.

Where abouts in the stored proc would you put these.

the place where you get null values use isnull and convert them to ''

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources