Fail Job Step if record count on table is zero

Posted on 2005-04-26
Last Modified: 2010-05-18
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.

Question by:lameyers
    LVL 11

    Accepted Solution

    assuming you have you items in the table Item_Master_Temp.

    you could try this:
    declare @item_count as int
    set @item_count = (select count(*) from Item_master_Temp)
    -- counting the number of items in the temp table.

    then check this with:
    if @item_count <= 0
     raiserror('nice message', 16, 1)


    then in the properties of the job STEP, make sure you set the option "on failure"
    to "finish with fail", to make sure the job stops there.

    i'm not sure you set the properties of the step, instead of the job.


    Author Comment

    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!
    LVL 11

    Expert Comment

    glad to help

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    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
    Viewers will learn how the fundamental information of how to create a table.

    755 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

    17 Experts available now in Live!

    Get 1:1 Help Now