Posted on 2007-08-01
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
            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
                XtractReadyFlg = 1
            End If
        Loop Until rsFiles.EOF
    End If
    'MsgBox XtractReadyFlg
    Set fso = Nothing
    Set objFile = Nothing
End If
End If

Set rs = Nothing
Set rsFiles = Nothing
Set rsLastUpdate = Nothing
Set con = Nothing

If XtractReadyFlg Then
    Main = DTSStepScriptResult_ExecuteTask
    Main = DTSStepScriptResult_DontExecuteTask
End If

End Function
Question by:guilloryt
    1 Comment
    LVL 42

    Accepted Solution

    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->

    4. What win 2003 sp do you have? same for sql server?

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now