How to fail T-SQL script without using RAISERROR()?

Question

This is what I am trying to accomplish:

Check if database is read-only in first step of SQL Server Agent job. If this is the case - exit the job.

So I figured I will use Advanced Properties for Job Step and select "Quit the job reporting success" for "On failure cation:"

But how does one fail T-SQL script without using RAISERROR()? Reason I don't want to use RAISERROR() is because one needs to be a member of sysadmin server role to use it.

I found rather ugly approach that works - selecting something from the table that doesn't exist, script looks like this:

declare @DBIsRO int;

SET @DBIsRO= (SELECT is_read_only
FROM sys.databases
WHERE name = 'test')

IF @DBIsRO=1 
   select * from dd -- table dd doesn't exist in current database

However, this just seems like a kludge and I am looking for a 'proper' approach.

dani herrera · Accepted Answer · 2012-11-20 21:16:22Z

5

Reread documentation:

Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin ...

You can do:

raiserror( 'Readonly',16,1)

answered Nov 20, 2012 at 21:16

dani herrera

52k11 gold badges133 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Remus Rusanu · Accepted Answer · 2012-11-20 21:17:19Z

0

Reason I don't want to use RAISERROR() is because one needs to be a member of sysadmin server role to use it.

Well if that would be true nobody would ever use it. Is simply not true. The spec says:

Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions.

Even if that would be true, you could use code signing to leverage it. But is simply not necessary.

answered Nov 20, 2012 at 21:17

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Collectives™ on Stack Overflow

How to fail T-SQL script without using RAISERROR()?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related