sql server 2005 transaction problem(sql server experts this question for you :))

[alenak]

Hi Everybody,

I have a web application that makes large number of insertions and updates in DB. In this application I send a sql query which is sql transaction shown below. The problem is that SOMETIMES one of tables naming 'AimD' has no record although table 'AimB' has a record.But according to transaction it must HAVE. A very important consideration is that the table 'AimD' is getting 1 million records everyday and approximately 1 to 7 records leak in this way for every 1 million records.
In your opinion what causes this transaction break down? and why does sql server not make rollback?

SET XACT_ABORT ON BEGIN TRY BEGIN TRANSACTION declare @BId INT,@manager varchar(8),@saha varchar(8),@personx varchar(16),@operation varchar(8)

if not exists(select Id from AimB where CariKod = 'L1520310009' and AyNo = 1)
Begin
insert AimB (CariKod,AyNo,KulId) values ('L1520310009',1,724)
select @BId = IDENT_CURRENT ('AimB')
select @manager = manager,@saha = Saha,@personx = personx,@operation = operation from ChangeDimentionCari with (nolock) where SubeKodu = 'L1520310009'
insert AimKodlar (BId,Donem,managerKodu,SahaKodu,personxKodu,operat ionKodu) values (@BId,1,@manager,@saha,@personx,@operation)
insert AimD (BId,StokKodu, Aim,ListPrice) values ( @BId,'01100110005',0,dbo.[fn_ListPrice]('L1520310009','01100110005'))
-- .......
--There are about 270 insertions into AimD table
End

COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH


thanks for your replies in advance.

[alenak]

the problem has been solved