Error Handling in Insert Into...Select from

[Tinkerers]

Hi,

I've spent hours on this. I have the following sql:

set @SSQL='SELECT * from openquery(MySqlTable,''select SampleTime,Sum(T2*.01)
FROM '+@PortSamples'
group by SampleTime'')'

INSERT INTO SQL_Server.dbo.TableA
(SampleTime,T2)
--execute the text inside the @SSQL variable
exec (@SSQL)

The problem is, sometimes the select statement will return an error because of corruption in the MySqlTable, where it cannot read 1 of the tables. This is all enclosed in a loop so I cannot remove the exec command and the variable for the table name.

How can I add error handling to simply bypass the error and continue execution ? Otherwise the SQL works mint.

Thanks for any help !!!
Paul

 

[Crowley16]

check the @@error value after running your execute command

best way is to save the @@error into a variable, and then test wether that variable is 0...

--------------------
Procrastinate Now!

 

[Tinkerers]

I tried checking the @@Error value and it correctly identifies the error, however, execution stops. I cannot figure out how to code this so it will skip this table and continue execution.

 

[Crowley16]

Oh, you're running the sql directly without looping and checking the tables...

in this case, you will need to first put in a statement to check that the table in question exists, and if so, then do the execute...

--------------------
Procrastinate Now!

 

[Tinkerers]

The problem is "The table DOES exist". When I try to read from it, an error is generated that says it's corrupt. The exact same thing happens when I try to read using a MySql browser application. I need to skip the corrupt table and continue code execution and have been unable to do this.

As soon as the code hits this error, I can't even execute something like select @read_error=@@error, because the execution stops dead.

Ideas ?

Thanks,
Paul

 

[SQLDenis]

that is a non trapable error and will terminate a batch
read this

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

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ptheriault]

what is the severity of the error?

- Paul

- If at first you don't succeed, find out if the loser gets anything.