Learn how to a build a cloud-first strategyRegister Now


SQL Server Agent

Posted on 2012-09-13
Medium Priority
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

Jared_S earned 2000 total points
ID: 38395922
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

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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 different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

810 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