Solved

Activex Job step Constants

Posted on 2004-08-12
7
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

734 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