Erro trapping question -- sql server (alternate)

OakRogbak_erPine NO[at]SPAM yahoo.com
10/14/2004 7:45:51 AM

I have a batch file that runs SQL Server scripts using commands like:

OSQL -Umyname -Pmypassword -iScript_01.sql -w200 -e -n
[quoted text, click to view]

Script_01.sql will contain statements like:
Update SASI.AACT set schoolnum='071' where schoolnum in ('000',' ')
update SASI.AATD set schoolnum='071' where schoolnum in ('000',' ')
update SASI.AATP set schoolnum='071' where schoolnum in ('000',' ')
update SASI.ACHS set schoolnum='071' where schoolnum in ('000',' ')
update SASI.ACLS set schoolnum='071' where schoolnum in ('000',' ')

If one of those tables should not exist, how could I have it continue,
but hopefully the log would have a reference to the error?

I am experimenting, but I am unsuccessfull with something like:
BEGIN TRAN
select count(*) from sasi.aact --this could be an update
statement
if @@ERROR =208 GOTO err_handle
select count(*) from sasi.astu
if @@ERROR <> 0 GOTO err_handle
select count(*) from sasi.astu
if @@ERROR <> 0 GOTO err_handle
select count(*) from sasi.astu
if @@ERROR <> 0 GOTO err_handle
err_handle:
return

Simon Hayes
10/15/2004 11:18:11 AM

[quoted text, click to view]

Error handling is rather awkward in MSSQL:

http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

In your case, the easiest thing is probably to avoid the error by checking
if the table exists before trying to query it:

if object_id('sasi.aact') is not null and
objectproperty(object_id('sasi.aact'), 'IsTable') = 1
begin
.... -- do something here
end
else
begin
.... -- log to an error table
end

Simon

sql server (alternate) : Erro trapping question