• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

Can I catch Error Severity 16 and continue processing

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?
1 Solution
Raja Jegan RSQL Server DBA & ArchitectCommented:
If you are performing Row by Row operations using WHILE or CURSOR statements in your code, then you can use @@ROWCOUNT to identify whether the last operation was successful or not and Log those error records and proceed with the next records.
If you are doing SET based operations like INSERT INTO or SELECT INTO statements, you can't identify the error records and proceed with next valid record. In this case you can do like this

a. Check for Null values in records like

INSERT INTO ur_table
SELECT from some_table
WHERE column_name is NOT NULL

b. Also check for other conditions like this and insert those valid records alone into your table instead of trying to insert all records in a single shot.
c. Do make sure that the records identified as invalid are logged into error table so that it can be rectified and inserted back.
verdanteAuthor Commented:
Thanks for that - used the WHILE loop. Just for the record the script has the following script

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
        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;
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now