Solved

Activex Job step Constants

Posted on 2004-08-12
7
374 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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)

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

707 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

16 Experts available now in Live!

Get 1:1 Help Now