Hi,
I created a stored procedure which loads several tables from a linked database in temporary tables, joins them, filters them, formats columns and finally dumps the result in a new temporary table. Then, the records from the resulting temporary table is saved to a physical table and the stored procedure should return the records that were written to the physical table.
I wrapped everything in a transaction to make sure that nothing would be written to the physical table is something goes wrong.
In order to verify/follow what is going on I added some log messages that are written to another physical table (my log table)
here is the code:
USE [MyDatabase];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [uspMyStoredProc]
WITH EXEC AS CALLER
AS
BEGIN
--SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET XACT_ABORT, NOCOUNT ON;
Declare @ProcName nvarchar(100);
Declare @Guid uniqueidentifier;
SET @ProcName = OBJECT_NAME(@@PROCID);
Set @Guid = NewID();
--Log begin of stored procedure
--------------------------------
exec uspLogAction @ProcName, 'Stored procedure started', null, null, 3, @Guid;
BEGIN TRY
...
BEGIN TRANSACTION;
--Log begin of transaction
----------------------------
exec uspLogAction @ProcName, 'TRANSACTION started', null, null, 3, @Guid;
...
-- Loading staff in temporary table
...
...
-- At this stage, I have a table #Temp
--Save result to physical table and let SQL assign the ID (incremental)
-----------------------------------------------------------------------------
insert into tblPhysicalTable (sdProcessDateTime, Processtype, ClientNumber,ClientName)
Select * from #Temp;
----------------------------------------------------------------------
-- Reload records written to tblPhysicalTable so that I can get the ID
INSERT into #Return
Select intID, sdProcessDateTime, ProcessType, ClientNumber, ClientName
from tblPhysicalTable WITH (NOLOCK) -- With(NoLock) is mandatory since my transaction hasn't been committed yet !!!
where sdProcessDateTime = @Now;
--Log that the transaction is about to be committed
exec uspLogAction @ProcName, 'TRANSACTION about to be committed', null, null, 3, @Guid;
COMMIT TRANSACTION;
--Now return changes
Select @MyMessage=Convert(varchar, count(*)) + ' Records found' from #Return
exec uspLogAction @ProcName, @MyMessage , null, null, 3, @Guid;
--This will return the needed records to BizTalk
Select * From #Return;
Drop table #Return;
exec uspLogAction @ProcName, 'Stored procedure complete', null, null, 3, @Guid;
END TRY
BEGIN CATCH
DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorLine INT = ERROR_LINE();
DECLARE @ErrorMsg nvarchar(MAX) = ERROR_MESSAGE();
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
--Log the rollback
EXEC uspLogAction @ProcName, @ErrorMsg , @ErrorNumber , @ErrorLine , 1, @Guid; -- I cannot log the error before the rollback otherwise the log is also rolled back
EXEC uspLogAction @ProcName, 'Transaction has been rolled back', null, null, 2, @Guid;
THROW;
END CATCH
END
So when all goes well, I have 5 messages logged:
'Stored procedure started'
'Transaction started'
'Transaction about to be committed'
'# records found'
'Stored procedure complete'
In 95% of calls, I do get the 5 above messages but in some cases, I only have the 3 first log messages (and less often, I got the 4 first logs.)
Occasionally, (when the linked server is not available or in case of deadlock), I do get errors logged:
'Stored procedure started'
'Error message'
'Transaction has been rolled back'
So it seems that all works fine but I cannot explain what can go wrong in my code between 'Commit Transaction' and End Try.
Even if an error would occur after the transaction is committed, the error should be trapped and I would see it in the logs.
(note that I removed a lot of the code that I believe is not relevant but I didn't remove anything between the end transaction and end try because that is where the issue occurs.)
Can anybody point me to the right direction?
Thank you