asked on

Unzip hang

Hi,

There is the case, I run the query as following in Query Analyzer in Sql server, it hanging there. I using the regersted Winzip pro, all the line works fine excepte EXEC master..xp_cmdshell @SQL. So I try to unzip one files in cmd prompt, it went throgh fine, the only thing is the two lines show before the result line like
>>WinZip(R) Command Line Support Add-On Version 2.0 (Build 7041)
Copyright (c) WinZip International LLC 1991-2005 - All Rights Reserved>>

Will this a matter that hang EXEC master.xxp_cmdshell @SQL? Is any sepecial configuration set up coused this problem? I have scrupt here for a week have no way to figue out. Please help!

/***Unzip files***/

EXEC master..xp_cmdshell 'del C:\DATA\FTP_Download\*.txt'
GO

drop table ##Files
go

CREATE TABLE ##Files ( FileName VARCHAR(100))

INSERT INTO ##Files
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\DATA\FTP_Download\*.zip"'

INSERT INTO ##Files
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\DATA\FTP_Download\*.txt.z"'

DECLARE cFiles CURSOR FOR
SELECT FileName FROM ##Files

DECLARE @FileName VARCHAR(50)
DECLARE @SQL VARCHAR(2000)

OPEN cFiles
FETCH NEXT FROM cFiles INTO @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'C:\Progra~1\utility\WinZip\wzunzip -e C:\DATA\FTP_Download\' + @FileName + ' C:\DATA\FTP_Download\'
EXEC master..xp_cmdshell @SQL

FETCH NEXT FROM cFiles INTO @FileName
END

CLOSE cFiles
DEALLOCATE cFiles

Brian Mulder

Hello LIULIHUA,

no idea if the 2 lines from winzip cause the command to hang, i use arj myself and have put it in a batch file that is called by SQL Server

if your cmd promt goes ok why not place the lines in bat file and execute that from the exec in sql server

hope this helps a bit
bruintje

bwdowhan

I do the same thing as bruintje... I setup a batch file that accepts parameters for zip...

The one thing I would try is to add the (-min) option to run winzip in a minimized mode. You might also check to see if you are trying to unzip files with a filename that already exists. If so, and you want to force override, you can use (-o)...

The systex would be:

SET @SQL = 'C:\Progra~1\utility\WinZip\wzunzip -min -e -o C:\DATA\FTP_Download\' + @FileName + ' C:\DATA\FTP_Download\'

The -min parameter *MUST* be listed first!

Brian

LIULIHUA

ASKER

cmd prompt is only oparating one file at a time, I have 72 files in total with two type of files, .zip and .txt.z. It need run @SQL to populate the files in ##Files.

LIULIHUA

ASKER

The output as below came up 72 times, because I have 72 files. paste one below.

>>WinZip(R) Command Line Support Add-On Version 2.0 (Build 7041)
Copyright (c) WinZip International LLC 1991-2005 - All Rights Reserved
NULL
ERROR: unknown option m in -min
Program is terminating!
NULL
>>

nmcdermaid

If its hanging then most likely Winzip is prompting for something.

Maybe a path is incorrect or it requires a password.

LIULIHUA

ASKER

Hi bwdowhan,

The output as below came up 72 times, because I have 72 files. I paste one as example here.

>>WinZip(R) Command Line Support Add-On Version 2.0 (Build 7041)
Copyright (c) WinZip International LLC 1991-2005 - All Rights Reserved
NULL
ERROR: unknown option m in -min
Program is terminating!
NULL
>>
Any comment?

LIULIHUA

ASKER

Hi nmcdermaid,

the path is correct and no password be set up. It worked the first week I register, then the second week Winzip prompting for weekly update ( It hang first, then I cmd it in prompt, it show weekly update check "Y" or "No" something), I changed the configuration with "no check for update" then. Since then Winzip stay in prompting for something. Do you have any idea to check what Winzip prompting for from back end?

LIULIHUA

ASKER

Hi,

Of concren the password issue, I did uninstallation and reinstall WinZip again. after run the scripts, I got the error message. Mine is Winzip Pro, it should be able to run WZUNZIP command line. isn't it? any comment?

'C:\Progra~1\utility\WinZip\WZUNZIP' is not recognized as an internal or external command,
operable program or batch file.
NULL

LIULIHUA

ASKER

The path was wrong because I reinstalled Winzip this morning. I changed it for now and rerun the scripts as show on the first comment, it hang again instead of given out error message. PLEASE HELP! THANKS.

nmcdermaid

What you need to do is build the command line that you are running in SQL , then copy and paste that on to the actual command line (ie Start/Run then type CMD and press Enter) and run it. See if there are any prompts coming up.

nmcdermaid

i.e. alter your stored proc so that it has

SELECT @SQL

right before

EXEC master..xp_cmdshell @SQL

This will show the command line before it runs it. You can copy that and run it directly on the command line.

LIULIHUA

ASKER

Hi nmcdermaid,

Thank you for the response.

I did "SELECT @SQL

right before

EXEC master..xp_cmdshell @SQL", it hang still, nothing come out.

Acturally, I did command line with "C:\>C:\progra~1\winzip\wzunzip -yb -o c:\data\ftp_download\*.zip c:\data\ftp_download" yesterday, it worked good. And one thing I found very weird is when SQL is hang, I "end Process" manually and relogin Query Analyzer, however cmd.exe still runing there. That's mean if I end process and login five times, there may have five cmd.ext runging in my system even I have ended the application from Window Process Manager. I seen a guy got the same problem from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=24736, too bad there is no final solution at end.

ASKER CERTIFIED SOLUTION

nmcdermaid

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LIULIHUA

ASKER

Hi nmcdermaid,

1.This is the output I ran with the exact copy ( I just added DECLARE @SQL VARCHAR(2000)) from you.

/**** First run*****/
(2 row(s) affected)

Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'TOP'.

/******/

2.I took out "TOP 1" run it then, this is the output
/**** Second run****/
(2 row(s) affected)

Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the table '##Files', because it does not exist in the system catalog.

(27 row(s) affected)

(49 row(s) affected)

(1 row(s) affected)

3. I uncomment the first line of the script:
-- EXEC master..xp_cmdshell @SQL
to this:
EXEC master..xp_cmdshell @SQL
and run it, this is the output,
/****Third run****/
(1 row(s) affected)

(27 row(s) affected)

(49 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

/****/
By the way, I have 27 .zip files and 49 .txt.z files.

nmcdermaid

OK so what that test verified is that if you can unzip ONE file OK.... I assume it unzipped the file and you can see the file in the unzip folder?

This implies that:

1. Unzip works OK
2. The issue is just in a particular file (i.e. not the first file) or it is with the cursor.

I suggest that you now try your original script, but only put ONE file in the source folder. Make sure that works - i,e, the SP returns and the unzipped file is created.

Then just add files to the unzip folder one by one and rerun your SP until you find the file that is the issue.

Try that and let me know how you go.

LIULIHUA

ASKER

Sorry, i forgot to tell you in privious comment that no any files is unzipped even SQL said "one row affected".

LIULIHUA

ASKER

Hi nmcdermaid,

I just did a sample test like this and it worked perfect. Thank you for the idea -- test in sample way. Now the only thing I need to deal with is the ".txt.z " files. As Winzip declared they support .zip files ONLY. If you ever experienced similar issue please let me know. Many thanks.

DECLARE @FileName VARCHAR(50)
DECLARE @SQL VARCHAR(2000)
set @FileName = '*.zip'
SET @SQL ='C:\Progra~1\WinZip\wzunzip -yb -o C:\DATA\FTP_Download\' + @FileName + ' C:\DATA\FTP_Download\'
EXEC master..xp_cmdshell @SQL
go