intercept error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

intercept error

Author

Topic

casati74
Posting Yak Master

109 Posts

Posted - 2007-01-22 : 05:09:05

Hello,
i wrote this:

set @lsSql1 = 'INSERT INTO '+@lsInvolvedTable+
'([IdTag], [Tag], [NrTubo], [Valore], [IdHeat], [Colata], [Ordine],'+
' [DataOraAcquisizione])'+ ' VALUES ( '+@IdTag+','''+ @TagName+''','+@FieldNumber+', '+@TagValue+', '+@HeatID+','+@HeatNumber+','+@OrderNumber+','''+@lsDataOraAcquisizione+''')'

exec (@lsSql1)

IF (@@ERROR <> 0)
begin
set @TagErrorVal = 1
print ' assegno errore'
print @TagErrorVal
return @TagErrorVal
end

But if exec (@lsSql1)generate error i cant' intercept it by IF (@@ERROR <> 0).
How can i retrive error?!?!?

Thank's

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-22 : 05:12:25

There are two options:
1. Don't use D-Sql
2. If option 1 is not possible, embed all erro handling logic inside D-Sql as well.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

casati74
Posting Yak Master

109 Posts

Posted - 2007-01-22 : 05:21:56

If i try this solution i have the same problem!!!

insert into prova (pu, descr) values('pippo','pippo')
if (@@ERROR <> 0)

begin
RAISERROR ('returned error from doProcessData', 16, 1, 'prova', 'pippo')
execute sp_addmessage 50005, 16, 'error!!!'
RAISERROR (50005, 16, 1, 'prova', 'pippo')
print 'funziona'
end
I recive only the message for conversion not exeed but not a print message!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 05:33:28

The print message is not transferred.

Peter Larsson
Helsingborg, Sweden

casati74
Posting Yak Master

109 Posts

Posted - 2007-01-22 : 05:44:10

I have the same problem if i wrote
declare @error int
set @error = 0

insert into prova (pu, descr) values('pippo','pippo')

if (@@ERROR <> 0)

begin
return @error
end

the if (@@ERROR <> 0) check is not processed!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 05:46:07

I think it is, but the insert statement is not generating an error becuse everything is fine...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 05:47:47

what happens if you try this?
declare @error int
set @error = 0

insert into prova (pu, descr) select replicate('pippo', 1600), replicate('pippo', 1600)

if (@@ERROR <> 0)

begin
return @error
end

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-22 : 05:48:33

[code]declare @error int
set @error = 0

insert into prova (pu, descr) values('pippo','pippo')
Select @error = @@ERROR

if (@@error <> 0)
begin
return @error
~~end~~[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 05:50:54

quote:

Originally posted by harsh_athalye

declare @error int
set @error = 0

insert into prova (pu, descr) values('pippo','pippo')
Select @error = @@ERROR

if (@@error <> 0)
begin
return @error
end

Simplified as

insert into prova (pu, descr) values('pippo','pippo')
return @@error

Peter Larsson
Helsingborg, Sweden

casati74
Posting Yak Master

109 Posts

Posted - 2007-01-22 : 08:30:47

All These is a good metod but if in my case the pu column is not a varchar data type; is int!!!

When i try to perform this insert
insert into prova (pu, descr) values('pippo','pippo')
I recive an error message and the error assignement
Select @error = @@ERROR

is not executed.

In SQL 2005 there is a try catch instruction (and it wok wonderfully)
There is something of similar in Sql 2000??????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 08:44:04

Yes, the @@ERROR variable.
But remember that it is highly volatile. Once referenced, it is set to zero again.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-22 : 08:50:37

What you are trying to do will result in syntax error and hence will not be caught by @@ERROR variable.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources