[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQLSERVERAGENT JOB DOES NOT TERMINATE WITH DTS TASK WORKFLOW SCRIPT

Posted on 2007-08-01
1
Medium Priority
?
577 Views
Last Modified: 2013-11-30
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
Comment
Question by:guilloryt
1 Comment
 
LVL 43

Accepted Solution

by:
Eugene Z earned 2000 total points
ID: 19610112
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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question