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