Varchar to datetime error

Syek · 06/19/2013, 06:58

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)

Spoiler

PS_GAMELOG__system log start (GameLog01)

Spoiler

PS_DBAGENT__charsave log start (DBAgent01)

Spoiler

PS_DBAGENT__system log start (DBAgent01)

Spoiler

sondo81 · 06/19/2013, 17:41

just use the English sql server,

Quote:

al convertir el tipo de datos

sounds more Spanish or so..

Syek · 06/19/2013, 17:58

just reinstalling sql in english could be the fix?

I'll try it

GM.Triest · 06/19/2013, 20:29

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

castor4878 · 06/19/2013, 22:19

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.

Syek · 06/19/2013, 23:50

Quote:

Originally Posted by GM.Triest

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