March 19, 2007 at 1:20 am
Dear all,
I have the following problem:
I currently have an overnight process (VB6 application) that creates and zips some files for my users to get from the FTP server. This seems to be a problem lately due to unexpected behavior of Windows Scheduler as well as my zip utility. So I decided to move this process to SQL. Below please find the SP which is supposed to do the job. Please bear in mind that when this was running OK it needed approximately 20 minutes to complete.
------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyProcName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MyProcName]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE Procedure dbo.MyProcName AS
DECLARE @cmdString as nVarChar(500)
DECLARE @fCode as Int
SET NOCOUNT ON
-- The following cursor selects the code of all active users that are supposed to have a ZIP file
-- It returns approximately 300 user codes
-- ****** Code Cursor ******
DECLARE CodeCursor CURSOR FOR
SELECT Code FROM MyTable ORDER BY Code
-- ****** Code Cursor ******
OPEN CodeCursor
FETCH NEXT FROM CodeCursor INTO @fCode
WHILE @@FETCH_STATUS = 0
BEGIN
-- ZipFiles is a batch file that uses the WinZip Command Line Utility and zips all necessary files in a Zip file per user
-- The appropriate folders are on the FTP server (my users are remote users).
Set @cmdString = 'c:\Projects\WZip\ZipFiles ' + STR(@fCode)
Exec master..xp_cmdshell @cmdString
FETCH NEXT FROM CodeCursor INTO @fCode
END
CLOSE CodeCursor
DEALLOCATE CodeCursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------------------------------------------------
I tested this SP using 4-5 values from a test table and it worked fine. Once I tried a real run, the SP was running for 45 minutes - basically it hanged - without doing anything. I checked the status of the process in Enterprise Manager - Management - Current Activity - Process Info and the status was "runnable" - if this piece of information can be of any help.
Any ideas?
Thanks & Regards,
Andreas Kantounas
March 19, 2007 at 1:53 am
open an RDC connection using your sqlagent-serviceaccount or sqlserver-serviceaccount.
Then try to run one of the FTP cmds from the cmd-prompt.
This way you're sure it uses the securitycontext of your serviceaccount.
Also keep in mind there may be some intrusion on your FTP-server which may slow down your sp.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 19, 2007 at 2:43 am
Your VB6 application must have calls to database to update something in table MyTable.
If this is the case then you've got deadlock.
_____________
Code for TallyGenerator
March 19, 2007 at 2:57 am
Dear all,
Thanks for your replies. However, the problem is much more simple than it was initially speculated. My WinZip 11 Pro along with the WinZip Command-Line Utility was not properly registered and therefore it was expecting the user's interference for every call in my loop.
My testing was done on a different machine and that's why the problem wasa not trapped in the first place.
Thanks anyway.
Andreas Kantounas
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply