Solved

Can I catch Error Severity 16 and continue processing

Posted on 2010-11-15
2
474 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:verdante
2 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 34141781
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.
0
 

Author Closing Comment

by:verdante
ID: 34142040
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
      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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question