Can't RAISERROR for message 1205

Question

Can't RAISERROR for message 1205

seth delconte

SSCertifiable

Points: 6388
More actions
October 13, 2007 at 6:49 pm

#120796

I'm trying to call a RAISERROR for message 1205 like this: RAISERROR(1205,-1,-1) for testing purposes. I'm testing an alert that I made for deadlocks. When I try to test it by calling the RAISERROR, I get this error message:
Msg 2732, Level 16, State 1, Line 2
Error number 1205 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000.
If anyone can tell me why SQL won't allow me to access error 1205, I would appreciate it.
Thanks.
_________________________________
seth delconte
http://sqlkeys.com

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

Ironically, the error given is the answer.

error numbers belwo 50,000 are reserved, and can only be raised by the SQL Server engine. you can read the list of error messages in master.dbo.sysmessages. to raise an error via code, you have to start with 50001 and any number greter than that.

Books Online gives this example, so you can see that an error can be raised:

RAISERROR (50005, 16, 1, @@JOB_ID, @@MIN_LVL, @@MAX_LVL)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

seth delconte SSCertifiable Points: 6388 More actions · Answer 2

Ok, so no way to test the alert then, aside from letting a deadlock ocurr?

_________________________________
seth delconte
http://sqlkeys.com

Lowell SSC Guru Points: 323504 More actions · Answer 3

sure you can, you just have to raise the error yourself:

begin

[logic for determining if an issue appears here]

declare @err varchar(400)

set @err='Transaction (Process ID ' + convert(varchar,@@spid) + ') was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction. '

RAISERROR(@err,-1,-1)

end

Results:

Transaction (Process ID 53) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

seth delconte SSCertifiable Points: 6388 More actions · Answer 4

Thanks, it works fine.

_________________________________
seth delconte
http://sqlkeys.com