What cause a transaction to fail? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

What cause a transaction to fail?

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2008-11-22 : 02:00:35

I have a transaction that do several of insert and update statements sometimes the transaction fails to commit with no reason why. which leads to unacceptability to the database or the current activity folder in the enterprise manager until i clear current connections.

What leads a transaction fail to commit? and should i do explicit rollback if the @@transcount>0 after the commit ?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 02:04:41

if any error occurs in between it wont commit. Put it in TRY CATCH blocks and see if any error occurs in b/w within transaction

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 02:08:05

also see this

http://www.sommarskog.se/error-handling-I.html

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-22 : 02:22:41

A transaction can fail for many reason.

Foreign key constraints, conversion errors, datatype errors, NULLs, truncation errors and so on.

E 12°55'05.63"
N 56°04'39.26"

ann06
Posting Yak Master

171 Posts

Posted - 2008-11-22 : 07:57:34

i tried to replicate the transaction failing but was not able to do so, i tried to break these rules foreign key constraints, inserting null, duplicate primary keys. the transaction was committed, for the query with the error it shows the error with the 0 rows affected. but as a whole it commits !!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 11:02:19

quote:
Originally posted by ann06

i tried to replicate the transaction failing but was not able to do so, i tried to break these rules foreign key constraints, inserting null, duplicate primary keys. the transaction was committed, for the query with the error it shows the error with the 0 rows affected. but as a whole it commits !!

you tried with TRY CATCH block also?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-22 : 13:10:13

Show us the code you use.
I think we can spot the error.

E 12°55'05.63"
N 56°04'39.26"

ann06
Posting Yak Master

171 Posts

Posted - 2008-11-23 : 08:06:55

ALTER PROC INSERT_NEW_EFAX

AS
BEGIN

DECLARE @DMSDOCID FLOAT --PARAMETER
DECLARE @DMSUID BIGINT
DECLARE @DMSINDOCID INT
Declare @FILENAME NVARCHAR(254)
Declare @id int

SET @DMSDOCID= (SELECT UID_COUNTER FROM OAEFILE_UID_MAIN WHERE UID_TYPE ='DMSDOCID' AND APPID='1')
SET @DMSUID= (SELECT UID_COUNTER FROM OAEFILE_UID_MAIN WHERE UID_TYPE ='DOCNO' AND APPID='1' AND CABINETID='1')
SET @DMSINDOCID = ( SELECT DOC_COUNTER FROM OAEFILE_CABINET WHERE APPID='1' AND CABINETID='8')
Set @ID = (Select min (ID ) from Efax.dbo.incomingefax where ISArchived =0 )
SET @filename = (select filename from efax.dbo.incomingEfax where id=@id)

print CONVERT(VARCHAR,@DMSDOCID)
PRINT convert(VARCHAR,@DMSUID)
PRINT CONVERT(VARCHAR,@DMSINDOCID)

--
if ((Select count (ISarchived) from efax.dbo.incomingEfax where isarchived =0) > 0 )
begin

if ((SELECT COUNT(LOCK_TABLENAME) FROM OAEFILE_LOCKTABLES WHERE LOCK_TABLENAME ='OAEFILE_FAILURE_SYS_DOCNOS' AND CONVERT(varchar, TRANS_DATE, 102) < CONVERT(varchar, GETDATE(), 102)) = 0)
and
((Select COUNT(LOCK_TABLENAME) FROM OAEFILE_LOCKTABLES WHERE LOCK_TABLENAME='OAEFILE_FAILURE_SYS_DOCNOS')=0)
and ((Select COUNT(LOCK_TABLENAME) FROM OAEFILE_LOCKTABLES WHERE LOCK_TABLENAME='OAEFILE_UID_MAIN')=0)

begin

BEGIN tRAN

INSERT INTO OAEFILE_LOCKTABLES(LOCK_TABLENAME,TRANS_DATE) VALUES ('OAEFILE_FAILURE_SYS_DOCNOS',Getdate())

INSERT INTO OAEFILE_LOCKTABLES(LOCK_TABLENAME,TRANS_DATE) VALUES ('OAEFILE_CABINET',Getdate())
UPDATE OAEFILE_CABINET SET DOC_COUNTER=(@DMSINDOCID+1) WHERE APPID = '1' AND CABINETID = '8'
Delete from OAEFILE_LOCKTABLES WHERE LOCK_TABLENAME ='OAEFILE_CABINET'

INSERT INTO OAEFILE_LOCKTABLES(LOCK_TABLENAME,TRANS_DATE) VALUES ('OAEFILE_UID_MAIN',GetDate())
UPDATE OAEFILE_UID_MAIN SET UID_COUNTER=(@DMSDOCID+1)WHERE UID_TYPE ='DMSDOCID' AND APPID='1'
UPDATE OAEFILE_UID_MAIN SET UID_COUNTER=(@DMSUID+1) WHERE UID_TYPE ='DOCNO' AND APPID='1' AND CABINETID='1'
Delete from OAEFILE_LOCKTABLES WHERE LOCK_TABLENAME ='OAEFILE_UID_MAIN'

INSERT INTO FAX_Main(DOCNO,INDOCNO,DOCDATE,DOCDATE_HJ,YEAR,TR_DUE_DATE,TR_DUE_DATE_HJ,ASSIGNTO_RECORDID,REMARKS_AR,NUM_FIELD7,NUM_FIELD8,CHAR_FIELD8_AR,CHAR_FIELD9_AR,CHAR_FIELD1,CHAR_FIELD9,FORWARD_DATE,CHAR_FIELD1_AR,CONFID_ID,CLOSE_DATE,ASSGNCNTCT_ID,SUBJECT,DOC_ORIGN_SOURCE,STATUS_ID,PRIORITY_NO,USER_ID,IP,ISHAAR_YEAR,CHAR_FIELD3_AR,CAT_ID,SUBCAT_ID,CHAR_FIELD6_AR,CHAR_FIELD7_AR,FORM_FLAG) VALUES (@DMSUID, @DMSINDOCID, GETDATE(), '', '', '', '', '', NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL,NULL,'test-FAX','1','1','1','2','E-FAX_IP','92083','92084','92085','92086','92087','92088','304#305#306#TEXT92087')
print 'fax_main inserted'

insert into FAX_ACTION_HST(DOCNO, SITE_ID, REMARKS, REMARKS_AR, USER_ID, SYSTEM_ID, IP, TRANS_DATE, TRANS_DATE_HJ, TRANS_TIME, FORM_ID, ACTION_ID) values (@DMSUID, 1, 'Create New E-FAX', GETDATE(), 'E-FAX', 'FAX_SERVER', 'FAX_SERVER', GETDATE(), '', '', 'FORM304', 3)
Print 'fax_action_hst inserted'

INSERT INTO FAX_ATTACH(DOCNO,ATTACH_DOCNO,ATT_DATE_HJ,REMARKS_AR,ATTACH_TYPE,ACCESSKEY) VALUES (@DMSUID,@DMSDOCID,'17/11/2008',NULL,'52','3')
print 'fax_attach inserted'

insert into fax_doc(dms_docno,dms_docdata) select @DMSDOCID,BinaryData from Efax.dbo.incomingefax where ID=@ID and isarchived=0
print 'fax_main inserted'
INSERT INTO DMSDOCINFO(APPID,DOCID,DOCNAME,DATASIZE,LOADMETHOD,REMOTEIP,USERNAME,DOCCONTENT) VALUES ('8',@DMSDOCID,@filename,'270033','1','128.1.200.41','2','Application/tif')
print 'dms docinfo inserted'

DELETE FROM OAEFILE_LOCKTABLES WHERE LOCK_TABLENAME ='OAEFILE_FAILURE_SYS_DOCNOS'
print 'OAEFILE_FAILURE_SYS_DOCNOS deleted'

COMMIT TRAN

IF @@TRANCOUNT = 0
begin
update efax.dbo.incomingefax
Set isarchived=1
where id=@id
print 'updated'
end
Else print 'not updated'
End
End
END

first it happened when i forgot to insert the commit tran, after that it happened once and i couldn't replicate the error i tried many possibilities but once it happened it freezez the database and i should clear the active connections manualy to get back to normal.
any suggestions thanks much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-23 : 12:23:44

where are you checking for errors? you need a check like
IF @@ERROR >0
ROLLBACK TRAN ...

ann06
Posting Yak Master

171 Posts

Posted - 2008-11-24 : 08:36:44

thanks the articles were helpfull

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 08:39:10

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources