TJMike


We have an application that accesses a SQL Server 2005 database. The isolation level is set to Read Committed. When the app calls a procedure to insert,update, or delete it seems to change the isolation level after the commit. After the commit, it calls another proc to fill a drop down and it gets the following message

"You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels"

The proc that fills the drop down isn't even using a with readpast command, but it is being used in some of the procs used prior to the commit.

Does anyone have any ideas




Re: with Readpast changing isloation level

John Gordon - MSFT


Moving this one to T-SQL forums, where your question might obtain the expertise it requires.

Thanks,

John







Re: with Readpast changing isloation level

Redbaron71

Hi TJMike,

It is probably still having the same SPID. Have you tried running a profiler on it and debugging it

You could also try one thing. Just before the call to the proc that populates the drop dopwn list, youcan try a disconnect so that the spid changes. There fore resetting all Hints or Sets. Then connect again and call the proc.

The Mods will surely know the reason but this is just a shot in the dark.






Re: with Readpast changing isloation level

NorthwindDBA

Please post the DDL for the proc that causes the error. Do you have a queryhint in there






Re: with Readpast changing isloation level

Louis Davidson

In addition to the other responses, have you used profiler to get an idea of the calls that are actually being made, and when they are being made That might shed some light on what is happening where. This sounds like you have some timing issues, and maybe somethings are happening on different connections.




Re: with Readpast changing isloation level

TJMike

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