Solved

Activex Job step Constants

Posted on 2004-08-12
7
375 Views
Last Modified: 2008-02-01
I am running a step in a Server agent job that checks for the presence of a file. I want the step to fail if the file does not exist. Easy enough, but I CANNOT find the right constant to pass back to the SQL server agent to give the step a status of failure. Here is my script:
Function Main()
    Dim FileSys
    Dim strFileRoot
    Dim strISAFile
    Dim strToken
    Dim intCount
    Dim FileFound
       
    intCount = 1
    strISAFile = "ISA_IN.TXT"
    strToken = "token.txt"
    Set FileSys = CreateObject("Scripting.FileSystemObject")
    strFileRoot = "C:\isis\dbData\EDI_Admin\"
    FileFound = "False"

    Do While intCount < 500000 and FileFound = "False"
          If Not FileSys.FileExists(strFileRoot & strToken) Then
              intCount = intCount + 1
          Else
              FileFound = "True"            
          End If      
    Loop
                
    If FileFound = "False" Then      
       Main = DTSTaskExecResult_Failure
       Set FileSys = Nothing      
       Exit Function
    End If    

    If Not FileSys.FileExists(strFileRoot & strISAFile) Then
        Main = DTSTaskExecResult_Failure
        Set FileSys = Nothing
        Exit Function
    Else
        Main = DTSTaskExecResult_Success
    End If
    Set FileSys = Nothing
End Function

This was my first try, I know that  DTSTaskExecResult_Success is a datapump constant, but I have tried every job step constant I can find and all still report back success.
0
Comment
Question by:nwfisher
  • 3
  • 2
7 Comments
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11795316
your task is fine. make sure you set your package to fail on first error.

package properties..logging.. fail package on first error.
this will send the error back to sql agent.
0
 

Author Comment

by:nwfisher
ID: 11795432
I actually figured this out about an hour ago. The tale goes like this. There is no way to send back a code from a server agent job step that will cause the step to be logged as "failed". The only way around this problem is to throw up a runtime error using Err.Raise. This script WAS NOT in a DTS package. It is a step in a SQL server agent job.
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11797347
yep. good catch. ask for a points refund
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:nwfisher
ID: 11797357
This is my first question, how do I ask? If it's here, I'm asking.
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11797418
I answered my question myself. What do I do?

 
Post a question in the Community Support topic area asking for a refund, and asking the Moderators to close the question. You'll be required to post your solution in your original question. A Moderator will post a notice of your request which will give the participants 96 hours to object to the refund. Note that if it resembles one of the suggested comments, the likelihood is that your request will not be granted, but rather, the points will be awarded to the Expert who makes the suggestion. In your Community Support request, remember to post a link to the original question.
 
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 12098323
PAQed, with points refunded (125)

GhostMod
Community Support Moderator
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

914 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

13 Experts available now in Live!

Get 1:1 Help Now