Solved

Can I catch Error Severity 16 and continue processing

Posted on 2010-11-15
2
476 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

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…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

717 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