SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stop a zip file from having null characters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kyle Doouss
Yak Posting Veteran

United Kingdom
53 Posts

Posted - 11/28/2011 :  07:02:22  Show Profile  Reply with Quote

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

India
52325 Posts

Posted - 11/28/2011 :  07:17:15  Show Profile  Reply with Quote
use ISNULL() or COALESCE to convert NULLs to ''

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

Go to Top of Page

Kyle Doouss
Yak Posting Veteran

United Kingdom
53 Posts

Posted - 11/28/2011 :  07:31:58  Show Profile  Reply with Quote
I have used isnull() before in selects etc.

Where abouts in the stored proc would you put these.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/28/2011 :  07:32:47  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000