LIULIHUA
asked on
Unzip scripts not stable
Hi experts,
the scripts it worked perfect last week in Query Analyzer, however, it hangs again this moring when I back run it manually. It did delect *.txt and created ##Files, after that system just hang there forever. Any helps are appreciate.
/***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\*.zi
INSERT INTO ##Files
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\DATA\FTP_Download\*.tx
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\WinZi
EXEC master..xp_cmdshell @SQL
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
the scripts it worked perfect last week in Query Analyzer, however, it hangs again this moring when I back run it manually. It did delect *.txt and created ##Files, after that system just hang there forever. Any helps are appreciate.
/***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\*.zi
INSERT INTO ##Files
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\DATA\FTP_Download\*.tx
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\WinZi
EXEC master..xp_cmdshell @SQL
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
ptjcb
How much free space do you have on the drive? Do you have enough free space in tempdb?
ASKER
I have 7.6 mb avaliable.
I just did a test in cmd prmprt, c:\c:\progra~utilit\winsip
it said " unvalid file name c:\data\ftpdownload\ca_acc
any idea?
I just did a test in cmd prmprt, c:\c:\progra~utilit\winsip
it said " unvalid file name c:\data\ftpdownload\ca_acc
any idea?
Is this a permissions issue? Since you are running it from QA it would be run under your permissions. Can you see/open this file?
I have 7.6 mb avaliable. Only 7.6 MB?
ASKER
I am obj onwer, this SQL server is indvidul version run at my laptop. there shouldn't be permissions problem.
yes, 7.6 mb. it ran perfect last week.
yes, 7.6 mb. it ran perfect last week.
c:\data\ftpdownload\ca_acc
What has changed since last week? Have you run any OS updates? Anti-Virus updates? etc
What has changed since last week? Have you run any OS updates? Anti-Virus updates? etc
ASKER
hi,
I correct some typo in cmd prmpt, and try it again it worked at prmpt as:
c:\>c:\progra~utilit\winsi
I correct some typo in cmd prmpt, and try it again it worked at prmpt as:
c:\>c:\progra~utilit\winsi
ASKER
NOop, no any updates,
As I mentioned in prior comment, I can open/see this file. and run it with cmd prmpt.
As I mentioned in prior comment, I can open/see this file. and run it with cmd prmpt.
Did you have only 7.6MB free when you ran it last week?
ASKER
yes,
Can you run this in portions to see where the hang up is? For example, you say the directories are created but that is it.
drop table ##Files
go
CREATE TABLE ##Files ( FileName VARCHAR(100))
INSERT INTO ##Files
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\DATA\FTP_Download\*.zi
INSERT INTO ##Files
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\DATA\FTP_Download\*.tx
***
Can you see ##Files?
****
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\WinZi
EXEC master..xp_cmdshell @SQL
****
PRINT @SQL (so you can see the @SQL - or if it is getting this far)
*****
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
drop table ##Files
go
CREATE TABLE ##Files ( FileName VARCHAR(100))
INSERT INTO ##Files
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\DATA\FTP_Download\*.zi
INSERT INTO ##Files
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\DATA\FTP_Download\*.tx
***
Can you see ##Files?
****
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\WinZi
EXEC master..xp_cmdshell @SQL
****
PRINT @SQL (so you can see the @SQL - or if it is getting this far)
*****
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
ASKER
***
Can you see ##Files? Yes, I can.
****
When I run this part, I got "Incorrect Sytex" error
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\WinZi
EXEC master..xp_cmdshell @SQL
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '@SQL'.
>>yes, 7.6 mb. <<
I trust you mean 7.6GB.
I trust you mean 7.6GB.
> When I run this part, I got "Incorrect Sytex" error
Are you still creating and filling the ##Files table, or just trying to run the code shown in that message?
You didn't show the FETCH and END at the end in that message. Were they there when you tested it?
Going back to your original code, you could try changing the command you execute to something like the following, which echo's the command line you've built and appends the output to C:\sqlunzip.txt:
SET @SQL = 'echo C:\Progra~1\utility\WinZip
James
Are you still creating and filling the ##Files table, or just trying to run the code shown in that message?
You didn't show the FETCH and END at the end in that message. Were they there when you tested it?
Going back to your original code, you could try changing the command you execute to something like the following, which echo's the command line you've built and appends the output to C:\sqlunzip.txt:
SET @SQL = 'echo C:\Progra~1\utility\WinZip
James
ASKER
This is what I did, I ran the scripts as following, and it echo to C:\sqlunzip.txt all the files path like below:
C:\Progra~1\utility\WinZip
...........
they all looks good to me.
/*** scripts***/
CREATE TABLE ##Files ( FileName VARCHAR(100))
INSERT INTO ##Files
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\DATA\FTP_Download\*.zi
INSERT INTO ##Files
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\DATA\FTP_Download\*.tx
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 = 'echo C:\Progra~1\utility\WinZip
EXEC master..xp_cmdshell @SQL
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
/***finish****/
C:\Progra~1\utility\WinZip
...........
they all looks good to me.
/*** scripts***/
CREATE TABLE ##Files ( FileName VARCHAR(100))
INSERT INTO ##Files
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\DATA\FTP_Download\*.zi
INSERT INTO ##Files
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\DATA\FTP_Download\*.tx
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 = 'echo C:\Progra~1\utility\WinZip
EXEC master..xp_cmdshell @SQL
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
/***finish****/
ASKER
Hi,
##Files is created perfectly, and the echo file, sqlunzip.txt is look good as well, but unzip still hang, that's mean something wrong when it runs to this part of scripts, am I right?
/****
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
##Files is created perfectly, and the echo file, sqlunzip.txt is look good as well, but unzip still hang, that's mean something wrong when it runs to this part of scripts, am I right?
/****
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
ASKER
Hi,
I test Winzip in my PC using cmd,
c:\>c: \progra~\utitlity\winzip\w
it worked great, so Winzip is not the problem niether.
I test Winzip in my PC using cmd,
c:\>c: \progra~\utitlity\winzip\w
it worked great, so Winzip is not the problem niether.
That is why I added the PRINT command after the exec sql - just to see what it was running...
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\WinZi
EXEC master..xp_cmdshell @SQL
--****
PRINT @SQL -- (so you can see the @SQL - or if it is getting this far)
--*****
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
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\WinZi
EXEC master..xp_cmdshell @SQL
--****
PRINT @SQL -- (so you can see the @SQL - or if it is getting this far)
--*****
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
ASKER
I did it before, it just hang there.
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\*.zi
INSERT INTO ##Files
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\DATA\FTP_Download\*.tx
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\WinZi
EXEC master..xp_cmdshell @SQL
PRINT @SQL
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
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\*.zi
INSERT INTO ##Files
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\DATA\FTP_Download\*.tx
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\WinZi
EXEC master..xp_cmdshell @SQL
PRINT @SQL
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
That is why I suggested that you add PRINT commands to the cursor to see what does work and it might give you a clue to where it is failing.
ASKER
This is the part that I ran, and got hang there. No any thing be printed out.
/***
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\WinZi
EXEC master..xp_cmdshell @SQL
PRINT @SQL
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
/***
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\WinZi
EXEC master..xp_cmdshell @SQL
PRINT @SQL
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
I have added a few more PRINT commands.
DECLARE cFiles CURSOR FOR
SELECT FileName FROM ##Files
DECLARE @FileName VARCHAR(50)
DECLARE @SQL VARCHAR(2000)
OPEN cFiles
FETCH NEXT FROM cFiles INTO @FileName
PRINT @FileName + ' this is the file name'
WHILE @@FETCH_STATUS = 0
PRINT CAST(@@FETCH_STATUS as varchar) + ' this is the fetch status'
BEGIN
SET @SQL = 'C:\Progra~1\utility\WinZi
EXEC master..xp_cmdshell @SQL
PRINT @SQL
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
DECLARE cFiles CURSOR FOR
SELECT FileName FROM ##Files
DECLARE @FileName VARCHAR(50)
DECLARE @SQL VARCHAR(2000)
OPEN cFiles
FETCH NEXT FROM cFiles INTO @FileName
PRINT @FileName + ' this is the file name'
WHILE @@FETCH_STATUS = 0
PRINT CAST(@@FETCH_STATUS as varchar) + ' this is the fetch status'
BEGIN
SET @SQL = 'C:\Progra~1\utility\WinZi
EXEC master..xp_cmdshell @SQL
PRINT @SQL
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
ASKER
This is result, it generateing non-stop " 0 this is the fetch status"...... until I cancelled it
ca_accounts.zip this is the file name
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
.
.
....
ca_accounts.zip this is the file name
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
.
.
....
Good - it is in the WHILE statement
Did it return anything for the @Filename?
*****
DECLARE cFiles CURSOR FOR
SELECT FileName FROM ##Files
DECLARE @FileName VARCHAR(50)
DECLARE @SQL VARCHAR(2000)
OPEN cFiles
FETCH NEXT FROM cFiles INTO @FileName
PRINT @FileName + ' this is the file name'
WHILE @@FETCH_STATUS = 0
--PRINT CAST(@@FETCH_STATUS as varchar) + ' this is the fetch status'
BEGIN
PRINT 'Creating @SQL'
SET @SQL = 'C:\Progra~1\utility\WinZi
EXEC master..xp_cmdshell @SQL
PRINT @SQL
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
Did it return anything for the @Filename?
*****
DECLARE cFiles CURSOR FOR
SELECT FileName FROM ##Files
DECLARE @FileName VARCHAR(50)
DECLARE @SQL VARCHAR(2000)
OPEN cFiles
FETCH NEXT FROM cFiles INTO @FileName
PRINT @FileName + ' this is the file name'
WHILE @@FETCH_STATUS = 0
--PRINT CAST(@@FETCH_STATUS as varchar) + ' this is the fetch status'
BEGIN
PRINT 'Creating @SQL'
SET @SQL = 'C:\Progra~1\utility\WinZi
EXEC master..xp_cmdshell @SQL
PRINT @SQL
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
ASKER
Yes, the first line,
ca_accounts.zip this is the file name
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
0 this is the fetch status
Ok..now run the version I just put there - I commented out the Fetch_status check and added another right after the BEGIN. What I am trying to do is walk through the cursor to find where it is hanging. At this point I am looking at the statements between the BEGIN...END since we have not had a PRINT @SQL yet.
ASKER
You mean like this
/*** version 1
DECLARE cFiles CURSOR FOR
SELECT FileName FROM ##Files
DECLARE @FileName VARCHAR(50)
DECLARE @SQL VARCHAR(2000)
OPEN cFiles
FETCH NEXT FROM cFiles INTO @FileName
PRINT @FileName + ' this is the file name'
WHILE @@FETCH_STATUS = 0
BEGIN
Sorry, you have to have the entire statement - SQL won't let you run up to a breakpoint
I have added a few more PRINT statements just to see what the results will be
****
DECLARE cFiles CURSOR FOR
SELECT FileName FROM ##Files
DECLARE @FileName VARCHAR(50)
DECLARE @SQL VARCHAR(2000)
OPEN cFiles
FETCH NEXT FROM cFiles INTO @FileName
PRINT @FileName + ' this is the file name'
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Creating @SQL'
SET @SQL = 'C:\Progra~1\utility\WinZi
-- EXEC master..xp_cmdshell @SQL
PRINT @SQL
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
I have added a few more PRINT statements just to see what the results will be
****
DECLARE cFiles CURSOR FOR
SELECT FileName FROM ##Files
DECLARE @FileName VARCHAR(50)
DECLARE @SQL VARCHAR(2000)
OPEN cFiles
FETCH NEXT FROM cFiles INTO @FileName
PRINT @FileName + ' this is the file name'
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Creating @SQL'
SET @SQL = 'C:\Progra~1\utility\WinZi
-- EXEC master..xp_cmdshell @SQL
PRINT @SQL
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
ASKER
THis is the result, it seems master..xp_cmdshell not function right.
ca_accounts.zip this is the file name
Creating @SQL
C:\Progra~1\utility\WinZip
Creating @SQL
C:\Progra~1\utility\WinZip
Creating @SQL
C:\Progra~1\utility\WinZip
Creating @SQL
ca_accounts.zip this is the file name
Creating @SQL
C:\Progra~1\utility\WinZip
Creating @SQL
C:\Progra~1\utility\WinZip
Creating @SQL
C:\Progra~1\utility\WinZip
Creating @SQL
If you looked at the script you would see that I have commented it out the last time because I wanted to see what it was doing.
-- EXEC master..xp_cmdshell @SQL
DECLARE cFiles CURSOR FOR
SELECT FileName FROM ##Files
DECLARE @FileName VARCHAR(50)
DECLARE @SQL VARCHAR(2000)
OPEN cFiles
FETCH NEXT FROM cFiles INTO @FileName
PRINT @FileName + ' this is the file name'
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'Creating @SQL'
SET @SQL = 'C:\Progra~1\utility\WinZi
PRINT @SQL
EXEC master..xp_cmdshell @SQL
PRINT 'xp_cmdshell was executed'
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
****
Ok...you can see that it is working through the three files. Try the above script (EXEC xp_cmdshell has been uncommented so it should work). This will test if it is executing the @sql variable.
-- EXEC master..xp_cmdshell @SQL
DECLARE cFiles CURSOR FOR
SELECT FileName FROM ##Files
DECLARE @FileName VARCHAR(50)
DECLARE @SQL VARCHAR(2000)
OPEN cFiles
FETCH NEXT FROM cFiles INTO @FileName
PRINT @FileName + ' this is the file name'
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'Creating @SQL'
SET @SQL = 'C:\Progra~1\utility\WinZi
PRINT @SQL
EXEC master..xp_cmdshell @SQL
PRINT 'xp_cmdshell was executed'
FETCH NEXT FROM cFiles INTO @FileName
END
CLOSE cFiles
DEALLOCATE cFiles
****
Ok...you can see that it is working through the three files. Try the above script (EXEC xp_cmdshell has been uncommented so it should work). This will test if it is executing the @sql variable.
ASKER
I got nothing came out because it hang again.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is result come out:
ca_accounts.zip this is the file name
C:\Progra~1\utility\WinZip
(2 row(s) affected)
xp_cmdshell was executed
C:\Progra~1\utility\WinZip
(2 row(s) affected)
xp_cmdshell was executed
C:\Progra~1\utility\WinZip
(2 row(s) affected)
So WinZip is actually installed on the server and the account that is used by the SQL Server service (NOT your login) has all the required permissions? If you are sure it is consider the following:
1. The zip file is corrupt or
2. The files are already there.
In either case, there is a message box (that you will never see) waiting patiently for you to click, so that it looks like it is hanging when in fact it is not.
As I suggested in your other duplicate and now nearly abandoned question you need to use the Command line interface (http://www.winzip.com/prodpagecl.htm) and not the full blown version. This gives you all the switches you need and will not "hang" waiting for you to click a non-existant MsgBox.
I trust this is clear now.
P.S. Did I mention that allowing CmdShell to execute an app is a really bad idea ...
1. The zip file is corrupt or
2. The files are already there.
In either case, there is a message box (that you will never see) waiting patiently for you to click, so that it looks like it is hanging when in fact it is not.
As I suggested in your other duplicate and now nearly abandoned question you need to use the Command line interface (http://www.winzip.com/prodpagecl.htm) and not the full blown version. This gives you all the switches you need and will not "hang" waiting for you to click a non-existant MsgBox.
I trust this is clear now.
P.S. Did I mention that allowing CmdShell to execute an app is a really bad idea ...
ASKER
Hi,
I have question for comment below,
>>In either case, there is a message box (that you will never see) waiting patiently for you to click, so that it looks like it is hanging when in fact it is not.>> If this is correct, why this script was worked for one week?
I have question for comment below,
>>In either case, there is a message box (that you will never see) waiting patiently for you to click, so that it looks like it is hanging when in fact it is not.>> If this is correct, why this script was worked for one week?
Again, because:
1. The zip file was not corrupt and
2. The files were not already there and
3. No other error occured.
1. The zip file was not corrupt and
2. The files were not already there and
3. No other error occured.
ASKER
Hi,
1. Currently, I am in development phrase, SQL server and Winzip are both installed in my pc, and I tested Winzip in my PC using cmd, it worked good, will that proved that Winzip is not corrupt?
c:\>c: \progra~\utitlity\winzip\w
2. if " allowing CmdShell to execute an app is a really bad idea ...", what is the best way? please advice.
1. Currently, I am in development phrase, SQL server and Winzip are both installed in my pc, and I tested Winzip in my PC using cmd, it worked good, will that proved that Winzip is not corrupt?
c:\>c: \progra~\utitlity\winzip\w
2. if " allowing CmdShell to execute an app is a really bad idea ...", what is the best way? please advice.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
One more question, how do I know the zip file is corrupt?
One more question, how do I know the zip file is corrupt?
You will know when you try and unzip it. In other words you will get an error message and no files wil be unzipped.
ASKER
if I can manually unzip that file that I couldn't unzip from Query Analyzor, will that means that zip file is still normal? Because the my setuation just like this.
That would main requirement. Another one would be to ensure that the files do not already exist or you would get an error message.
Actually more than an error message, it is a message box asking if you want to overwrite. That is assuming that you still are using the application with a graphical interface and not the Command line interface which has no graphical interface.
ASKER
Indeed, I tried to use command line interface by modifying the script as below, it still hang.
"BEGIN
SET @SQL = 'C:\Progra~1\utility\WinZi
EXEC master..xp_cmdshell @SQL"
"BEGIN
SET @SQL = 'C:\Progra~1\utility\WinZi
EXEC master..xp_cmdshell @SQL"
It is not hanging! Let me try this once again:
It is waiting for you to respond to a message similar to the next:
C:\DATA\FTP_Download\XYZ.A
Either make sure the files do not exist or make the following change to overwrite them:
SET @SQL = 'C:\Progra~1\utility\WinZi
Or not:
SET @SQL = 'C:\Progra~1\utility\WinZi
It is waiting for you to respond to a message similar to the next:
C:\DATA\FTP_Download\XYZ.A
Either make sure the files do not exist or make the following change to overwrite them:
SET @SQL = 'C:\Progra~1\utility\WinZi
Or not:
SET @SQL = 'C:\Progra~1\utility\WinZi
ASKER
Hi,
Since the .txt files alway be deleted first with the first line run, so I pick the second one:
>>SET @SQL = 'C:\Progra~1\utility\WinZi
unforturnatly, it was hanging, it is waiting for me to response something as your words. so I did a testing, I put "-d-o" to gether to get the output that will help me know where the process stoping, and this is the results. Is that mean somewhere need to modify with Winzip Command Line Support configuration? Just like it happend last Monday, Winzip32 stoped there because the weekly update request waiting for clicking "Yes" or "NO". I unchecked the option of weekly update redio in confeguration of Winzip32 at the end, even it still waiting me response for something.
/*** output***/
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 - in -d-o
Program is terminating!
NULL
Since the .txt files alway be deleted first with the first line run, so I pick the second one:
>>SET @SQL = 'C:\Progra~1\utility\WinZi
unforturnatly, it was hanging, it is waiting for me to response something as your words. so I did a testing, I put "-d-o" to gether to get the output that will help me know where the process stoping, and this is the results. Is that mean somewhere need to modify with Winzip Command Line Support configuration? Just like it happend last Monday, Winzip32 stoped there because the weekly update request waiting for clicking "Yes" or "NO". I unchecked the option of weekly update redio in confeguration of Winzip32 at the end, even it still waiting me response for something.
/*** output***/
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 - in -d-o
Program is terminating!
NULL
I give up. Good luck.