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

SQLSERVERAGENT JOB DOES NOT TERMINATE WITH DTS TASK WORKFLOW SCRIPT

I have had an sql 2000 database running on several servers no problem. Recently setup the same system on MSDE 2000  Windows 2003 server and for some reason the sqlserveragent job that updates the database does not terminate. After some testing it appears to occur whenever I have the workflow activex script enabled in the initial task of the dts job. The script checks if new copies of the flat files have been generated before allowing the job to run. If I disable this activex workflow script and schedule the job the job will terminate normally.

The reason for the script is the flat files that I am importing to the database are generated from several sources and the timing is uncertain. So I schedule the job to run every hour with the workflow script preventing the job from running any of the tasks unless a complete new set of flat files are ready.

The sqlserveragent does  log an error  "Unable to read local eventlog (reason: The data area passed to a system call is too small)" each time the job runs. Don't see this error on any of the sql 2000 servers.

One  peculiar thing is that I log to the db the progress of key steps in the dts jobs with the last step of the job logging the job is complete...  these entries indicate that all steps have completed. All the tables have been updated.

I tried logging onto the server with the same credentials as the sqlserveragent... exec each step in the job manually and verifying the sqlserveragent error log each time.... looking for the step that may be generating the error.  No error was generated. But if I execute the dts package as a whole it generates the error but terminates normally.
Any suggestions....

Code from the workflow script
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
      
dim sCatalog , sDataSource
sCatalog = DTSGlobalVariables("gvCatalog").Value
sDataSource = DTSGlobalVariables("gvDataSource").Value

Dim con, rs, rsFiles, rsLastUpdate, conString, XtractReadyFlg
Set con = CreateObject("ADODB.Connection")
conString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;"
conString = conString & "Persist Security Info=False;Initial Catalog=" & sCatalog &";Data Source=" & sDataSource

con.Open conString
XtractReadyFlg = 0
Set rsLastUpdate = con.Execute("SELECT     MAX(CONVERT(char, ImportDate, 1)) AS LastDTS FROM dbo.tbl_process_status WHERE     (Status = N'DTS Job Started')")

If Not rsLastUpdate.EOF Then
If DateDiff("d", rsLastUpdate.Fields("LastDTS").Value, Date) > 0 Then
   
    Dim FileDate, OldFileDate, sFilePathName
    Dim fso, objFile
    Set fso = CreateObject("Scripting.FileSystemObject")
   
    Set rs = CreateObject("ADODB.Recordset")
    Set rsFiles = CreateObject("ADODB.Recordset")
   
    Set rsFiles = con.Execute("SELECT  FileName, ExportDir, LdFileStr FROM dbo.tbl_Eval_SAP_FTP")
    If Not rsFiles.EOF Then
    rsFiles.MoveFirst
   
        Do
            Set rs = con.Execute("SELECT TOP 1 FileDate FROM   dbo.tbl_process_status WHERE   (Status LIKE N'" & rsFiles.Fields("LdFileStr").Value & "')  ORDER BY FileDate DESC")
            If Not rs.EOF Then OldFileDate = rs.Fields("FileDate").Value
            sFilePathName = (rsFiles.Fields("ExportDir").Value & rsFiles.Fields("FileName").Value)
            If fso.FileExists(sFilePathName) Then
                Set objFile = fso.GetFile(sFilePathName)
                FileDate = objFile.DateLastModified
            End If
           
            'MsgBox sFilePathName & " " & OldFileDate & " " & FileDate
           
            'MsgBox DateDiff("N", OldFileDate, FileDate)
            If DateDiff("N", OldFileDate, FileDate) <= 0 Then
                XtractReadyFlg = 0
                Exit Do
            Else
                XtractReadyFlg = 1
            End If
            rsFiles.MoveNext
        Loop Until rsFiles.EOF
    End If
   
    'MsgBox XtractReadyFlg
   
    Set fso = Nothing
    Set objFile = Nothing
    rs.Close
    rsFiles.Close
End If
End If

rsLastUpdate.Close
Set rs = Nothing
Set rsFiles = Nothing
Set rsLastUpdate = Nothing
con.Close
Set con = Nothing


If XtractReadyFlg Then
    Main = DTSStepScriptResult_ExecuteTask
Else
    Main = DTSStepScriptResult_DontExecuteTask
End If



End Function
0
guilloryt
Asked:
guilloryt
1 Solution
 
Eugene ZCommented:
please clarify:
1. same pack is running fine on another boxes. Is it correct?
2.If 'yes': check, please: .what mssql service  NT accout do you use on the box?- Local admin?
         
3. check MSDTC service settings on the win 2003 box  vs. good ones->
http://support.microsoft.com/kb/555017

4. What win 2003 sp do you have? same for sql server?
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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