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

Popular posts from this blog

javascript - Karma not able to start PhantomJS on Windows - Error: spawn UNKNOWN -

Nuget pack csproj using nuspec -

c# - Display ASPX Popup control in RowDeleteing Event (ASPX Gridview) -