SQL Server Agent

Posted on 2012-09-13
Last Modified: 2012-09-13
In a SQL Server Agent job I have the code below and when I schedule the job it correctly returns 3000 rows. However sometimes the data is not at the source location (Oracle table DBPROD.E5V_NB_A_MSR) and I want the job to fail or allow a retry.
The job always runs successfully because it may not bring back any data but still made the select.

I thought I could use an IF @@Rowcount = 0, but I am either placing it incorrectly or it is not allowed.

My question is since my code works but on first attempt may not bring back any data how can I get SQL Server Agent to perform a retry?

Truncate table tblYTD;
Insert Into tblYTD

Select *
from openquery(egp6,

'select from DBPROD.E5V_NB_A_MSR
where NBA_RO_NBR = ''021'' and NBA_TAM_NBR In (''3'',''D'',''F'', ''4'')
and NBA_ACCTG_DT = sysdate - 2
Question by:SeTech
    LVL 12

    Accepted Solution

    You can force a failure in step 1 with this:

    IF Put Your Boolean Test Here
       RAISERROR('50005 Put your message regarding the failure here',16, -1, @@servername )

    Open in new window

    If successful, quit step 1
    If Failed, Retry (set Retry Attempts to be > 0)

    Author Comment

    Could be me, but I do not follow your comments, thanks thou

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    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 setup several different housekeeping processes for a SQL Server.

    758 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

    7 Experts available now in Live!

    Get 1:1 Help Now