2

Useally I am catching error like this

EXECUTE (@STATEMENT)

SELECT @ERR_CODE = @@ERROR

its working on simple errors , like below query .. @@ERROR return value

 insert into tab1 values(1) -- error attempt to insert unique constraint

however the below query also give unique constraint error however the @ERROR dont catch it it return null value

insert into tab1 select id from tab2

so the above statement give unique constraint but @ERROR doesnt catch it

another example I have the below error 

sybase could not acquire a lock within the specified wait period

@ERROR didnt catch either

My question is there a way where I can catch any error where executing statement ?

Moudiz
  • 7,211
  • 22
  • 78
  • 156
  • Have you looked at this: http://stackoverflow.com/questions/23631224/sybase-is-it-possible-to-set-error/23631781#23631781 – Mike Gardner Jan 25 '16 at 15:39
  • @MichaelGardner in your answer your explaining how to set an error however my question is why the @@error not returning lal the errors , is there another way? – Moudiz Jan 25 '16 at 17:15
  • I'm not sure. I just know that in our application, we would define a large number of custom errors, and test for/throw those errors during execution. Capturing all the errors seems tricky based on the other questions I recall seeing on SO. I'm am not a developer, so I don't know the details of it, unfortunately. – Mike Gardner Jan 25 '16 at 17:20
  • @MichaelGardner well the `uniqueconstraint` error is really simple however its not catching it if i tried it with `insert.. select` . its bit complicated if I start setting message, though ill consider it . – Moudiz Jan 25 '16 at 17:40
  • No - This RobV - http://stackoverflow.com/users/3594999/robv Rob Vershoor. He is on the SAP/Sybase Evangelism team, and runs sypron.nl, a good resource for ASE/IQ/Rep Server – Mike Gardner Jan 25 '16 at 17:44
  • 1
    @MichaelGardner I hope he can find a trick for this . thanks you for your support micheal – Moudiz Jan 25 '16 at 17:48

1 Answers1

0

Are you absolutely sure you've got no statement between your insert with a constraint failure and looking at @@error? Anything at all would reset @@error.

It's not the fact that you are using dynamic SQL in EXECUTE() - @@error will still be available.

Try doing the same as me below - do you get different?

create table tempdb..abe(a int)
select 1 a into #a

insert #a values (1)

create unique index x on tempdb..abe(a)
insert tempdb..abe select * from #a
Msg 2601, Level 14, State 2:
Server 'CRENG_QA', Line 1:
Attempt to insert duplicate key row in object 'abe' with unique index 'x'
Command has been aborted.
(0 rows affected)
select @@error

 -----------
        2601

execute('insert tempdb..abe select * from #a')
Msg 2601, Level 14, State 2:
Server 'CRENG_QA', Line 1:
Attempt to insert duplicate key row in object 'abe' with unique index 'x'
Command has been aborted.
(0 rows affected)
select @@error

 -----------
        2601
Abe Crabtree
  • 524
  • 3
  • 9