July 13, 2012 at 8:21 am
I have a requirement were in we need to catch the exact error record during an insert operation. We have implemented the solution using a TRY..CATCH block, where the set based operation goes into the TRY block. If we encounter error the CATCH block implements a cursor logic to get hold of the exact record set. Is there any better solution which we can implement, I strongly believe this is just an adhoc solution and must have a better solution.
Code Sample:-
Begin Try
Insert into MyTable(id,name)
SELECT id,name from Names where country = 'INDIA'
End Try
Begin Catch
SET @MyCursor = CURSOR FOR SELECT id,name from Names where country = 'INDIA'
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @id,@name
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
Insert into Mytable (id,name)
values (@id,@name)
END TRY
BEGIN CATCH
Update Secondtable
Set Status = 'Error'
Where id = @id
END CATCH
FETCH NEXT FROM @MyCursor
INTO @id ,@name
CLOSE @MyCursor
DEALLOCATE @MyCursor
End Catch
July 13, 2012 at 9:02 am
You don't need a cursor in catch. Primary key, ERROR_NUMBER, ERROR_LINE and ERROR_MESSAGE should be enough.
Try this:
DECLARE @MyTable AS TABLE (id INT NOT NULL PRIMARY KEY CLUSTERED, name VARCHAR(10) NULL)
BEGIN TRY
-- This PRINT statement will run because the error
-- occurs at the SELECT statement.
PRINT N'Starting execution';
INSERT INTO @MyTable(id,name)VALUES(1,'INDIA')
INSERT INTO @MyTable(id,name)VALUES(2,'INDIA')
INSERT INTO @MyTable(id,name)VALUES(3,'INDIA')
INSERT INTO @MyTable(id,name)VALUES(3,'INDIA')
--SELECT id,name from Names where country = 'INDIA'
SELECT * FROM @MyTable
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine;
END CATCH;
GO
July 13, 2012 at 9:21 am
Well that doesn't really solve the OP's issue. They have an insert and want to know which line in the data is causing the issue. I modified your example to demonstrate.
DECLARE @MyTable AS TABLE (id INT NOT NULL PRIMARY KEY CLUSTERED, name VARCHAR(10) NULL)
BEGIN TRY
-- This PRINT statement will run because the error
-- occurs at the SELECT statement.
PRINT N'Starting execution';
declare @Source table (id int not null, name varchar(10))
insert @Source
select 1,'INDIA' union all
select 2,'INDIA' union all
select 3,'INDIA' union all
select 3,'INDIA'
INSERT INTO @MyTable
select * from @Source
--SELECT id,name from Names where country = 'INDIA'
SELECT * FROM @MyTable
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine;
END CATCH;
GO
This does not show the line in the source that caused the issue, it does a great job of showing which line caused the error though.
Seems to me that the request is a bit strange but I am not sure how else you would figure out which row caused the insert error other than a **cough**cursor**cough**. I would reconsider carefully before putting a cursor into a catch...or any code for that matter.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 13, 2012 at 9:38 am
Sean Lange (7/13/2012)
Well that doesn't really solve the OP's issue. They have an insert and want to know which line in the data is causing the issue. I modified your example to demonstrate.
DECLARE @MyTable AS TABLE (id INT NOT NULL PRIMARY KEY CLUSTERED, name VARCHAR(10) NULL)
BEGIN TRY
-- This PRINT statement will run because the error
-- occurs at the SELECT statement.
PRINT N'Starting execution';
declare @Source table (id int not null, name varchar(10))
insert @Source
select 1,'INDIA' union all
select 2,'INDIA' union all
select 3,'INDIA' union all
select 3,'INDIA'
INSERT INTO @MyTable
select * from @Source
--SELECT id,name from Names where country = 'INDIA'
SELECT * FROM @MyTable
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine;
END CATCH;
GO
This does not show the line in the source that caused the issue, it does a great job of showing which line caused the error though.
Seems to me that the request is a bit strange but I am not sure how else you would figure out which row caused the insert error other than a **cough**cursor**cough**. I would reconsider carefully before putting a cursor into a catch...or any code for that matter.
Look to move the process over to SSIS. There are some simply options on capturing the records in a source file causing errors.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 13, 2012 at 9:43 am
Matt Miller (#4) (7/13/2012)
Look to move the process over to SSIS. There are some simply options on capturing the records in a source file causing errors.
Well that would certainly be an option. 😀 I am by no means an SSIS guru, or even a rank novice for that matter. Doing it in pure t-sql would suck. glad to hear SSIS has an easy way to identify that. I will have to do some exploring.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 14, 2012 at 9:04 am
Sorry Matt, cannot move over to use SSIS.:crying:
July 23, 2012 at 11:12 am
arun1_m1 (7/14/2012)
Sorry Matt, cannot move over to use SSIS.:crying:
Then the next answer is to know WHAT would cause insert errors. Tables have a definite structure, with definite constraints, so type checks, constraint checks would be where I go next. Rather than performing the insert one at a time until you hit the error and THEN still have to play the "why did this fail" game, just use a form of data cleansing to weed out the rows that fail the insert.
Now,data cleansing will slow down your process somewhat, since those checks can be time-consuming. That said - I've fond that it is usually MUCH faster than running the entire insert as a cursor.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 23, 2012 at 12:08 pm
One more option: if you have a way to actually "walk" the input file, you could create smaller batches to import. Then walk through the import in batches, and find the issues that way:
DECLARE @MyTable AS TABLE (id INT NOT NULL PRIMARY KEY CLUSTERED, name VARCHAR(10) NULL)
declare @Source table (id int not null, name varchar(10))
declare @errors table (id int not null, name varchar(10))
declare @startingID int, @batchsize int, @endingID int
select @startingID=1,
@batchsize = 2 --in a "real" system - make the batch size much larger, then work your way down
insert @Source
select 1,'INDIA' union all
select 2,'INDIA' union all
select 3,'INDIA' union all
select 3,'INDIA'
select @endingID=MAX(id) from @Source;
While (@endingID>=@startingID)
BEGIN
BEGIN TRY
print 'hi'
INSERT INTO @MyTable
select *
from @Source
where id between @startingID and @startingID+@batchsize-1
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine;
INSERT INTO @Errors
select *
from @Source
where id between @startingID and @startingID+@batchsize-1
END CATCH;
set @startingID = @startingID+@batchsize
END
SELECT * FROM @MyTable
SELECT * FROM @errors
GO
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply