sql - raiserror shows one message, but not another -
i have code:
declare @timestamp varchar(20), @latestfeed int, @largestkeyprocessed int, @nextbatchmax int, @rc int; set @timestamp = cast(current_timestamp varchar(20)); set @latestfeed = (select max([feed_id]) [dbo].[caqh_resp_all_test_mirror]); set @largestkeyprocessed = (select min([record_id]) - 1 [dbo].[caqh_resp_all_test_mirror] [feed_id] = @latestfeed); set @nextbatchmax = 1; set @rc = (select max([record_id]) [dbo].[caqh_resp_all_test_mirror]); raiserror(@timestamp, 0, 1) nowait raiserror(@largestkeyprocessed, 0, 2) nowait while (@nextbatchmax < @rc) begin begin try --do stuff commit transaction flaghandling raiserror('transaction committed', 0, 3) nowait raiserror(@timestamp, 0, 4) nowait raiserror(@largestkeyprocessed, 0, 5) nowait end try begin catch --catch stuff end catch end
it seems run fine, has couple of things seem odd me. @ outset, prints date i'd expect see it, prints actual error message raiserror i'm using. i'm using exact same syntax, 1 prints desired timestamp, while next produces
jul 23 2015 9:09am msg 18054, level 16, state 1, line 16 error 33218606, severity 0, state 2 raised, no message error number found in sys.messages. if error larger 50000, make sure user-defined message added using sp_addmessage.
then, after transaction committed, correctly shows following messages:
transaction committed jul 23 2015 9:11am
but leaves out last message should show value of @largestkeyprocessed
. raiserror message same 1 raiserror message @ beginning of script produces aforementioned odd behavior.
i want have messages print messages window without looking error, , want have messages print. doing wrong here?
you're giving int raiserror, means you're using msg_id functionality:
raiserror ( { msg_id | msg_str | @local_variable } { ,severity ,state } [ ,argument [ ,...n ] ] ) [ option [ ,...n ] ]
msg_id:
is user-defined error message number stored in sys.messages catalog view using sp_addmessage. error numbers user-defined error messages should greater 50000. when msg_id not specified, raiserror raises error message error number of 50000.
you should use varchar variables, , can contain number want send. see documentation.
Comments
Post a Comment