I have a SQL Server job with several steps. On the first step I need the logic to:
1--check for a table to have zero rows
2--if not zero rows, force an error so that the step will fail and I can set up the step to try again in 15 minutes.
What I have not been able to do is "force" the error correctly. To see what I mean, I put the following code in the first step of my job, but when I run it, regardless of whether the "test" table has zero rows or not, the job shows as having run successfully. Any suggestions?
if 0<>(select COUNT(*) from test)
RAISERROR (N'There are rows in table' , --message text
N'number', --first argument
5); --second argument
-- The message text returned is: This is message number 5.