Home > Software engineering >  Incorrect operation on a sql server procedure
Incorrect operation on a sql server procedure

Time:12-21

I have a problem with an online game.

In the virtual machine with Windows Server 2012R2 SQL Server 2017 CU31, the procedure works without errors.

But on my machine with Windows Server 2022 SQL Server 2019 CU18, it doesn't work.

This is the log error:

[Error] ERR(GET): SQL_ERROR >> { CALL SelectBase_proc10 ( 1, 4 ) }
[Error] SqlState:HY000, NativeError:4096 Msg:[Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream
[System] [_QueryCase_SelectChar::vComplete] (1)(2) Load Failed. Result(20)

This is the procedure:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SelectBase_proc10]            
    @Key INT,            
    @Hour int = 6     
AS
BEGIN      
    DECLARE @Date DATETIME, @LastDate DATETIME, @PegaData DATETIME          
      
    SET @Date = DATEADD(HOUR, @Hour, CONVERT(DATETIME, LEFT(CONVERT(varchar, GETDATE(), 120), 10)))            
    SET @LastDate = DATEADD(DAY, -1, @Date)   
    SET @PegaData = GETDATE()
      
    SET NOCOUNT ON            
       
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED            
      
    BEGIN TRY         
        SELECT TOP(1)            
            Name, Class, Level, Female, Race, Exp, Angle, Money,
            Integration, MapId, SpaceType, PosX, PosY, PosZ,
            Hp, Mp, Sp, Ep, Fp, Tp, Rp, Ap, Cp, Cg, 
            Fire, Ice, Light, Dark, InvenSlot, ExpirationPeriodInvenSlot,
            FellowBagSlot, BoostSlot, KeepingSlot, StartIdx,      
            ItemSerialOrder, PartyID, byIllegal, OnBoardSkillUnsealed, Comment,            
            GuildDBKey, GuildMemberType, EventInGateArea, StaticWarpArea, DynamicWarpMap,            
            DynamicWarpPosX, DynamicWarpPosY, DynamicWarpPosZ, DynamicWarpAngle, InvenPrioritize,            
            InvenBag1Prioritize, InvenBag2Prioritize, InvenBag3Prioritize, InvenBag4Prioritize,            
            SummonFellow, bMount, LastSummonFellow, byUnderware, TotalPlayTime,            
            FellowSlot, FellowSlotExp, FarmDBKey, SoulPoint, 
            bCanUseSoulTalents, CanUseSoulTalentsAwaken, 
            AchievementPoint, TitleRecID,            
            CASE 
                WHEN LastAccessDate <= @LastDate 
                     OR (LastAccessDate <= @Date AND @PegaData >= @Date) 
                    THEN 1 
                    ELSE 0 
            END bReset,         
            bMilitiaType, CreateTime, MentoringPenaltyTime, MentoringGraduationCount   
        FROM 
            Table_CharBase WITH(nolock) 
        WHERE 
            DBKey = @Key AND Deleted = 0         
    END TRY            
    BEGIN CATCH            
        IF @@TRANCOUNT > 0 
           ROLLBACK            
      
        EXEC dbo.common_RaiseError;            
    END CATCH      
END

Where is the error?

I haven't tried to fix it yet because I'm new to database

CodePudding user response:

I managed to find out what and not and error in the procedure, this error appears when I install the sql server on the ssd. When installing on hd the error disappeared.

  • Related