?
Solved

Can I catch Error Severity 16 and continue processing

Posted on 2010-11-15
2
Medium Priority
?
483 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

764 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