I am attempting to update a database with a datetime value. The database column data type is datetime (MS SQL 2k). Whenever I attempt to databaseupdate, I get this error in the pbtrace.log file:
Error 1 (rc 100)
(c3a09a8): PREPARE:
(c3a09a8): UPDATE xxx_xxx SET LASTLOGINPOP3 = '256-0-0 0:0:0.000' WHERE USERID = 'uuuuuuu' (16 MilliSeconds)
(c3a09a8):
Error 3606 (rc -1) : Arithmetic overflow occurred.
(c3a09a8): CANCEL: (0 MilliSeconds)
(c3a09a8): COMMIT: (0 MilliSeconds)
(c3a09a8):
Error 3902 (rc -1) : The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
(c3a09a8): DISCONNECT: (0 MilliSeconds)
(c3a09a8): SHUTDOWN DATABASE INTERFACE: (0 MilliSeconds)

Here is a portion of my script:
DatabaseGet(rowcounter, 2, userid)

DatabaseGet(rowcounter, 4, usercreated)

FileDate( file2check4, llfiledate )

FileTime( file2check4, llfiletime )

DateTime( llfiledate, llfiletime, llfiledatetime )

String( llfiledatetime, sllfiledate )

DatabaseSet( rowcounter, 3, sllfiledate )

DatabaseUpdate( "update", numrowsupdated )

The problem could be in the conversion
Try adding MessageBox( sllfiledate ) after String( llfiledatetime, sllfiledate ) line to check what you get there

Also, which connection method are you using - native or ODBC?

: I am attempting to update a database with a datetime value. The database
: column data type is datetime (MS SQL 2k). Whenever I attempt to
: databaseupdate, I get this error in the pbtrace.log file: Error 1 (rc 100)
: (c3a09a8): PREPARE: (c3a09a8): UPDATE xxx_xxx SET LASTLOGINPOP3 = '256-0-0
: 0:0:0.000' WHERE USERID = 'uuuuuuu' (16 MilliSeconds)
: (c3a09a8): Error 3606 (rc -1) : Arithmetic overflow occurred.
: (c3a09a8): CANCEL: (0 MilliSeconds)
: (c3a09a8): COMMIT: (0 MilliSeconds)
: (c3a09a8): Error 3902 (rc -1) : The COMMIT TRANSACTION request has no
: corresponding BEGIN TRANSACTION.
: (c3a09a8): DISCONNECT: (0 MilliSeconds)
: (c3a09a8): SHUTDOWN DATABASE INTERFACE: (0 MilliSeconds)

: Here is a portion of my script: DatabaseGet(rowcounter, 2, userid)

: DatabaseGet(rowcounter, 4, usercreated)

: FileDate( file2check4, llfiledate )

: FileTime( file2check4, llfiletime )

: DateTime( llfiledate, llfiletime, llfiledatetime )

: String( llfiledatetime, sllfiledate )

: DatabaseSet( rowcounter, 3, sllfiledate )

: DatabaseUpdate( "update", numrowsupdated )

: The problem could be in the conversion
: Try adding MessageBox( sllfiledate ) after String( llfiledatetime,
: sllfiledate ) line to check what you get there

: Also, which connection method are you using - native or ODBC?

The file date looks Ok in the script trace, but I'll try the messagebox.
Native driver. Should I try ODBC ?

: The problem could be in the conversion
: Try adding MessageBox( sllfiledate ) after String( llfiledatetime,
: sllfiledate ) line to check what you get there

: Also, which connection method are you using - native or ODBC?
The messagebox looks good.
I'll try the ODBC.

JAL 44: DATETIME
JAL 44: Executing DATETIME("10/21/2002", "09:32:36", "1/1/1900 00:00:00")
JAL Return "10/21/2002 09:32:36"
JAL 46: STRING
JAL 46: Executing STRING("10/21/2002 09:32:36", "")
JAL Return "10/21/2002 09:32:36"
JAL 47: MESSAGEBOX
JAL 47: Executing MESSAGEBOX("10/21/2002 09:32:36")
JAL
JAL 48: DATABASESET
JAL 48: Executing DATABASESET("1", "3", "10/21/2002 09:32:36")
JAL
JAL 49: DATABASEUPDATE
JAL 49: Executing DATABASEUPDATE("update", "0")

With ODBC, it still errors, the database log:
Error 1 (rc 100)
(dab0190): PREPARE WITH BIND VARIABLES:
(dab0190): UPDATE pga_com SET lastloginpop3 = ? WHERE userid = ? (0 MilliSeconds)
(dab0190): DATETIME Length=0 ID=:1
(dab0190): VCHAR Length=7 ID=:2 *00gueta* (0 MilliSeconds)
(dab0190): EXECUTE: (0 MilliSeconds)
(dab0190):
Error 999 (rc -1) : SQLSTATE = 22008
[Microsoft][ODBC SQL Server Driver]Invalid date format
(dab0190): CANCEL: (0 MilliSeconds)

: The messagebox looks good.
: I'll try the ODBC.

: JAL 44: DATETIME
: JAL 44: Executing DATETIME("10/21/2002", "09:32:36",
: "1/1/1900 00:00:00")
: JAL Return "10/21/2002 09:32:36"
: JAL 46: STRING
: JAL 46: Executing STRING("10/21/2002 09:32:36", "")
: JAL Return "10/21/2002 09:32:36"
: JAL 47: MESSAGEBOX
: JAL 47: Executing MESSAGEBOX("10/21/2002 09:32:36")
: JAL
: JAL 48: DATABASESET
: JAL 48: Executing DATABASESET("1", "3", "10/21/2002
: 09:32:36")
: JAL
: JAL 49: DATABASEUPDATE
: JAL 49: Executing DATABASEUPDATE("update", "0")

What is your default date/time format in the database?

Can you use DatabaseUpdate with an exact command instead of DatabaseSave?

: With ODBC, it still errors, the database log: Error 1 (rc 100)
: (dab0190): PREPARE WITH BIND VARIABLES: (dab0190): UPDATE pga_com SET
: lastloginpop3 = ? WHERE userid = ? (0 MilliSeconds)
: (dab0190): DATETIME Length=0 ID=:1
: (dab0190): VCHAR Length=7 ID=:2 *00gueta* (0 MilliSeconds)
: (dab0190): EXECUTE: (0 MilliSeconds)
: (dab0190): Error 999 (rc -1) : SQLSTATE = 22008
: [Microsoft][ODBC SQL Server Driver]Invalid date format
: (dab0190): CANCEL: (0 MilliSeconds)

: What is your default date/time format in the database?

: Can you use DatabaseUpdate with an exact command instead of DatabaseSave?
I'm not exactly sure how to answer that one. The default for that column was blank, I changed it to (getdate()) and it did not help.

Update on this issue, please ?

: The file date looks Ok in the script trace, but I'll try the messagebox.
: Native driver. Should I try ODBC ?

As I understand the problem is caused the way 24x7 presets date/time value. This presentation is not understood by your database
When you call the DatabaseUpdate method 24x7 generates the actual SQL update statement or statements depending on how many rows have been changed since last update or retrieve and sends them to the database.
If for the sake of argument you change just one value (as in your script) and call the DatabaseUpdate method 24x7 will execute one SQL command like the following

UPDATE mytable SET date_col = '12/10/2003 09:25:18' WHERE my_key_col = 7;

If your database expects the date/time value in '10-DEC-2003 09:25:18' format it will surely reject '12/10/2003 09:25:18' and return some error.

As a workaround, please try using an ODBC connection. If you get the same error using ODBC connection, locate PBODB70.INI file in 24x7 home directory and edit PBDateTime parameter in the proper section of the file. By default this parameter is set to
PBDateTime='STANDARD_DATETIME'

You can change it to PBDateTime='DD-MON-YYYY HH:MM:SS' or another format understood by your database. This should fix the issue.

: Update on this issue, please ?