Solved

Can I catch Error Severity 16 and continue processing

Posted on 2010-11-15
2
472 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with a query 3 44
SQL Query 2 59
Stored procedure 4 32
Query to Add Late Tolerance 10 60
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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 …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

919 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now