ignore error sqlserver 2000

Question

ignore error sqlserver 2000

John Todd-346730

Old Hand

Points: 345
More actions
July 7, 2006 at 8:46 am

#171515

I've heard that I sqlserver 2000 has no compatibility to ignore errors (like a CONTINUE_ON_ERROR setting, or 2005's try/catch)
Is that true?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 1

Not entirely. I assume you are talking about in stored procedure programming (or trigger, functions, etc) Depending on the error severity, most errors are actually "ignored", and you need to explicitly check @@ERROR if there was an error and you want to handle it. Only the most severe errors (severity 19 or above? but i'm guessing) will stop a procedure.

Mark

John Todd-346730 Old Hand Points: 345 More actions · Answer 2

I got a sql script and I want all sql commands in the script to run regardless of errors that are encountered.

begin transaction

create table a

go

insert into table a select * from b

go

insert into did_it values('Ok did that.')

go

commit

So even if there is no table b, I still want 'Ok Did that' inserted into the did_it table.

- John

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

I assumed I knew what would happen, but I thought I would test it anyway.

create table #a (fld1 int)
--create table #b (fld1 varchar(50))
create table #did_it (fld1 varchar(50))
declare @sql nvarchar(50)

begin transaction

set @sql = N'insert into #a select * from #b'
EXECUTE sp_executesql @sql
--insert into #a select * from #b

insert into #did_it values('Ok did that.')

commit

select * from #did_it

drop table #a
--drop table #b
drop table #did_it

I assumed that it was a fatal severity because the table b was not there when the SQL was prepared. And when I ran it with the commented line like you originally did had it, it was severity level 16, and nothing else processed.

Server: Msg 208, Level 16, State 1, Line 10
Invalid object name '#b'.

However, when I did the dynamic sql, so that the sql would not be checked until the execute command, I got the same error severity, but the rest of the script ran.

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#b'.

(1 row(s) affected)

fld1                                               
-------------------------------------------------- 
Ok did that.

(1 row(s) affected)

That was not what I was expecting. But it is what you were hoping to do.

The other thing you can do, is check if the table exists, and only execute the statement if so.

begin transaction

if exists 
  (select * from dbo.sysobjects where id = object_id(N'[Batch]') and 
  OBJECTPROPERTY(id, N'IsUserTable') = 1)
  BEGIN
    insert into #a select * from #b
  END

insert into #did_it values('Ok did that.')

commit

select * from #did_it

Hope this helps

Mark

John Todd-346730 Old Hand Points: 345 More actions · Answer 4

How do I this 'dynamic sql' thing? looks like just what I want to do.

I'm running my sql script embedded in a batch file so I hope its not a GUI driven thing.

the non-existant table was just an example, I'm patching a database and I want to allow for users to have mucked with it so that it doesn't have to be in any expected state and if a particular statement fails I don't want to stop the train.

- John