Register for your free account! | Forgot your password?

You last visited: Today at 15:43

  • Please register to post and access all features, it's quick, easy and FREE!

Advertisement



Varchar to datetime error

Discussion on Varchar to datetime error within the Shaiya PServer Development forum part of the Shaiya Private Server category.

Reply
 
Old   #1
 
elite*gold: 0
Join Date: Sep 2010
Posts: 17
Received Thanks: 1
Varchar to datetime error

Hi, everyone... to the point.... I'm having some problems with mi warehouse and item mall, all the problems are because varchar cannot convert into datetime (I THINK) and I'm getting sick of searching in others post because anything works so... here are my logs.

Hope somebody could help me... please please

If somebody knows how to fix this, I'll be grateful if he explains me detailed and sloooooooowly please


PS_GAME__system log start (Game01)


PS_GAMELOG__system log start (GameLog01)


PS_DBAGENT__charsave log start (DBAgent01)


PS_DBAGENT__system log start (DBAgent01)
Syek is offline  
Old 06/19/2013, 17:41   #2
 
sondo81's Avatar
 
elite*gold: 0
Join Date: Sep 2010
Posts: 400
Received Thanks: 643
just use the English sql server,
Quote:
al convertir el tipo de datos
sounds more Spanish or so..
sondo81 is offline  
Thanks
1 User
Old 06/19/2013, 17:58   #3
 
elite*gold: 0
Join Date: Sep 2010
Posts: 17
Received Thanks: 1
just reinstalling sql in english could be the fix? I'll try it
Syek is offline  
Old 06/19/2013, 20:29   #4
 
GM.Triest's Avatar
 
elite*gold: 0
Join Date: Feb 2009
Posts: 192
Received Thanks: 151
That doesn't fix it. Your usp_Insert_Action_Log_E is messed up. I had the same problem.
Just modify your current Stored procedure to :

Code:
USE [PS_GameLog]
GO
/****** Object:  StoredProcedure [dbo].[usp_Insert_Action_Log_E]    Script Date: 06/19/2013 13:29:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  Stored Procedure dbo.usp_Insert_Action_Log_E    Script Date: 2008-6-7 18:32:40 ******/

/****** Object:  Stored Procedure dbo.usp_Insert_Action_Log_E    Script Date: 2008/3/15 下午 05:16:53 ******/



ALTER  Proc [dbo].[usp_Insert_Action_Log_E]

/* 
Created by , 2004-08-17
Modified by , 2004-08-19

게임내 행동 로그 남기기 */


/*

*/

@UserID varchar(18),
@UserUID int,
@CharID int,
@CharName varchar(50),
@CharLevel tinyint,
@CharExp int,
@MapID smallint,
@PosX real,
@PosY real,
@PosZ real,
@ActionTime datetime,
@ActionType tinyint,
@Value1 bigint = null,
@Value2 int = null,
@Value3 int = null,
@Value4 bigint = null,
@Value5 int = null,
@Value6 int = null,
@Value7 int = null,
@Value8 int = null,
@Value9 int = null,
@Value10 int = null,
@Text1 varchar(100) = '',
@Text2 varchar(100) = '',
@Text3 varchar(100) = '',
@Text4 varchar(100) = '',
@Sql nvarchar(4000) = '',
@yyyy varchar(4) = '',
@mm varchar(2) = '',
@dd varchar(2) = '',
@Bucket smallint = -1

AS

SET @yyyy = datepart(yyyy, @ActionTime)
SET @mm = datepart(mm, @ActionTime)
SET @dd = datepart(dd, @ActionTime)


IF(LEN(@mm) = 1)
BEGIN
 SET @mm = '0' + @mm
END

IF(LEN(@dd) = 1)
BEGIN
 SET @dd = '0' + @dd
END

SET @Sql = N'
INSERT INTO PS_GameLog.dbo.ActionLog
(UserID, UserUID, CharID, CharName, CharLevel, CharExp, MapID,  PosX, PosY, PosZ, ActionTime, ActionType, 
Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8, Value9, Value10, Text1, Text2, Text3, Text4)
VALUES(@UserID, @UserUID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType, 
@Value1, @Value2, @Value3, @Value4, @Value5, @Value6, @Value7, @Value8, @Value9, @Value10, @Text1, @Text2, @Text3, @Text4)'

EXEC sp_executesql @Sql, 
N'@UserID varchar(18), @UserUID int, @CharID int, @CharName varchar(50), 
@CharLevel tinyint, @CharExp int, @MapID smallint, @PosX real, @PosY real, @PosZ real, @ActionTime datetime, @ActionType tinyint, 
@Value1 bigint, @Value2 int, @Value3 int, @Value4 bigint, @Value5 int, @Value6 int, @Value7 int, @Value8 int, 
@Value9 int, @Value10 int, @Text1 varchar(100), @Text2 varchar(100), @Text3 varchar(100), @Text4 varchar(100)',
@UserID, @UserUID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType, 
@Value1, @Value2, @Value3, @Value4, @Value5, @Value6, @Value7, @Value8, @Value9, @Value10, @Text1, @Text2, @Text3, @Text4
GM.Triest is offline  
Thanks
2 Users
Old 06/19/2013, 22:19   #5
 
castor4878's Avatar
 
elite*gold: 0
Join Date: Dec 2010
Posts: 717
Received Thanks: 3,357
Reinstallation of SQL Svr (that wil reinstall the same soft with same settings) is indeed useless. Meantime the issue is not limited to that "usp_Insert_Action_Log_E" procedure, partly because the posted log was related to "Usp_Save_User_StoredItem_Add_E".

General remarks

several stored procedure contains some ""patchs"" to manage date.
apparently there are 2 reasons for that: accumulations of modifications w/o clean-up of useless code or data declaration, and recurrent bugs with date conversion.

the code:
Code:
SET @yyyy = datepart(yyyy, @ActionTime)
SET @mm = datepart(mm, @ActionTime)
SET @dd = datepart(dd, @ActionTime)

IF(LEN(@mm) = 1)
BEGIN
 SET @mm = '0' + @mm
END

IF(LEN(@dd) = 1)
BEGIN
 SET @dd = '0' + @dd
END
belongs to the first category, the variables @yyyy and @mm and @dd are strictly useless - they do not participate to any SQL statements.
As far as i known *one* stored procedure fills-in a table that does have some 'day', 'month' & 'year' columns (defined as nvarchar, which is a shame), only that SP requires with code; for all others where it was copied w/o any reason, it's just dirtiness.

the point there is not to say "looks that SP it's bad...", the point is: since the SP that frequently generate issues (failures) with date management are polluted with useless code or not reliable patchs, we have to fix them with care. thinking that we can simply don't care about code that seems useless is not a safe practice (when you will face random bugs geenrated by code that shall not exist, you will get my point).

General Constraints

The server components exchange information between them. This is known to work (we can ignore the actual mechanism (could be named pipes, socket, shared mem, it doesn't matter)). They also need to read / write data to the database, it is the purpose of the ps_dbAgent service. That service seems to use ODBC API to transit the SQL request to the SQL Svr engine.

One constraint of these API is that no native communication takes place and thus no data are typed. Instead every bit & byte are managed as text. This includes date.

This implies that the server components generates dates as string and transmits these strings (thru ODBC) to the base where we want them to be magically casted to 'datetime' variables.

It can work, if you are lucky enough. Meaning if some settings (whose list is not that known or actually under your control) allow all together the conversion, some of them include the system defined date representation.

But also it will not work for a lot of us, and thus the definition of a stored procedure with a statement like:

@ActionTime datetime

for an incoming (received) parameter, will generate an error (and the non execution of the procedure) on all systems where the received string can not implicitely be converted to a datetime.

How to Patch?

The log posted by original post shows that the SP is invoked with a date like '2013-06-16 20:50:08'.
This pattern is known as ODBC format: 'YYYY-MM-DD HH:MM:SS'. It seems obvious and understandable w/o mistake. But again, the convertion of SQL svr side are the results of a chain of settings.

To figure it out, try the following code with SQL Svr Management Studio:
Code:
declare @d datetime
set @d = '2012-11-25 12:34:56'
on some systems, we will obtain "success", these hosts can use the SP as provided above.

on other systems, you will obtain an errror with something like "overflow during conversion", these system require:
Code:
declare @d datetime
set @d = '2012-25-11 12:34:56'
to work finely. they do encode date as 'YYYY-DD-MM'.
Other systems with yet another domestic settings can require another format.

for these you must edit the stored procedure as follows:

- replace the datetime type in the list of procedure's parameters by varchar(20)
- declare a new variable as datetime
- perform the string-to-date conversion with the
- substitute varaibles as required in the next requests.

in your case:

Code:
ALTER | CREATE Proc [dbo].[usp_Save_User_StoredItem_Add_E]

@ServerID tinyint,
...
@Count tinyint,
@Maketime datetime,
@Maketype char(1)

AS

INSERT INTO UserStoredItems (ServerID, ..., maketime, maketype)
VALUES (@ServerID, ..., @MakeTime, @Maketype)
becomes

Code:
ALTER | CREATE Proc [dbo].[usp_Save_User_StoredItem_Add_E]

@ServerID tinyint,
....
@Count tinyint,
@StrMaketime varchar(20),
@Maketype char(1)

AS

DECLARE @MakeTime datetime
SET @MakeTime = convert(datetime, @StrMaketime, 120)

INSERT INTO UserStoredItems (ServerID, ..., maketime, maketype)
VALUES (@ServerID, ..., @MakeTime, @Maketype)
where the value 120 (3rd parameter of convert) means that the given string is in ODBC canonical format (meaning 'yyyy-mm-dd hh:mi:ss')

Note that this way to process will work on all systems (!), the ps_dbAgent always generates a "ODBC string-date" and it always inserts it into a string-request (no native communication), for the SQL svr it will always success to convert the string according to the '120' rule-format.
castor4878 is offline  
Thanks
4 Users
Old 06/19/2013, 23:50   #6
 
elite*gold: 0
Join Date: Sep 2010
Posts: 17
Received Thanks: 1
Quote:
Originally Posted by GM.Triest View Post
That doesn't fix it. Your usp_Insert_Action_Log_E is messed up. I had the same problem.
Just modify your current Stored procedure to :

Code:
USE [PS_GameLog]
GO
/****** Object:  StoredProcedure [dbo].[usp_Insert_Action_Log_E]    Script Date: 06/19/2013 13:29:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  Stored Procedure dbo.usp_Insert_Action_Log_E    Script Date: 2008-6-7 18:32:40 ******/

/****** Object:  Stored Procedure dbo.usp_Insert_Action_Log_E    Script Date: 2008/3/15 下午 05:16:53 ******/



ALTER  Proc [dbo].[usp_Insert_Action_Log_E]

/* 
Created by , 2004-08-17
Modified by , 2004-08-19

게임내 행동 로그 남기기 */


/*

*/

@UserID varchar(18),
@UserUID int,
@CharID int,
@CharName varchar(50),
@CharLevel tinyint,
@CharExp int,
@MapID smallint,
@PosX real,
@PosY real,
@PosZ real,
@ActionTime datetime,
@ActionType tinyint,
@Value1 bigint = null,
@Value2 int = null,
@Value3 int = null,
@Value4 bigint = null,
@Value5 int = null,
@Value6 int = null,
@Value7 int = null,
@Value8 int = null,
@Value9 int = null,
@Value10 int = null,
@Text1 varchar(100) = '',
@Text2 varchar(100) = '',
@Text3 varchar(100) = '',
@Text4 varchar(100) = '',
@Sql nvarchar(4000) = '',
@yyyy varchar(4) = '',
@mm varchar(2) = '',
@dd varchar(2) = '',
@Bucket smallint = -1

AS

SET @yyyy = datepart(yyyy, @ActionTime)
SET @mm = datepart(mm, @ActionTime)
SET @dd = datepart(dd, @ActionTime)


IF(LEN(@mm) = 1)
BEGIN
 SET @mm = '0' + @mm
END

IF(LEN(@dd) = 1)
BEGIN
 SET @dd = '0' + @dd
END

SET @Sql = N'
INSERT INTO PS_GameLog.dbo.ActionLog
(UserID, UserUID, CharID, CharName, CharLevel, CharExp, MapID,  PosX, PosY, PosZ, ActionTime, ActionType, 
Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8, Value9, Value10, Text1, Text2, Text3, Text4)
VALUES(@UserID, @UserUID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType, 
@Value1, @Value2, @Value3, @Value4, @Value5, @Value6, @Value7, @Value8, @Value9, @Value10, @Text1, @Text2, @Text3, @Text4)'

EXEC sp_executesql @Sql, 
N'@UserID varchar(18), @UserUID int, @CharID int, @CharName varchar(50), 
@CharLevel tinyint, @CharExp int, @MapID smallint, @PosX real, @PosY real, @PosZ real, @ActionTime datetime, @ActionType tinyint, 
@Value1 bigint, @Value2 int, @Value3 int, @Value4 bigint, @Value5 int, @Value6 int, @Value7 int, @Value8 int, 
@Value9 int, @Value10 int, @Text1 varchar(100), @Text2 varchar(100), @Text3 varchar(100), @Text4 varchar(100)',
@UserID, @UserUID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType, 
@Value1, @Value2, @Value3, @Value4, @Value5, @Value6, @Value7, @Value8, @Value9, @Value10, @Text1, @Text2, @Text3, @Text4
this didn't work :s

castor the first code was wrong the second one worked, emmmm if it's not a big problem, could you emmm giveme a mini tutorial to know how to do this ? I'm kind of slow in this topic :s

Quote:
Other systems with yet another domestic settings can require another format.

for these you must edit the stored procedure as follows:

- replace the datetime type in the list of procedure's parameters by varchar(20)
- declare a new variable as datetime
- perform the string-to-date conversion with the convert function
- substitute varaibles as required in the next requests.
thankyou
Syek is offline  
Reply


Similar Threads Similar Threads
[Help] Stucked at DateTime.Now
01/13/2012 - CO2 Private Server - 10 Replies
His code should spawn monster in time xx.35 but don't work.way? Thanks for help
datetime and count
09/14/2010 - CO2 Private Server - 2 Replies
okay i was trying to count how long an action took. but it counted how long it took to send the action, but not how long it took. How can i make it count after the action is send. And yea I did put the time after the action, but still doing it. Something I can use to check if action is done? Is for 5165 tanel.
DateTime Guild War
12/16/2009 - CO2 Private Server - 5 Replies
Alright well in the 5165 source I made a datetime start for guild war. Here's the code. public static void GuildWarTime()// On or Off { if (DateTime.Now.DayOfWeek == DayOfWeek.Wednesday && DateTime.Now.Hour == 2 && DateTime.Now.Minute == 15) { StartWar(); } }
[QUESTion] DateTime Help
07/23/2009 - CO2 Private Server - 6 Replies
Ok im looking at this one datetime im trying to make one but i dont understand this hour part where it says ServerTime.Hour == 8 what time is the 8 lol else if ((World.ServerTime.DayName == "Monday" || World.ServerTime.DayName == "Wednesday") && World.ServerTime.Hour == 8 && World.ServerTime.Minute == 50)
[HELP] Datetime error
06/23/2009 - CO2 Private Server - 0 Replies
Ok im geting this error when i try to load 10 expballs per day here it is http://img188.imageshack.us/img188/8279/erroryfn.p ng



All times are GMT +1. The time now is 15:43.


Powered by vBulletin®
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Support | Contact Us | FAQ | Advertising | Privacy Policy | Terms of Service | Abuse
Copyright ©2024 elitepvpers All Rights Reserved.