>Version SQL Server 2000
>
>I apogize for the long post here, but this is a stored procedure that we call from our VFP application. Yesterday, a client said this report was returning the right number of records, but the wrong data in two of the fields, the Surgeon Name and Primary Procedure field. I logged into their system using Remote Desktop and ran the application, and sure enough, it was wrong, not only was it wrong, but to return 20 records it took over 2 minutes. Everything else is running correctly. So the first thing I did was back up their database and pulled it back to our shop. I restored it here expecting to see the same thing. But it ran perfect here and returned in 1 second. So I thought, well, when I backed it up and restored it, it must have cured any index problems. So last night I backed up their system again, dropped the database and restored it on their system, but still had the same result. I then copied the code (while logged into their system) and ran stored procedure code in the
>QA, it produced the same wrong results. So I ran the first part of the stored procedure seperate (the query that loads the cursor) and it returned the correct results. So somehow on their system, it is returning the wrong data. The even stranger part is the two fields that are incorrect I do not modify while manuiplating the cursor. I ran DBCC dbCheck and it didn't show anything being wrong.
>
>I'm hoping that someone might have some clues where I should start looking.
>
>Thanks for any help
>
>Kirk Kelly
>
>
>
>CREATE PROCEDURE ct_Get_CaseAnalysis3
>
>@tiDeptID int, @ltQueryFrom datetime, @ltQueryTo DateTime
>
>as
>
>declare @include bit
>set @include=1
>
>declare @Results TABLE (meetingnumber int,casedate varchar(12),mpinumber varchar(25),
> PatientName varchar(50), PrimarySurgeon varchar(50), PrimaryProcedure varChar(100),
> RoomName varchar(50), ScheduledStart DateTime, EnterOR dateTime,ExitOR datetime,
> PRocStart DateTime, ProcEnd DateTime, RoomOpens int, RoomClosed int, WheelsTimeUsedMins int,
> caseDelayMins int, TurnMinutes int, RoomOpensChar varchar(20), RoomCloseChar varChar(20),firstCase bit,
> IncludeInRpt bit, SurgeonInRoom DateTime)
>
>
>declare xCases cursor LOCAL READ_ONLY for
>SELECT top 100 percent dbo.meetings.meetingnumber,
> convert(varchar(12),dbo.meetings.Begintime,101) as CaseDate,
> dbo.patients.mpinumber,
> RTRIM(dbo.patients.lastname) + ', ' + RTRIM(dbo.patients.firstname) AS PatientName,
> (SELECT top 1 RTRIM(dbo.coper.lastname) + ', ' + RTRIM(dbo.coper.firstname) AS surgeonName
> FROM dbo.meetings m INNER JOIN
> dbo.meetproc ON m.meetingnumber = dbo.meetproc.meetingnumber INNER JOIN
> dbo.coper ON dbo.meetproc.coperid = dbo.coper.coperid
> where m.meetingnumber=meetings.meetingnumber
> order by meetproc.sysorder) as PrimarySurgeon,
> (SELECT top 1 Description
> FROM dbo.meetings m INNER JOIN
> dbo.meetproc ON m.meetingnumber = dbo.meetproc.meetingnumber
> where m.meetingnumber=meetings.meetingnumber
> order by meetproc.sysorder) as PrimaryProcedure,
> dbo.corooms.roomname,
> dbo.meetings.begintime as ScheduledStart,
> dbo.meetings.enteror,
> dbo.meetings.exitor,
> dbo.meetings.procstart,
> dbo.meetings.procend,
> dbo.coRooms.DeptDisplayOrder,
> dbo.coRooms.SchedFrom as RoomOpens,
> dbo.coRooms.SchedTo as RoomClosed,
> datediff(mi,enteror,exitor) as WheelsTimeUsedMins,
> datediff(mi,BeginTime,SurgeonInRoom) as CaseDelayMins,
> 0 as turnMinutes,
> ' ' as RoomOpensChar,
> ' ' as RoomCloseChar,
> dbo.meetings.SurgeonInRoom
>FROM dbo.meetings INNER JOIN
> dbo.patients ON dbo.meetings.patientid = dbo.patients.patientid INNER JOIN
> dbo.corooms ON dbo.meetings.ActualRoomid = dbo.corooms.roomid
>where iscompleted=1
>and meetings.DeptID=@tiDeptID
>and meetings.iscancelled=0
>and meetings.isblock=0
>and meetings.begintime between @ltQueryFrom and @ltQueryTo
>and meetings.RoomClosed=0
>and meetings.ERCase=0
>AND datepart(dw,Meetings.BeginTime) between 2 and 6
>order by convert(varchar(12),EnterOR,101),DeptDisplayOrder,EnterOR
>
>
>declare @meetingnumber int,
>@casedate varchar(12),@mpinumber varchar(25),
>@PatientName varchar(50), @PrimarySurgeon varchar(50), @PrimaryProcedure varChar(100),
>@RoomName varchar(50), @ScheduledStart DateTime, @EnterOR dateTime,@ExitOR datetime,
>@PRocStart DateTime, @ProcEnd DateTime, @DeptDisplayOrder int,@RoomOpens int, @RoomClosed int, @WheelsTimeUsedMins int,
>@caseDelayMins int, @TurnMinutes int, @RoomOpensChar varchar(20), @RoomCloseChar varChar(20),@SurgeonInRoom DateTime
>
>declare @tiCases Int
>
>declare @tcLastRoom varchar(50)
>declare @tcLastDate varchar(10)
>declare @tcLastExit datetime
>declare @tiTurnMin int
>set @tiTurnMin=0
>set @tcLastDate='XXXXXXXXXX'
>set @tcLastRoom='XXXXXX'
>
>
>
>declare @tiCtr int
>declare @FirstRecord bit
>declare @RoomRow int
>set @RoomRow=1
>set @FirstRecord=1
>set @tiCtr=1
>set nocount on
>Open xCases
>set @ticases=@@cursor_rows
>
>
>while (@tiCtr <= @tiCases)
>Begin
> fetch xCases into @meetingnumber,@casedate,@mpinumber,@PatientName, @PrimarySurgeon, @PrimaryProcedure,
> @RoomName,@ScheduledStart,@EnterOR,@ExitOR,@PRocStart,@ProcEnd,@DeptDisplayOrder,@RoomOpens,@RoomClosed,
> @WheelsTimeUsedMins,@caseDelayMins,@TurnMinutes,@RoomOpensChar,@RoomCloseChar,@SurgeonInRoom
>
>
> set @tiCtr = @tiCtr+1
> if (@firstRecord=1)
> begin
> set @FirstRecord = 0
> insert into @Results
> (meetingnumber,casedate,mpinumber,PatientName, PrimarySurgeon, PrimaryProcedure,
> RoomName, ScheduledStart,EnterOR,ExitOR,ProcStart,ProcEnd,RoomOpens, RoomClosed,WheelsTimeUsedMins,
> caseDelayMins, TurnMinutes, RoomOpensChar, RoomCloseChar,firstCase,IncludeInRpt,SurgeonInRoom) values
> (@meetingnumber,@casedate,@mpinumber,@PatientName, @PrimarySurgeon, @PrimaryProcedure,
> @RoomName,@ScheduledStart,@EnterOR,@ExitOR,@PRocStart,@ProcEnd,@RoomOpens,@RoomClosed,
> @WheelsTimeUsedMins,@caseDelayMins,@TurnMinutes,@RoomOpensChar,@RoomCloseChar,1,1,@SurgeonInRoom)
> set @tcLastDate=@CaseDate
> set @tcLastRoom=@RoomName
> set @tcLastExit=@ExitOR
> end
> else
> if (@CaseDate<>@tcLastDate)
> begin
> insert into @Results
> (meetingnumber,casedate,mpinumber,PatientName, PrimarySurgeon, PrimaryProcedure,
> RoomName, ScheduledStart,EnterOR,ExitOR,ProcStart,ProcEnd,RoomOpens, RoomClosed,WheelsTimeUsedMins,
> caseDelayMins, TurnMinutes, RoomOpensChar, RoomCloseChar,firstCase,IncludeInRpt,SurgeonInRoom) values
> (@meetingnumber,@casedate,@mpinumber,@PatientName, @PrimarySurgeon, @PrimaryProcedure,
> @RoomName,@ScheduledStart,@EnterOR,@ExitOR,@PRocStart,@ProcEnd,@RoomOpens,@RoomClosed,
> @WheelsTimeUsedMins,@caseDelayMins,@TurnMinutes,@RoomOpensChar,@RoomCloseChar,1,1,@SurgeonInRoom)
> set @tcLastDate=@CaseDate
> set @tcLastRoom=@RoomName
> set @tcLastExit=@ExitOR
> end
> else
> if (@RoomName<>@tcLastRoom)
> begin
> insert into @Results
> (meetingnumber,casedate,mpinumber,PatientName, PrimarySurgeon, PrimaryProcedure,
> RoomName, ScheduledStart,EnterOR,ExitOR,ProcStart,ProcEnd,RoomOpens, RoomClosed,WheelsTimeUsedMins,
> caseDelayMins, TurnMinutes, RoomOpensChar, RoomCloseChar,firstCase,IncludeInRpt,SurgeonInRoom) values
> (@meetingnumber,@casedate,@mpinumber,@PatientName, @PrimarySurgeon, @PrimaryProcedure,
> @RoomName,@ScheduledStart,@EnterOR,@ExitOR,@PRocStart,@ProcEnd,@RoomOpens,@RoomClosed,
> @WheelsTimeUsedMins,@caseDelayMins,@TurnMinutes,@RoomOpensChar,@RoomCloseChar,1,1,@SurgeonInRoom)
> set @tcLastDate=@CaseDate
> set @tcLastRoom=@RoomName
> set @tcLastExit=@ExitOR
> end
> else
> begin
> set @tiTurnMin=DateDiff(mi,@tcLastExit,@EnterOR)
> insert into @Results
> (meetingnumber,casedate,mpinumber,PatientName, PrimarySurgeon, PrimaryProcedure,
> RoomName, ScheduledStart,EnterOR,ExitOR,ProcStart,ProcEnd,RoomOpens, RoomClosed,WheelsTimeUsedMins,
> caseDelayMins, TurnMinutes, RoomOpensChar, RoomCloseChar,firstCase,IncludeInRpt,SurgeonInRoom) values
> (@meetingnumber,@casedate,@mpinumber,@PatientName, @PrimarySurgeon, @PrimaryProcedure,
> @RoomName,@ScheduledStart,@EnterOR,@ExitOR,@PRocStart,@ProcEnd,@RoomOpens,@RoomClosed,
> @WheelsTimeUsedMins,@caseDelayMins,@tiTurnMin,@RoomOpensChar,@RoomCloseChar,0,1,@SurgeonInRoom)
> set @tcLastDate=@CaseDate
> set @tcLastRoom=@RoomName
> set @tcLastExit=@ExitOR
> end
>end
>
>select * from @Results
>
>
>Close xcases
>DeAllocate xCases
>GO
>
Kirk,
If the same query is returning different results, the first thing is to look for is differences between the two servers of SET ANSI_NULLS, SET ANSI_PADDING, and SET CONCAT_NULL_YIELDS_NULL in the restored database and also in temp_db. If there are no differences and you are using TOP/ORDER BY, the next thing to check is the coallation.
For the performance issue, there's nothing to be done except to pull apart the stored procedure and check the execution plan of each query.
