Hi,

I am having a problem scheduling a DTS package via SQL Server Agent.

I have created a DTS package that has 1 task in it which runs a VB6 program
that copies a file to a specified folder and then uses the Shell command to
run wzunzip.exe to unzip the file.

My problem is that the package runs fine when I run it manually from the DTS
"Local Packages" folder but when I schedule it to run from the SQL Server
Agent, it fails to unzip the files. The copy process works but it doesn't
execute the wzunzip.exe program.

Here is the section of VB code that runs winzip:
RetVal = Shell("\\srvr-sql2\winzip\wzunzip.exe \\srvr-sql2\todo\pczip001.zip
\\srvr-sql2\todo\", vbHide)
MsgBox "Shell ended with error code: " & RetVal

The return code varies but is in the 2000-3000 range (2380, etc).

I don't understand why the VB exe works fine if executed from windows and it
works when executed from DTS local packages or from the edit window but not
when scheduled.

Any help would be greatly appreciated!

Thanks, Neal...

Hi Neal,

Running DTS packaged as scheduled job has certain limitation and careful
considerations. I suggest that you read the following article first:
269074 INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074

It is better to add some error handling code to the VB program and write
the error to some file, so we can check the file for error details.

In addition, we need to check which security context the job is running
under (269074 tells us how to check) and make this account has Full Control
access to the UNC directory. Does changing UNC path to a local work, e.g.
RetVal = Shell("c:\winzip\wzunzip.exe c:\todo\pczip001.zip c:\todo\",
vbHide)?


Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------
| Reply-To: "Neal Boxrud" <nboxrud@hastingspark.com>
| From: "Neal Boxrud" <nboxrud@hastingspark.com>
| Newsgroups: microsoft.public.sqlserver.dts
| Subject: Scheduled DTS Package fails to execute WinZip.
| Lines: 29
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <HIieb.85$ww6.1102@jekyl.ab.tac.net>
| Date: Tue, 30 Sep 2003 10:04:38 -0700
| NNTP-Posting-Host: 209.52.84.69
| X-Trace: jekyl.ab.tac.net 1064941479 209.52.84.69 (Tue, 30 Sep 2003
11:04:39 MDT)
| NNTP-Posting-Date: Tue, 30 Sep 2003 11:04:39 MDT
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!newsfee
d01.sul.t-online.de!t-online.de!newspeer1-gui.server.ntli.net!ntli.net!peer0
1.cox.net!peer02.cox.net!cox.net!news3.optonline.net!newsfeed.telusplanet.ne
t!newsfeed.telus.net!hekyl.ab.tac.net!jekyl.ab.tac.net!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.dts:39259
| X-Tomcat-NG: microsoft.public.sqlserver.dts
|
| Hi,
|
| I am having a problem scheduling a DTS package via SQL Server Agent.
|
| I have created a DTS package that has 1 task in it which runs a VB6
program
| that copies a file to a specified folder and then uses the Shell command
to
| run wzunzip.exe to unzip the file.
|
| My problem is that the package runs fine when I run it manually from the
DTS
| "Local Packages" folder but when I schedule it to run from the SQL Server
| Agent, it fails to unzip the files. The copy process works but it doesn't
| execute the wzunzip.exe program.
|
| Here is the section of VB code that runs winzip:
| RetVal = Shell("\\srvr-sql2\winzip\wzunzip.exe
\\srvr-sql2\todo\pczip001.zip
| \\srvr-sql2\todo\", vbHide)
| MsgBox "Shell ended with error code: " & RetVal
|
| The return code varies but is in the 2000-3000 range (2380, etc).
|
| I don't understand why the VB exe works fine if executed from windows and
it
| works when executed from DTS local packages or from the edit window but
not
| when scheduled.
|
| Any help would be greatly appreciated!
|
| Thanks, Neal...
|
|
|

Make sure sql agent has access to the files.

[quoted text, click to view]

Thanks for the reference, I have read through and I believe that I have
addressesed these issues.

I have determined that the VB program is actually executing from the SQL
Server Agent as a scheduled job. Several steps such as copying files to a
folder are carried out before I call the WinZip program and they are done.
There are other steps after the shell command that execute as well. The job
completes without errors when run from the server agent but it doesn't unzip
the file.

I noticed that after I run the job in server agent the wzunzip.exe process
is running in the processes tab of task manager. Each time I run the job
another instance of wzunzip.exe ia added to the list of processes. When I
run from DTS package the wzunzip.exe does not remain in the list of running
processes.

Any thoughts on why the wzunzip.exe is not running when executed as job from
the Server Agent?

Thanks, Neal...


[quoted text, click to view]

Hi Neal,

Because SQL job is run through the SQL Server Agent service, any popup
messages that could cause SQL job to hang do not appear on your desktop. To
see if the SQL job is raising any popup messages, you will need to run SQL
Server and SQL Server Agent as an application, not as a service. Follow the
steps below:

a. Login to the Windows server with the same account used to start the SQL
Server service.

b. Open a command prompt window. If you are using SQL Server 2000, you will
need to navigate to the directory where the executable files for your SQL
Server instance are installed. (i.e to MSSQL\Binn folder)

c. Start SQL Server as an application by executing this command:
D:\MSSQL\Binn>sqlservr -c
NOTE: For SQL Server 2000, if you are using a named instance, you need
to add -s with the instance name.

Start SQL Server as an application by executing this command:
D:\MSSQL\Binn>sqlagent -c

d. Connect to SQL Server with Enterprise Manager and run SQL Server job.

e. If a popup window appears, then we have found the source of the problem.
Unless we can remove the pop-up window, it is not possible to run it in SQL
Server job when SQL Server Agent is turn as service.

By the way, does executing "c:\winzip\wzunzip.exe c:\todo\pczip001.zip
c:\todo\" or the VB program in Command prompt directly bring a pop-up
window?


Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------
| Reply-To: "Neal Boxrud" <nboxrud@hastingspark.com>
| From: "Neal Boxrud" <nboxrud@hastingspark.com>
| Newsgroups: microsoft.public.sqlserver.dts
| References: <HIieb.85$ww6.1102@jekyl.ab.tac.net>
<BpRA4DBiDHA.2624@cpmsftngxa06.phx.gbl>
| Subject: Re: Scheduled DTS Package fails to execute WinZip.
| Lines: 116
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <E8Zgb.11$a6.230@jekyl.ab.tac.net>
| Date: Wed, 8 Oct 2003 11:59:40 -0700
| NNTP-Posting-Host: 209.52.84.67
| X-Trace: jekyl.ab.tac.net 1065639588 209.52.84.67 (Wed, 08 Oct 2003
12:59:48 MDT)
| NNTP-Posting-Date: Wed, 08 Oct 2003 12:59:48 MDT
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!nntp.abs.net!newsfeed2.
telusplanet.net!newsfeed.telusplanet.net!newsfeed.telus.net!hekyl.ab.tac.net
!jekyl.ab.tac.net!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.dts:39618
| X-Tomcat-NG: microsoft.public.sqlserver.dts
|
| Thanks for the reference, I have read through and I believe that I have
| addressesed these issues.
|
| I have determined that the VB program is actually executing from the SQL
| Server Agent as a scheduled job. Several steps such as copying files to a
| folder are carried out before I call the WinZip program and they are done.
| There are other steps after the shell command that execute as well. The
job
| completes without errors when run from the server agent but it doesn't
unzip
| the file.
|
| I noticed that after I run the job in server agent the wzunzip.exe process
| is running in the processes tab of task manager. Each time I run the job
| another instance of wzunzip.exe ia added to the list of processes. When I
| run from DTS package the wzunzip.exe does not remain in the list of
running
| processes.
|
| Any thoughts on why the wzunzip.exe is not running when executed as job
from
| the Server Agent?
|
| Thanks, Neal...
|
|
[quoted text, click to view]
| > Hi Neal,
| >
| > Running DTS packaged as scheduled job has certain limitation and careful
| > considerations. I suggest that you read the following article first:
| > 269074 INF: How to Run a DTS Package as a Scheduled Job
| > http://support.microsoft.com/?id=269074
| >
| > It is better to add some error handling code to the VB program and write
| > the error to some file, so we can check the file for error details.
| >
| > In addition, we need to check which security context the job is running
| > under (269074 tells us how to check) and make this account has Full
| Control
| > access to the UNC directory. Does changing UNC path to a local work,
e.g.
| > RetVal = Shell("c:\winzip\wzunzip.exe c:\todo\pczip001.zip c:\todo\",
| > vbHide)?
| >
| >
| > Bill Cheng
| > Microsoft Online Partner Support
| >
| > Get Secure! - www.microsoft.com/security
| > This posting is provided "as is" with no warranties and confers no
rights.
| > --------------------
| > | Reply-To: "Neal Boxrud" <nboxrud@hastingspark.com>
| > | From: "Neal Boxrud" <nboxrud@hastingspark.com>
| > | Newsgroups: microsoft.public.sqlserver.dts
| > | Subject: Scheduled DTS Package fails to execute WinZip.
| > | Lines: 29
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| > | Message-ID: <HIieb.85$ww6.1102@jekyl.ab.tac.net>
| > | Date: Tue, 30 Sep 2003 10:04:38 -0700
| > | NNTP-Posting-Host: 209.52.84.69
| > | X-Trace: jekyl.ab.tac.net 1064941479 209.52.84.69 (Tue, 30 Sep 2003
| > 11:04:39 MDT)
| > | NNTP-Posting-Date: Tue, 30 Sep 2003 11:04:39 MDT
| > | Path:
| >
|
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!newsfee
| >
|
d01.sul.t-online.de!t-online.de!newspeer1-gui.server.ntli.net!ntli.net!peer0
| >
|
1.cox.net!peer02.cox.net!cox.net!news3.optonline.net!newsfeed.telusplanet.ne
| > t!newsfeed.telus.net!hekyl.ab.tac.net!jekyl.ab.tac.net!not-for-mail
| > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.dts:39259
| > | X-Tomcat-NG: microsoft.public.sqlserver.dts
| > |
| > | Hi,
| > |
| > | I am having a problem scheduling a DTS package via SQL Server Agent.
| > |
| > | I have created a DTS package that has 1 task in it which runs a VB6
| > program
| > | that copies a file to a specified folder and then uses the Shell
command
| > to
| > | run wzunzip.exe to unzip the file.
| > |
| > | My problem is that the package runs fine when I run it manually from
the
| > DTS
| > | "Local Packages" folder but when I schedule it to run from the SQL
| Server
| > | Agent, it fails to unzip the files. The copy process works but it
| doesn't
| > | execute the wzunzip.exe program.
| > |
| > | Here is the section of VB code that runs winzip:
| > | RetVal = Shell("\\srvr-sql2\winzip\wzunzip.exe
| > \\srvr-sql2\todo\pczip001.zip
| > | \\srvr-sql2\todo\", vbHide)
| > | MsgBox "Shell ended with error code: " & RetVal
| > |