Here's the code that is causing the error.
Create PROCEDURE [dbo].[usp_GetGroupList]
@pn_CompanyNum smallint,
@pn_DepartmentNum smallint,
@ps_Debug char(1)
AS
begin
set NOCount on
-- Return all groups for Company and Department
select GroupNum, GroupName
from dbo.Groups with (readpast)
where DepartmentNum = @pn_DepartmentNum
and CompanyNum = @pn_CompanyNum
end
GO
We are running in an SOA environment, so the transactions are controlled by the service not the database. The Service also performs the commits and rollbacks. The above procedure runs fine until a commit is performed. When a commit is performed by the service, it looks like the isolation level gets changed. If I put a commit in the procedure, then the issue goes away, but that is not what we want. The service is adding/updating/deleting a number of records before a commit is done and it's all or nothing, so we cannot commit within the proc. Here's the proc for one of the inserts as well.
Create Procedure [dbo].[Usp_AddGroups]
(@pn_CompanyNum smallint,
@pn_DepartmentNum smallint,
@pn_GroupNum smallint,
@ps_GroupName varchar(30),
@ps_CurrentSeason char(3),
@pn_CurrentGroupDestination smallint,
@ps_CurrentRollToFlag char(1),
@ps_NextSeason char(3),
@pn_NextGroupDestination smallint,
@ps_NextRollToFlag char(1),
@ps_ThirdSeason char(3),
@pn_ThirdGroupDestination smallint,
@ps_ThirdRollToFlag char(1),
@ps_MismatchFlag char(1),
@ps_CorpMatrixFlag char(1),
@ps_Userid varchar(30),
@pn_RtnCode int output,
@ps_ErrorMessage varchar(200) output)
AS
BEGIN
declare @UpdtDate datetime;
set @UpdtDate = cast(GetDate() as datetime);
begin try
-- Insert records into Groups table first
Insert into dbo.Groups with(rowlock)
(CompanyNum, DepartmentNum, GroupNum, GroupName, CorpMatrixFlag,
CreateUser, CreateDate, ModifiedUser, ModifiedDate)
Values(@pn_CompanyNum, @pn_DepartmentNum, @pn_GroupNum, @ps_GroupName,
@ps_CorpMatrixFlag, @ps_Userid, @UpdtDate, @ps_Userid, @UpdtDate)
end try
begin catch
select @pn_RtnCode = ERROR_NUMBER(),
@ps_ErrorMessage = ERROR_MESSAGE()
Raiserror( @ps_ErrorMessage, 16, 1)
return
end catch
set @pn_RtnCode = 1
set @ps_ErrorMessage = 'Successful Add'
END
GO