• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2113
  • Last Modified:

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



0
LIULIHUA
Asked:
LIULIHUA
1 Solution
 
bruintjeCommented:
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
0
 
bwdowhanCommented:
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
0
 
LIULIHUAAuthor Commented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LIULIHUAAuthor Commented:
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
>>
0
 
nmcdermaidCommented:
If its hanging then most likely Winzip is prompting for something.

Maybe a path is incorrect or it requires a password.
0
 
LIULIHUAAuthor Commented:
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?
0
 
LIULIHUAAuthor Commented:
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?
0
 
LIULIHUAAuthor Commented:
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
0
 
LIULIHUAAuthor Commented:
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.
0
 
nmcdermaidCommented:
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.
0
 
nmcdermaidCommented:
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.
0
 
LIULIHUAAuthor Commented:
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.
0
 
nmcdermaidCommented:
That behaviour with CMD.EXE not clearing out is pretty normal actually.When you close Query Analyzer, it won't necessarily close the CMD process as well. Thats just the way proceses work.

If you want to solve this you will need to simplify it and find out which part is the issue. In this case the cursor could also be the issue.



Firstly, please try this script, it has no cursor, and doesn't actually unzip anything, it just spits out the command line:



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"'

SELECT @SQL = TOP 1 'C:\Progra~1\utility\WinZip\wzunzip -e C:\DATA\FTP_Download\' + FileName + ' C:\DATA\FTP_Download\' FROM ##Files

SELECT @SQL

-- EXEC master..xp_cmdshell @SQL


Run this script, copy the Query Analyzer output and paste it into a command shell and see what happens.



If everything works then the next step is to uncomment the final line of the script, so it actually tries to unzip it, and run the script again.

So you need to change this:

-- EXEC master..xp_cmdshell @SQL


to this:

EXEC master..xp_cmdshell @SQL



and run it again.


Let me know what happens.
0
 
LIULIHUAAuthor Commented:
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.
0
 
nmcdermaidCommented:
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.








0
 
LIULIHUAAuthor Commented:
Sorry, i forgot to tell you in privious comment that no any files is unzipped even SQL said "one row affected".
0
 
LIULIHUAAuthor Commented:
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
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now