Not able to write time stamp value from WebSphere Message Broker to SQL Server 2012 database

Question

Recently we have upgraded one of our application and as part of upgrade the database was also upgraded from SQL Server 2005 to 2008. We use WMB 7.0 to process the incoming messages intended for the application.

Upon receiving the message, broker updates a column (datetime) in db with the value returned by CURRENT_TIMESTAMP method in the messageflow.

UPDATE Database.DATABASENAME
SET MODIFIEDDATE = CURRENT_TIMESTAMP

This was working fine till we were using SQL Server 2005, but with SQL Server 2012 we get this error in broker logs:

[IBM][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character.

In the odbc.ini file we are using this configuration :

Driver=/opt/mqsi/ODBC/V6.0/lib/UKmsss24.so
Description=DataDirect 6.0 SQL Server Wire Protocol
Address= ADDRESSOFDB
Database= DATABASENAEM
AnsiNPW=Yes
QEWSD=40117
QuotedId=No
ReportDateTimeType=0

I have also tried to cast the timestamp before writing to db :

DECLARE timestampChar CHARACTER;
SET timestampChar = CAST (CURRENT_TIMESTAMP AS CHARACTER FORMAT 'yyyy-MM-dd HH:mm:ss.SSS');

UPDATE Database.DATABASENAME
SET MODIFIEDDATE = CAST(timestampChar AS TIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss.SSS')

I've also tried passthru :

PASSTHRU ('UPDATE Database.TRANSACTIONINSTALLMENT AS TI
SET UUID =?,
MODIFIEDDATE =?,
WHERE TI.TRANSACTIONID =? AND
TI.INVOLVEMENTNUMBER =? AND
TI.INSTALLMENTNUMBER =?',
techaccount.UUId,
currentTimestampChar,
cTransactionId,
techaccount.Reinsurer_Involvement_Number,
techaccount.Reinsurer_Involvement_Number,
techaccount.TechAccountAmtItem.InstalmentNbr);

But this also results in same error. Please help anyone.

Answer 1

You could try running the update using PASSTHRU like:

PASSTHRU('UPDATE DATABASENAME SET MODIFIEDDATE = ''' || CAST (CURRENT_TIMESTAMP AS CHARACTER FORMAT 'yyyy-MM-dd HH:mm:ss.SSS') || '''')

And I would take a look why you have the QEWSD in your odbc.ini: http://www-01.ibm.com/support/docview.wss?uid=swg21488008

Answer 2

Got it resolved finally. Below r the details :

Issue : Broker was not able to write the timestamp value in new SQL Server 2012 db.

Reason :
1. Broker sends the timestamp as datetime datatype. 2. Broker sends the timestamp in YYYY-MM-DD HH:MM:SS.ssssss format; both of which are not supported by datetime datatype in SQL Server 2012.

Fix : Cast timestamp to string and change its format to YYYY-MM-DD HH:MM:SS.sss.

Casting and Formatting :

DECLARE timestampChar CHARACTER;
SET timestampChar = CAST (CURRENT_TIMESTAMP AS CHARACTER FORMAT 'yyyy-MM-dd HH:mm:ss.SSS');

UPDATE Database.DATABASENAME
SET MODIFIEDDATE = timestampChar;

Answer 3

WMBv7 does not support SQL Server 2012: please see this link. Later versions of WMB do.

Also, with versions of SQL Server later than 2005, alternatives to datetime were introduced that you may want to consider.