Need a pro in SQL

[engxexphili]

Hi,

I've some SQL error. (don't cause bug but flood of CQuery my VPS)
With the model change of Wurstbrot QT, the errors appear, and if I delete the system, error don't come anymore..

When I login on every character :

 Spoiler

query:CHARACTER_STR 'S4',@im_idPlayer='0000033',@iserverindex='01',@ia ccount='(null)',@im_szName='(null)',@iplayerslot=0 ,@idwWorldID=0,@im_dwIndex=0,@im_vPos_x=0.000000,@ im_vPos_y=0.000000,@im_vPos_z=0.000000,@im_szChara cterKey='(null)',@im_dwSkinSet=0,@im_dwHairMesh=0, @im_dwHairColor=0,@im_dwHeadMesh=0,@im_dwSex=0,@im _vScale_x=0.000000,@im_dwMotion=0,@im_fAngle=0.000 000,@im_nHitPoint=0,@im_nManaPoint=0,@im_nFatigueP oint=0,@im_dwRideItemIdx=0,@im_dwGold=0,@im_nJob=0 ,@im_pActMover='(null)',@im_nStr=0,@im_nSta=0,@im_ nDex=0,@im_nInt=0,@im_nLevel=0,@im_nExp1=0,@im_nEx p2=0,@im_aJobSkill='(null)',@im_aLicenseSkill='(nu ll)',@im_aJobLv='(null)',@im_dwExpertLv=0,@im_idMa rkingWorld=0,@im_vMarkingPos_x=0.000000,@im_vMarki ngPos_y=0.000000,@im_vMarkingPos_z=0.000000,@im_nR emainGP=0,@im_nRemainLP=0,@im_nFlightLv=0,@im_nFxp =0,@im_nTxp=0,@im_lpQuestCntArray='(null)',@im_chA uthority='F',@im_dwMode=0,@im_idparty=0,@im_idMuer derer=0,@im_nFame=0,@im_nDeathExp=0,@im_nDeathLeve l=0,@im_dwFlyTime=0,@im_nMessengerState=0,@iTotalP layTime=0,@im_Card='(null)',@im_Index_Card='(null) ',@im_ObjIndex_Card='(null)',@im_Cube='(null)',@im _Index_Cube='(null)',@im_ObjIndex_Cube='(null)',@i m_Inventory='(null)',@im_apIndex='(null)',@im_adwE quipment='(null)',@im_dwObjIndex='(null)',@im_aSlo tApplet='(null)',@im_aSlotItem='(null)',@im_aSlotQ ueue='(null)',@im_SkillBar=0,@im_Bank='(null)',@im _apIndex_Bank='(null)',@im_dwObjIndex_Bank='(null) ',@im_dwGoldBank=0,@im_nFuel=0,@im_tmAccFuel=0,@im _dwSMTime='(null)',@iSkillInfluence='(null)',@im_a CompleteQuest='(null)',@im_extInventory='(null)',@ im_InventoryPiercing='(null)',@im_extBank='(null)' ,@im_BankPiercing ='(null)',@im_dwReturnWorldID=0,@im_vReturnPos_x=0 .000000,@im_vReturnPos_y=0.000000,@im_vReturnPos_z =0.000000,@im_nPKValue=0,@im_dwPKPropensity=0,@im_ dwPKExp=0,@im_nAngelExp=0,@im_nAngelLevel=0,@iszIn ventoryPet='(null)',@iszBankPet='(null)', @im_dwPetId=0,@im_nExpLog=0, @im_nAngelExpLog=0,@im_nCoupon=0,@im_nHonor=-1,@im_nLayer=0,@im_nCampusPoint=0,@im_idCampus=0,@ im_aCheckedQuest='(null)',@iGlowBody=0,@iGlowLegLe ft=0,@iGlowLegRight=0,@iGlowArmleft=0,@iGlowArmRig ht=0,@iGlowHandLeft=0,@iGlowHandRight=0
SQLSTATE:22007 error:[Microsoft][SQL Server Native Client 10.0][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
2013/12/18 14:46:17

 Spoiler

\Program\_Database\DbManager.cpp, 1283 CHARACTER_STR 'S4',@im_idPlayer='0000033',@iserverindex='01',@ia ccount='(null)',@im_szName='(null)',@iplayerslot=0 ,@idwWorldID=0,@im_dwIndex=0,@im_vPos_x=0.000000,@ im_vPos_y=0.000000,@im_vPos_z=0.000000,@im_szChara cterKey='(null)',@im_dwSkinSet=0,@im_dwHairMesh=0, @im_dwHairColor=0,@im_dwHeadMesh=0,@im_dwSex=0,@im _vScale_x=0.000000,@im_dwMotion=0,@im_fAngle=0.000 000,@im_nHitPoint=0,@im_nManaPoint=0,@im_nFatigueP oint=0,@im_dwRideItemIdx=0,@im_dwGold=0,@im_nJob=0 ,@im_pActMover='(null)',@im_nStr=0,@im_nSta=0,@im_ nDex=0,@im_nInt=0,@im_nLevel=0,@im_nExp1=0,@im_nEx p2=0,@im_aJobSkill='(null)',@im_aLicenseSkill='(nu ll)',@im_aJobLv='(null)',@im_dwExpertLv=0,@im_idMa rkingWorld=0,@im_vMarkingPos_x=0.000000,@im_vMarki ngPos_y=0.000000,@im_vMarkingPos_z=0.000000,@im_nR emainGP=0,@im_nRemainLP=0,@im_nFlightLv=0,@im_nFxp =0,@im_nTxp=0,@im_lpQuestCntArray='(null)',@im_chA uthority='F',@im_dwMode=0,@im_idparty=0,@im_idMuer derer=0,@im_nFame=0,@im_nDeathExp=0,@im_nDeathLeve l=0,@im_dwFlyTime=0,@im_nMessengerState=0,@iTotalP layTime=0,@im_Card='(null)',@im_Index_Card='(null) ',@im_ObjIndex_Card='(null)',@im_Cube='(null)',@im _Index_Cube='(null)',@im_ObjIndex_Cube='(null)',@i m_Inventory='(null)',@im_apIndex='(null)',@im_adwE quipment='(null)',@im_dwObjIndex='(null)',@im_aSlo tApplet='(null)',@im_aSlotItem='(null)',@im_aSlotQ ueue='(null)',@im_SkillBar=0,@im_Bank='(null)',@im _apIndex_Bank='(null)',@im_dwObjIndex_Bank='(null) ',@im_dwGoldBank=0,@im_nFuel=0,@im_tmAccFuel=0,@im _dwSMTime='(null)',@iSkillInfluence='(null)',@im_a CompleteQuest='(null)',@im_extInventory='(null)',@ im_InventoryPiercing='(null)',@im_extBank='(null)' ,@im_BankPiercing ='(null)',@im_dwReturnWorldID=0,@im_vReturnPos_x=0 .000000,@im_vReturnPos_y=0.000000,@im_vReturnPos_z =0.000000,@im_nPKValue=0,@im_dwPKPropensity=0,@im_ dwPKExp=0,@im_nAngelExp=0,@im_nAngelLevel=0,@iszIn ventoryPet='(null)',@iszBankPet='(null)', @im_dwPetId=0,@im_nExpLog=0, @im_nAngelExpLog=0,@im_nCoupon=0,@im_nHonor=-1,@im_nLayer=0,@im_nCampusPoint=0,@im_idCampus=0,@ im_aCheckedQuest='(null)',@iGlowBody=0,@iGlowLegLe ft=0,@iGlowLegRight=0,@iGlowArmleft=0,@iGlowArmRig ht=0,@iGlowHandLeft=0,@iGlowHandRight=0

Function of line 1283 (DBManager) :

BOOL CDbManager::SendItemtoCharacter( int nSlot, CMover* pMover, CQuery *qry, CQuery *qry1, CQuery *qrylog, LPDB_OVERLAPPED_PLUS lpDbOverlappedPlus )
{
// CHARACTER_STR 'S4',@im_idPlayer,@iserverindex
char szQuery[QUERY_SIZE] = { 0,};
DBQryCharacter( szQuery, "S4", pMover->m_idPlayer, g_appInfo.dwSys );

if( FALSE == qry->Exec( szQuery ) )
{
WriteLog( "%s, %d\t%s", __FILE__, __LINE__, szQuery );
return FALSE;
}

BOOL bResult = FALSE;
while( qry->Fetch() )
{
__SendItemContents SendItemContents;
#if __VER >= 12 // __EXT_PIERCING
CItemElem* pItemElem = &SendItemContents.itemElem;
pItemElem->m_bCharged = TRUE;
pItemElem->SetRandomOptItemId( 0 );
#else // __EXT_PIERCING
SendItemContents.nCharged = 1;
#if __VER >= 11 // __SYS_IDENTIFY
SendItemContents.iRandomOptItemId = 0;
#else // __SYS_IDENTIFY
SendItemContents.nRandomOptItemId = 0;
#endif // __SYS_IDENTIFY
#endif // __EXT_PIERCING

if( GetSendItem( qry, &SendItemContents ) == FALSE )
{
#if __VER >= 12 // __EXT_PIERCING
WriteLog( "SendItem::GetSendItem PlayerName = %s, Item_Count = %d Qry = %s", pMover->m_szName, SendItemContents.Item_Count, szQuery );
#endif // __EXT_PIERCING
continue;
}

if( 0 == strcmp( SendItemContents.OneItem, "penya" ) ) // Æä³ÄÀÎÁö È®ÀÎ
{
// Penya Áö±Þ
if( SendPenya( qry1, szQuery, pMover, SendItemContents.Item_Count, SendItemContents.nNo ) == FALSE )
{
WriteLog( "SendItem::SendPenya PlayerName = %s, Item_Count = %d Qry = %s", pMover->m_szName, SendItemContents.Item_Count, szQuery );
continue;
}

bResult = TRUE;

#ifndef __NOLOG
LogItemInfo aLogItem;
aLogItem.SendName = pMover->m_szName;
aLogItem.RecvName = "DB";
aLogItem.Gold = pMover->GetGold() - SendItemContents.Item_Count;
aLogItem.Gold2 = pMover->GetGold();
//aLogItem.ItemName = "SEED";
_stprintf( aLogItem.szItemName, "%d", II_GOLD_SEED1 );
aLogItem.itemNumber = SendItemContents.Item_Count;
aLogItem.Action = "U";
if( ItemLogQuery( qrylog, "LC", aLogItem, pMover->m_idPlayer, SendItemContents.nNo, SendItemContents.OneItem ) == FALSE )
{
// return;
continue;
}
#endif // __NOLOG

}
else // ¾ÆÀÌÅÛ Áö±Þ
{
SERIALNUMBER iSerialNumber = SendItem( qry1, szQuery, pMover, SendItemContents );
if( iSerialNumber != 0 )
{
bResult = TRUE;
#ifndef __NOLOG
LogItemInfo aLogItem;
aLogItem.SendName = pMover->m_szName;
aLogItem.RecvName = "DB";
aLogItem.Gold = pMover->GetGold();
aLogItem.Gold2 = pMover->GetGold();
//aLogItem.ItemName = SendItemContents.OneItem;
_stprintf( aLogItem.szItemName, "%d", SendItemContents.itemElem.m_dwItemId );
aLogItem.itemNumber = SendItemContents.Item_Count;
aLogItem.ItemNo = iSerialNumber;
#if __VER >= 12 // __EXT_PIERCING
aLogItem.nAbilityOption = pItemElem->GetOption();
aLogItem.nItemResist = pItemElem->m_bItemResist;
aLogItem.nResistAbilityOption = pItemElem->m_nResistAbilityOption;
#else // __EXT_PIERCING
aLogItem.nAbilityOption = SendItemContents.m_nAbilityOption;
aLogItem.nItemResist = SendItemContents.nItemResist;
aLogItem.nResistAbilityOption = SendItemContents.nResistAbilityOption;
#endif // __EXT_PIERCING
aLogItem.Action = "U";
if( ItemLogQuery( qrylog, "LC", aLogItem, pMover->m_idPlayer, SendItemContents.nNo, SendItemContents.OneItem ) == FALSE )
{
// return;
continue;
}
#endif // __NOLOG
}
else
{
// return;
continue;
}
}


}
// else
// {
// return;
// }
// }
return bResult;
}

Everything it's okey in source, i don't understand.. Please help !!

[raventh1984]

SQLSTATE:22007 error:[Microsoft][SQL Server Native Client 10.0][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

There is your error. You are trying to convert an Varchar type to an datetime type that wont work. So you will nee to check the model change snippets to see if something is out of the order.

[engxexphili]

There is no datetime convert needed in model change.

Code in DbManagerSave :

 Spoiler

#if __VER >= 11 // __SYS_IDENTIFY
sprintf( pItemStruct->szExt, "%d,%d,%I64d"
#if __VER >= 15 // __PETVIS
",%d"
#endif // __PETVIS
#ifdef __ITEM_LEVEL
",%d"
#endif//__ITEM_LEVEL
#ifdef __CHANGE_MODEL
",%d"
#endif
"/", pItemElem->m_bCharged, pItemElem->m_dwKeepTime, pItemElem->GetRandomOptItemId()
#if __VER >= 15 // __PETVIS
, static_cast<int>( pItemElem->m_bTranformVisPet )
#endif // __PETVIS
#ifdef __ITEM_LEVEL
, pItemElem->m_dwItemLevel
#endif//__ITEM_LEVEL
#ifdef __CHANGE_MODEL
, pItemElem->m_nChangeModel
#endif
);

So I don't understand..

[xTwiLightx]

We might take a look into your CHARACTER_STR 'S4' statement.

Mine is as follows:

ELSE
IF @iGu = 'S4' -- A©¬¡Æ¢®CO ¨ú¨¡AIAU E¢çAI
	BEGIN

	declare @s4_account varchar(32), @i1_e_date datetime

		declare @q1 nvarchar(4000)

		set @q1 = '
		SELECT 	Item_Name, Item_count, m_nAbilityOption, m_nNo, m_bItemResist, m_nResistAbilityOption,
			m_bCharged, nPiercedSize, adwItemId0, adwItemId1, adwItemId2, adwItemId3, adwItemId4,
			m_dwKeepTime, nRandomOptItemId,
			isnull(adwItemId5, 0) as adwItemId5, isnull(adwItemId6, 0) as adwItemId6, isnull(adwItemId7, 0) as adwItemId7, isnull(adwItemId8, 0) as adwItemId8, isnull(adwItemId9, 0) as adwItemId9, isnull(nUMPiercedSize, 0) as nUMPiercedSize,
			isnull(adwUMItemId0, 0) as adwUMItemId0, isnull(adwUMItemId1, 0) as adwUMItemId1, isnull(adwUMItemId2, 0) as adwUMItemId2, isnull(adwUMItemId3, 0) as adwUMItemId3, isnull(adwUMItemId4, 0) as adwUMItemId4
		FROM ITEM_SEND_TBL 
		WHERE m_idPlayer = @im_idPlayer 	
			AND serverindex = @iserverindex
			AND ItemFlag = 0'
		exec sp_executesql @q1, N'@im_idPlayer char(7), @iserverindex char(2)', @im_idPlayer, @iserverindex
		RETURN
	END

Originally Posted by engxexphili

There is no datetime convert needed in model change.

Code in DbManagerSave :

 Spoiler

#if __VER >= 11 // __SYS_IDENTIFY
sprintf( pItemStruct->szExt, "%d,%d,%I64d"
#if __VER >= 15 // __PETVIS
",%d"
#endif // __PETVIS
#ifdef __ITEM_LEVEL
",%d"
#endif//__ITEM_LEVEL
#ifdef __CHANGE_MODEL
",%d"
#endif
"/", pItemElem->m_bCharged, pItemElem->m_dwKeepTime, pItemElem->GetRandomOptItemId()
#if __VER >= 15 // __PETVIS
, static_cast<int>( pItemElem->m_bTranformVisPet )
#endif // __PETVIS
#ifdef __ITEM_LEVEL
, pItemElem->m_dwItemLevel
#endif//__ITEM_LEVEL
#ifdef __CHANGE_MODEL
, pItemElem->m_nChangeModel
#endif
);

So I don't understand..

But CHARACTER_STR 'S4' uses datetime.