Solved

How to "force" an error in a job step in SQL Server

Posted on 2011-09-29
3
390 Views
Last Modified: 2012-05-12
Hello,

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?

Thanks

if 0<>(select COUNT(*) from test)
	BEGIN
		RAISERROR (N'There are rows in table' , --message text
					10, --severity
					1, --state
					N'number',	--first argument
					5); --second argument
				-- The message text returned is:  This is message number 5.
	END

Open in new window

0
Comment
Question by:hpsuser
  • 2
3 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 36816733
if 0<>(select COUNT(*) from test)
        BEGIN
                 select 1 from NonExistantTableName where 1=0
        END
0
 
LVL 33

Accepted Solution

by:
knightEknight earned 500 total points
ID: 36816750
Or, change the error level in the raiserror statement:

RAISERROR (N'There are rows in table' , --message text
                                        11, --severity
                                        1, --state
                                        N'number',      --first argument
                                        5); --second argument
0
 

Author Closing Comment

by:hpsuser
ID: 36816969
thanks, works perfectly!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 4 45
Query Peformance + mulitple query plans 9 48
Sql query 107 29
Sql Query Datatype 2 19
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 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

11 Experts available now in Live!

Get 1:1 Help Now