Tuesday, November 20, 2012 8:09 PM
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 way that works - selecting something from the table that doesn't exist, script looks like this:
However, this just seems like a kludge and I am looking for a 'proper' approach.
declare @DBIsRO int;
SET @DBIsRO= (SELECT is_read_only
WHERE name = 'test')
IF @DBIsRO=1 select * from dd -- table dd doesn't exist in current database
- Edited by JoeSchmoe115 Tuesday, November 20, 2012 8:53 PM
Tuesday, November 20, 2012 9:51 PM
Well, I should've paid more attention - documentation 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 ...
Credit goes to user danihp at Stack Overflow
- Marked As Answer by JoeSchmoe115 Tuesday, November 20, 2012 9:51 PM