verdante
asked on
Can I catch Error Severity 16 and continue processing
Hi
I am performing a data migration in SQL Server 2005. I am attepting to create a script that does insert into table_2 select from table_1 and captures errors but then continues to process. I have run some tests with part of the table and have generated errors with Error Severity 16 where I have null or otherwise problematic values. This causes the Try statements to cease execution. What I want to be able to do is run the insert statement, capture any errors including the error message and recordnumber that had the error then continue processing the remaining records. Is that possible?
thx
Verdy
I am performing a data migration in SQL Server 2005. I am attepting to create a script that does insert into table_2 select from table_1 and captures errors but then continues to process. I have run some tests with part of the table and have generated errors with Error Severity 16 where I have null or otherwise problematic values. This causes the Try statements to cease execution. What I want to be able to do is run the insert statement, capture any errors including the error message and recordnumber that had the error then continue processing the remaining records. Is that possible?
thx
Verdy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
declare @rn int
declare @maxid int
delete test_results
set @rn = 0
set @maxid = (select max(id) from test_table_2) + 1
while @rn < @maxid
begin
set @rn = @rn + 1
BEGIN TRY
insert into test_table_1
select * from test_table_2 as t2 where t2.id = @rn
END TRY
BEGIN CATCH
Insert into test_results (row_id,error) values(@rn,ERROR_MESSAGE()
END CATCH;
end