usp_save_user_buypointitems_ncash

[Syek]

hi, somebody could help me with this problem?

the problem is the usual error 0 when buying things from item mall

Here usp_Save_User_BuyPointItems_NCash:

 Spoiler

USE [PS_GameData]
GO
/****** Object: StoredProcedure [dbo].[usp_Save_User_BuyPointItems_NCash] Script Date: 06/22/2013 18:36:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/****** Shoping Mall Fixed by sandolkakos Website: ******/



ALTER Proc [dbo].[usp_Save_User_BuyPointItems_NCash]

@UserUID int,
@CharID int,
@UsePoint int,
@ProductCode varchar(20),
@textDate varchar(30)

AS

DECLARE @UseTime datetime
set @UseTime=CONVERT(datetime, @textDate, 120)

SET NOCOUNT ON
SET XACT_ABORT ON

DECLARE @UseType int
DECLARE @ReturnValue int

SET @UseType = 1 -- 掘衙

BEGIN DISTRIBUTED TRANSACTION


EXEC @ReturnValue = PS_UserData.dbo.usp_Update_UserPoint @UserUID, @UsePoint
IF ( @ReturnValue < 0 )
BEGIN
GOTO ERROR
END
-- ?檣? 離馬(Old, 夥煎 UPDATE僥 陳塒唳辦 醴憮?纂陛 號檜 橾橫陴)
--UPDATE PS_UserData.dbo.Users_Master SET Point=Point-@UsePoint WHERE UserUID=@UserUID
--IF( @@ERROR<>0 OR @@ROWCOUNT=0)
--BEGIN
-- GOTO ERROR
--END

-- ?檣? 煎斜 晦煙
INSERT INTO PointLog(UseType,UserUID,CharID,UsePoint,ProductCo de,UseDate)
VALUES(@UseType,@UserUID,@CharID,@UsePoint,@Produc tCode,@textDate)
IF( @@ERROR<>0)
BEGIN
GOTO ERROR
END

COMMIT TRAN
RETURN 1

ERROR:
ROLLBACK TRAN
RETURN -1


SET XACT_ABORT OFF
SET NOCOUNT OFF

Here the logs:

PS_DBAGENT__system log start

 Spoiler

2013-06-22 19:16:10 PS_DBAGENT__system log start (DBAgent01)

2013-06-22 19:16:17 connect game, Name=



2013-06-22 19:16:17 connect game, Name=



2013-06-22 19:17:46 err=-1, [Microsoft][ODBC SQL Server Driver][SQL Server]La conversión del tipo de datos varchar en datetime produjo un valor fuera de intervalo., SQL STATE: 22007, NATIVE ERROR: 242 (0xF2)

2013-06-22 19:17:46 SaveBuyPointItem 7 Leon qerr=0, {?=call usp_Save_User_BuyPointItems_NCash(1,7,0,'B1_Cface0 001','2013-06-22 19:17:46')}

PS_GAMELOG__system log start

 Spoiler

2013-06-22 19:16:08 PS_GAMELOG__system log start (GameLog01)

2013-06-22 19:16:17 connect game

2013-06-22 19:16:58 err=-1, [Microsoft][ODBC SQL Server Driver][SQL Server]Error al convertir el tipo de datos varchar a datetime., SQL STATE: 42000, NATIVE ERROR: 8114 (0x1FB2)

2013-06-22 19:16:58 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'admin1',1,7,'Leon', 60,1641399100,0, 1917.949585,14.799993,377.186096, '2013-06-22 19:16:57' ,107, 255,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, '25.197.212.94','money=322097683, bank=0, cash=0','level=60,skp=396,stp=0','s=10,d=15,i=9,w= 10,r=9,l=12'

2013-06-22 19:16:58 err=-1, [Microsoft][ODBC SQL Server Driver][SQL Server]Error al convertir el tipo de datos varchar a datetime., SQL STATE: 42000, NATIVE ERROR: 8114 (0x1FB2)

2013-06-22 19:16:58 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'admin1',1,7,'Leon', 60,1641399100,0, 1917.949585,14.799993,377.186096, '2013-06-22 19:16:57' ,232, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 'Enter',NULL,NULL,NULL

2013-06-22 19:17:34 err=-1, [Microsoft][ODBC SQL Server Driver][SQL Server]Error al convertir el tipo de datos varchar a datetime., SQL STATE: 42000, NATIVE ERROR: 8114 (0x1FB2)

2013-06-22 19:17:34 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'admin1',1,7,'Leon', 0,0,0, 1917.949585,14.799993,377.186096, '2013-06-22 19:17:34',180, 1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 'VisibleOn',NULL,NULL,NULL

2013-06-22 19:17:46 err=-1, [Microsoft][ODBC SQL Server Driver][SQL Server]Error al convertir el tipo de datos varchar a datetime., SQL STATE: 42000, NATIVE ERROR: 8114 (0x1FB2)

2013-06-22 19:17:46 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'admin1',1,7,'Leon', 60,1641399100,0, 1917.949585,14.799993,377.186096, '2013-06-22 19:17:46' ,113, 3867805302032236545,100070,NULL,1,NULL,NULL,NULL,N ULL,NULL,NULL,'Appearance Change Scroll','PointItem','0,0,0,0,0,0 (Option','2013-06-22 19:17:46'

2013-06-22 19:17:46 err=-1, [Microsoft][ODBC SQL Server Driver][SQL Server]Error al convertir el tipo de datos varchar a datetime., SQL STATE: 42000, NATIVE ERROR: 8114 (0x1FB2)

2013-06-22 19:17:46 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'admin1',1,7,'Leon', 60,1641399100,0, 1917.949585,14.799993,377.186096, '2013-06-22 19:17:46' ,108, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL, 'money=322097683, bank=1952514048, cash=0, firstmoney=322097683','level=60,skp=396,stp=0','s= 10,d=15,i=9,w=10,r=9,l=12'

2013-06-22 19:17:46 err=-1, [Microsoft][ODBC SQL Server Driver][SQL Server]Error al convertir el tipo de datos varchar a datetime., SQL STATE: 42000, NATIVE ERROR: 8114 (0x1FB2)

2013-06-22 19:17:46 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'admin1',1,7,'Leon', 60,1641399100,0, 1917.949585,14.799993,377.186096, '2013-06-22 19:17:46' ,232, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 'Leave',NULL,NULL,NULL

If I'm missing some logs or dbo please tell me

[castor4878]

PS_DBAGENT__system.log indicates what kind of error occurs:

[SQL Server]La conversión del tipo de datos varchar en datetime produjo un valor fuera de intervalo., SQL STATE: 22007, NATIVE ERROR: 242 (0xF2)

and where the error occurs, we also got all transmitted parameters:

2013-06-22 19:17:46 SaveBuyPointItem 7 Leon qerr=0, {?=call usp_Save_User_BuyPointItems_NCash(1,7,0,'B1_Cface0 001', '2013-06-22 19:17:46')}

so, yet another, date conversion error ....

your stored procedure is defined as:

ALTER Proc [dbo].[usp_Save_User_BuyPointItems_NCash]

@UserUID int,
@CharID int,
@UsePoint int,
@ProductCode varchar(20),
@textDate varchar(30)

AS

DECLARE @UseTime datetime
set @UseTime=CONVERT(datetime, @textDate, 120)

so far so good, it receives:
1 as an int stored in @UserUID
7 as an int stored in @CharID
0 as an int stored in @UsePoint
'B1_Cface0 001' as varchar(20) (it is 13 char long) stored in @ProductCode
'2013-06-22 19:17:46' as varchar(30) (it is 29 char long) stored in @textDate

then you convert that "ODBC canonical" date into a datetime field using the convert function with the right parameter (120 means it is "ODBC canonical").

*w/o surptise* the log doesn't contain something like, "invalid argument for convert function", nor "unknown convert function", nor "invalid argument for assignment / invalid value for @UseTime field" or whatever close to that.

the error is so in the code present after.

we have:

PS_UserData.dbo.usp_Update_UserPoint @UserUID, @UsePoint

that does not use date (and the log does contain a date related error).

so last statement:

INSERT INTO PointLog(UseType,UserUID,CharID,UsePoint,ProductCo de,UseDate)
VALUES(@UseType,@UserUID,@CharID,@UsePoint,@Produc tCode,@textDate)

where we need about 3 seconds to figure out that the string-field-containing-an-OBDC-string-date-that-the-server-can-NOT-handle-by-itself parameter (the "textDate" variable) is used instead of the @UseTime variable, the one we introduced to handle that error ...

[Syek]

thanks thanks thanks you are awesome thanks for all the help more than says the errors, explaining to us how to fix them and how to understand why they happen

long live to you

[AxelMac]

Originally Posted by castor4878

PS_DBAGENT__system.log indicates what kind of error occurs:

[SQL Server]La conversión del tipo de datos varchar en datetime produjo un valor fuera de intervalo., SQL STATE: 22007, NATIVE ERROR: 242 (0xF2)

and where the error occurs, we also got all transmitted parameters:

2013-06-22 19:17:46 SaveBuyPointItem 7 Leon qerr=0, {?=call usp_Save_User_BuyPointItems_NCash(1,7,0,'B1_Cface0 001', '2013-06-22 19:17:46')}

so, yet another, date conversion error ....

your stored procedure is defined as:

ALTER Proc [dbo].[usp_Save_User_BuyPointItems_NCash]

@UserUID int,
@CharID int,
@UsePoint int,
@ProductCode varchar(20),
@textDate varchar(30)

AS

DECLARE @UseTime datetime
set @UseTime=CONVERT(datetime, @textDate, 120)

so far so good, it receives:
1 as an int stored in @UserUID
7 as an int stored in @CharID
0 as an int stored in @UsePoint
'B1_Cface0 001' as varchar(20) (it is 13 char long) stored in @ProductCode
'2013-06-22 19:17:46' as varchar(30) (it is 29 char long) stored in @textDate

then you convert that "ODBC canonical" date into a datetime field using the convert function with the right parameter (120 means it is "ODBC canonical").

*w/o surptise* the log doesn't contain something like, "invalid argument for convert function", nor "unknown convert function", nor "invalid argument for assignment / invalid value for @UseTime field" or whatever close to that.

the error is so in the code present after.

we have:

PS_UserData.dbo.usp_Update_UserPoint @UserUID, @UsePoint

that does not use date (and the log does contain a date related error).

so last statement:

INSERT INTO PointLog(UseType,UserUID,CharID,UsePoint,ProductCo de,UseDate)
VALUES(@UseType,@UserUID,@CharID,@UsePoint,@Produc tCode,@textDate)

where we need about 3 seconds to figure out that the string-field-containing-an-OBDC-string-date-that-the-server-can-NOT-handle-by-itself parameter (the "textDate" variable) is used instead of the @UseTime variable, the one we introduced to handle that error ...

you can post the complet query? pls

[Hep]

#Wrong Section btw. Thank you castor , helped me too .

[AxelMac]

what changes should I make?