Link to home
Start Free TrialLog in
Avatar of lameyers
lameyers

asked on

Fail Job Step if record count on table is zero

I have a job that runs several steps, starting with a truncate of a temp table and then insert from a text file.  After that (a DTS) it runs several stored procedures to do inserts/updates/deletes on the live table from the temp table. Everything runs fine unless a bad field in the text file is sent and it fails during the DTS, therefore importing no new records. (as mentioned above, I truncate the temp table first). So, if it fails to insert the bulk data during the DTS, the JOB doesn't see/read a failure from the DTS and moves on to the next step which is to update the live table, however, it is now updating the live table from a table with 0 records so the live table updates to 0 records, causing item information no longer to display on our website.  I do indicate in the JOB to quit on failure of the DTS, but the JOB just ignores that and continues to the stored procedures.
I need to know what step, or more how to write one in between the DTS and the strored procedures to check the count on the temp table and if the temp table has 0 records, to fail the job and not continue with the stored procedures.
I'm sure it is a simple tsql statement step I can add to the job, but can't get all the syntax correct. I think I use the RAISERROR command but don't know how to write it in to get a correct syntax.
I even created another table to update with the count so I can read off that instead of a straight count.
Here's an example of what I tried. Please note that I have never done this and just tried to put together solutions I found when searching for an answer.

update Item_Master_check
 set success=(Select Count(*) from Item_Master_Temp)

IF (Select Success from Item_Master_check WHERE Success = 0)

RAISERROR ('Not updated.', 16, 1)

I'm sure I can leave out the update and just do a count on Item_Master_temp and if 0 then fail the job.

Can someone help tell me what I need to write into my new step in my job that would help.

If you need more info, just let me know.

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of lluthien
lluthien

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lameyers
lameyers

ASKER

Thank you for the quick response.
I did some initial tests and all appears to work as supposed to.
I've implemented it. If this all goes well over the next week or so, then I'll apply to my other jobs.

Thanks again. Points well deserved!
glad to help