Support Article
SystemPulse process throw datetime conversion error in SqlServer
SA-2235
Summary
Administrator has observed SQLServerException in PegaRULES log file while PRPC tries to query PR_SYS_UPDATESCACHE table in the split-schema based PRPC 7.1.5 setup with SQL Server 2008 R2. There were no visible symptoms reported by the users.
Error Messages
(l.access.ConnectionManagerImpl) ERROR - Not returning connection 46504 for database "pegadata" to the pool as it previously encountered the following error
User ID: (unknown)
Last SQL: select pzClearCacheType as "pzClearCacheType", pzCreateDateTime as "pzCreateDateTime", pzKeyString as "pzKeyString", pzNodeName as "pzNodeName", pzObjClass as "pzObjClass", pzParameters as "pzParameters", pzSystemName as "pzSystemName" from PEGA_DATA.pr_sys_updatescache where (
(pzNodeName <> 'dev155') or (pzNodeName is NULL)) and (pzCreateDateTime between '2014-09-15 12:41:23.419' and '2014-09-15 13:41:23.419')
com.microsoft.sqlserver.jdbc.SQLServerException: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:254)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:84)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.<init>(SQLServerResultSet.java:311)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1526)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:332)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:404)
at com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl.execute(DatabasePreparedStatementImpl.java:990)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:6194)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6664)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6620)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6601)
at com.pega.pegarules.management.internal.PulseStatusAPI.PulseActivity(PulseStatusAPI.java:142)
Steps to Reproduce
NA
Root Cause
SystemPulse processing issue during conversion of a VARCHAR data type to a DATETIME data type in MS-SQL.
Resolution
This issue has been fixed in PRPC 7 ML7. Timestamp has been formatted as "yyyyMMdd HH:mm:ss:SSS".
Published January 31, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.