December 23, 2011 at 9:34 am
All,
I have the below scenario.
create table t
(
eno int primary key
)
alter proc p1
as
begin
BEGIN TRY
BEGIN TRAN
insert into t
select 1
union all
select 1
union all
select 2
union all
select 3
COMMIT TRAN
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage
into error_log
END CATCH;
end
I would like to insert 1 , 2, 3 in the table and the error information should go into a error log table.
is it possible? Inputs are welcome!
karthik
December 23, 2011 at 10:25 am
Yes, insert the error information into a table variable, do the rollback in the catch block (or you'll be left with an uncommitted transaction) then insert the contents of the table variable into the error log table after the rollback.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 25, 2011 at 11:21 pm
I have changed my code as below.
ALTER proc [dbo].[p1]
as
begin
DECLARE @ERROR_LOG TABLE
(
ErrorNumber INT,
ErrorSeverity INT,
ErrorState INT,
ErrorProcedure VARCHAR(255),
ErrorLine VARCHAR(255),
ErrorMessage VARCHAR(1000)
)
;
BEGIN TRY
insert into t
select 1
union all
select 1
union all
select 2
union all
select 3
END TRY
BEGIN CATCH
INSERT INTO @ERROR_LOG
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage
ROLLBACK
END CATCH;
End
But still I don't see any record into "t" table.
karthik
December 26, 2011 at 4:06 am
see below code
alter proc p1
as
begin
BEGIN TRY
truncate table error_log
BEGIN TRAN
insert into t
select 1
union all
select 1
union all
select 2
union all
select 3
COMMIT TRAN
END TRY
BEGIN CATCH
rollback
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage
into error_log
END CATCH;
end
go
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 26, 2011 at 6:18 am
I don't see any records in the table. 🙁
karthik
December 26, 2011 at 6:34 am
Which table you are talking about ? T table cant have data until we have PK satisfied data. and by design error table population also wrong as every time Sp execution looking to create errorlog table
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 26, 2011 at 7:32 am
Which table you are talking about ?
Yes. T table only.
T table cant have data until we have PK satisfied data
Thats what i need. I would like to to insert the remaining rows.
karthik
December 26, 2011 at 8:09 am
Please alter your PK index.
IGNORE_DUP_KEY = { ON | OFF }
Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.
ON
A warning message will occur when duplicate key values are inserted into a unique index. Only the rows violating the uniqueness constraint will fail.
OFF
An error message will occur when duplicate key values are inserted into a unique index. The entire INSERT operation will be rolled back.
IGNORE_DUP_KEY cannot be set to ON for indexes created on a view, non-unique indexes, XML indexes, spatial indexes, and filtered indexes.
To view IGNORE_DUP_KEY, use sys.indexes.
In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON.
For More: http://technet.microsoft.com/en-us/library/ms188388.aspx
December 26, 2011 at 11:52 pm
I believe...when we turn this option to ON, we can't capture the duplicate error message in the table. Here i come to conclusion as below.
If the IGNORE_DUP_KEY IS OFF, TRY block will throw the error message and it will be handled in CATCH block. It never come back to TRY block to process the remaining rows. We can either commit or rollback in the CATCH block whatever the TRY block processed before the exception. Once the exeception raised, it won't go back to TRY and continue its execution for the remaning rows.
If the IGNORE_DUP_KEY IS ON, TRY block won't throw the error message and all the rows will be processed and the duplicates will be ignored automatically. But it is not possible to catch those duplicates ( error messages) in CATCH block as it is not considered as the exception.
karthik
December 26, 2011 at 11:53 pm
There is no way to capture the error message as well as to insert all the rows (except the problematic one) by using TRY CATCH.
Am i correct?
karthik
December 27, 2011 at 12:02 am
"catch" works when logic/code under "Try" get failed.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 27, 2011 at 12:07 am
Then you should go for Dirty Solution i.e. cursor / loop & catch errors for each row.
December 27, 2011 at 2:10 am
Thats what my first thought (using cursor) on this requirement. But...i just wanted to do this without using BAD CURSOR 🙁
karthik
December 27, 2011 at 3:43 am
karthikeyan-444867 (12/27/2011)
Thats what my first thought (using cursor) on this requirement.
Try with LOOP logic
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 27, 2011 at 6:01 am
LOOP ?
WHILE LOOP ?
karthik
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply