I think this is duplicate to #2441

Pradeep Mathan, can you provide full stacktrace of the error?

We use the stable version of Tigase 5.2.0, The message archiving component source code on inspection has problems for sql server regarding limit and offset. Anyway I will try to reproduce the error and send the server side stacktrace. I had included the SqlException error message.

I am attaching the full stacktrace.

java.sql.SQLException: Incorrect syntax near 'limit'.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2894)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2334)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:643)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:506)
        at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:979)
        at tigase.archive.MessageArchiveDB.getItems(MessageArchiveDB.java:669)
        at tigase.archive.MessageArchiveDB.getItems(MessageArchiveDB.java:514)
        at tigase.archive.MessageArchiveComponent.getMessages(MessageArchiveComponent.java:407)
        at tigase.archive.MessageArchiveComponent.processActionPacket(MessageArchiveComponent.java:227)
        at tigase.archive.MessageArchiveComponent.processPacket(MessageArchiveComponent.java:107)
        at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessageReceiver.java:1475)

Pradeep Mathan - thank you for the stacktrace. It looks like the limitations of MS SQL in regard to currently used schema.

Andrzej, can you make the adjustments to the message archive schema and make it compatible with MS SQL server?

It is not issue with SQLServer schema but with SQLServer not supporting limit and @offset@.

(updated description with exceptions)

Issue is not fixed. We are using Sql Server 2008.

tigase.db.TigaseDBException: Cound not retrieve items

    at tigase.archive.db.JDBCMessageArchiveRepository.getItems(JDBCMessageAr

chiveRepository.java:620)

    at tigase.archive.MessageArchiveComponent.getMessages(MessageArchiveComp

onent.java:425)

    at tigase.archive.MessageArchiveComponent.processActionPacket(MessageArc

hiveComponent.java:270)

    at tigase.archive.MessageArchiveComponent.processPacket(MessageArchiveCo

mponent.java:111)

    at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessa

geReceiver.java:1490)

Caused by: java.sql.SQLException: Incorrect syntax near 'limit'.

    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.j

ava:372)

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

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

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

    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement

.java:506)

    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPrep

aredStatement.java:979)

    at tigase.archive.db.JDBCMessageArchiveRepository.getItems(JDBCMessageAr

chiveRepository.java:778)

    at tigase.archive.db.JDBCMessageArchiveRepository.getItems(JDBCMessageAr

chiveRepository.java:609)

    at tigase.archive.MessageArchiveComponent.getMessages(MessageArchiveComp

onent.java:425)

    at tigase.archive.MessageArchiveComponent.processActionPacket(MessageArc

hiveComponent.java:270)

    at tigase.archive.MessageArchiveComponent.processPacket(MessageArchiveCo

mponent.java:111)

    at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessa

geReceiver.java:1490)

Am I missing something out, I tested with Nov 19 2014 nightly build, it was not working I had posted the stacktrace, Nov 20 2014 nightly build does not have Message Archiving Changes. I did not understand why the status was changed to closed. Can you kindly help us out.

Pradeep, I am sorry, this is my mistake. I saw that it is marked as duplicated to #2441 which was closed already, so I closed this one as well. Reverting back to new.

I fixed SQL queries generated for SQLServer to retrieve list of messages and list of message collections.

It will be fixed in next nightly build of Tigase Message Archiving component.

Pradeep, please check it our and let us know if the problem is resolved.

Artur,

I checked the new tigase nightly build on Nov 30 and Dec 2 2014. I am using Sql Server 2008 as the sql database. I get an exception. Am I missing something out. This is the stacktrace.

SEVERE: Error retrieving messages

tigase.db.TigaseDBException: Cound not retrieve items

    at tigase.archive.db.JDBCMessageArchiveRepository.getItems(JDBCMessageAr

chiveRepository.java:646)

    at tigase.archive.MessageArchiveComponent.getMessages(MessageArchiveComp

onent.java:425)

    at tigase.archive.MessageArchiveComponent.processActionPacket(MessageArc

hiveComponent.java:270)

    at tigase.archive.MessageArchiveComponent.processPacket(MessageArchiveCo

mponent.java:111)

    at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessa

geReceiver.java:1490)

Caused by: java.sql.SQLException: Incorrect syntax near 'limit'.

    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.j

ava:372)

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

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

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

    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement

.java:506)

    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPrep

aredStatement.java:979)

    at tigase.archive.db.JDBCMessageArchiveRepository.getItems(JDBCMessageAr

chiveRepository.java:804)

    at tigase.archive.db.JDBCMessageArchiveRepository.getItems(JDBCMessageAr

chiveRepository.java:635)

    at tigase.archive.MessageArchiveComponent.getMessages(MessageArchiveComp

onent.java:425)

    at tigase.archive.MessageArchiveComponent.processActionPacket(MessageArc

hiveComponent.java:270)

    at tigase.archive.MessageArchiveComponent.processPacket(MessageArchiveCo

mponent.java:111)

    at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessa

geReceiver.java:1490)

Andrzej, please review stack trace and assist with resolution.

Could you share example JDBC connection string you pass to --user-repo-uri property in etc/init.properties file? I fix this issue and it works fine on my installation but I would like to see your connection string as from it I can get information which MSSQL Server database driver for Java is used so I could check this in same configuration.

I am attaching the etc/init.properties file

--comp-class-1 = tigase.muc.MUCComponent

--virt-hosts = M41WIN8

--user-db-uri = jdbc:jtds:sqlserver://hostname;databaseName=tigasedb_Nov30;user=username;password=password;schema=dbo;lastUpdateCount=false;cacheMetaData=false

--user-db = sqlserver

--admins = admin@M41WIN8

--comp-name-4 = message-archive

--comp-name-3 = proxy

config-type = --gen-config-all

--comp-name-2 = pubsub

--comp-name-1 = muc

--cluster-mode = true

--sm-plugins = +message-archive-xep-0136

--debug = server

--comp-class-4 = tigase.archive.MessageArchiveComponent

--comp-class-3 = tigase.socks5.Socks5ProxyComponent

--comp-class-2 = tigase.pubsub.PubSubComponent

What is the progress of this issue? It is blocking another ticket, so this is important to resolve it asap.

This issue is solved. Root cause of this issue was that we have 2 types of JDBC drivers for MSSQL and pervious fix was for SQLServer driver while I forgot to apply this fix also when we are using JTDS driver.