powered by Jive Software

OpenFire MSSQL error using DISTINCT with ntext

I have a new installation of OpenFire and I am getting the follow error in the error.log file. I am running OpenFire 3.8 with Microsoft SQL Server 2008 R2. Any help with this error would be greatly appreciated.

2013.02.28 11:52:29 org.jivesoftware.util.Log - Error selecting conversations

java.sql.SQLException: The ntext data type cannot be selected as DISTINCT because it is not comparable.

at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)

at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)

at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)

at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)

at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)

at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatem ent.java:777)

at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)

at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)

at $java.sql.PreparedStatement$$EnhancerByProxool$$458f1e25.executeQuery()

at com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager.getActiveConvers ations(JdbcPersistenceManager.java:381)

at com.reucon.openfire.plugin.archive.impl.ArchiveManagerImpl.(ArchiveManage rImpl.java:35)

at org.jivesoftware.openfire.plugin.MonitoringPlugin.initializePlugin(MonitoringPl ugin.java:154)

at org.jivesoftware.openfire.container.PluginManager.loadPlugin(PluginManager.java :483)

at org.jivesoftware.openfire.container.PluginManager.access$300(PluginManager.java :80)

at org.jivesoftware.openfire.container.PluginManager$PluginMonitor.run(PluginManag er.java:1073)

at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.util.concurrent.FutureTask$Sync.innerRunAndReset(Unknown Source)

at java.util.concurrent.FutureTask.runAndReset(Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101 (Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodi c(Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknow n Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.lang.Thread.run(Unknown Source)

try to change in table “ofMessageArchive” the “body” field from ntext to nvarchar(MAX)

Thanks for the help, that did work.