Collation problems

[Steve James]

Hi, we get this type of error every now and then due to table creation scripts.

Please advise.
Thanks

16-Nov-29 00:01:21:947 | ERROR | Error deleting old runtime versions from the runtime versions tables.
java.sql.SQLException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:504)
at org.enhydra.jdbc.core.CorePreparedStatement.executeUpdate(CorePreparedStatement.java:102)
at sun.reflect.GeneratedMethodAccessor128.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at net.bull.javamelody.JdbcWrapper.doExecute(JdbcWrapper.java:403)
at net.bull.javamelody.JdbcWrapper$StatementInvocationHandler.invoke(JdbcWrapper.java:128)
at net.bull.javamelody.JdbcWrapper$DelegatingInvocationHandler.invoke(JdbcWrapper.java:285)
at com.sun.proxy.$Proxy5.executeUpdate(Unknown Source)
at com.ebasetech.ufs.kernel.workspace.ServerRuntimePersist.deleteObsoleteRuntimeVersions(ServerRuntimePersist.java:891)
at com.ebasetech.ufs.kernel.RuntimeVersionsMaintenance.execute(RuntimeVersionsMaintenance.java:15)
at com.ebasetech.ufs.batch.ExecutableTask.run(ExecutableTask.java:157)
at java.lang.Thread.run(Thread.java:745)
16-Nov-29 00:01:21:947 | INFO | Transaction rolled back
16-Nov-29 00:01:21:947 | ERROR | Unexpected error occurred during scheduled task execution for task id RuntimeHeadlessMaintenance
com.ebasetech.xi.exceptions.FormRuntimeException: java.sql.SQLException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
at com.ebasetech.ufs.kernel.workspace.ServerRuntimePersist.deleteObsoleteRuntimeVersions(ServerRuntimePersist.java:896)
at com.ebasetech.ufs.kernel.RuntimeVersionsMaintenance.execute(RuntimeVersionsMaintenance.java:15)
at com.ebasetech.ufs.batch.ExecutableTask.run(ExecutableTask.java:157)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:504)
at org.enhydra.jdbc.core.CorePreparedStatement.executeUpdate(CorePreparedStatement.java:102)
at sun.reflect.GeneratedMethodAccessor128.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at net.bull.javamelody.JdbcWrapper.doExecute(JdbcWrapper.java:403)
at net.bull.javamelody.JdbcWrapper$StatementInvocationHandler.invoke(JdbcWrapper.java:128)
at net.bull.javamelody.JdbcWrapper$DelegatingInvocationHandler.invoke(JdbcWrapper.java:285)
at com.sun.proxy.$Proxy5.executeUpdate(Unknown Source)
at com.ebasetech.ufs.kernel.workspace.ServerRuntimePersist.deleteObsoleteRuntimeVersions(ServerRuntimePersist.java:891)
... 3 more

[Jon]

Sorry about the delay in answering. I wonder if this problem has been caused by the fix for http://forum.ebasetech.com/forum/viewtopic.php?t=1287.

Do you think you could check your repository database that the following three tables are correct (it's the collation for the RUNTIME_ID and ID columns that are causing the problem):

create table [dbo].[runtime_user_versions]
(
[RUNTIME_ID ] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[USER_ID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)

create table [dbo].[runtime_versions]
(
[ID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TIMEOUT] [datetime] NOT NULL,
[RUNTIME_TYPE] VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RUNTIME_ZIP] [image] NULL
)

CREATE TABLE [dbo].[current_versions]
(
[PROJECT] VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PATH] VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RUNTIME_ID] VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RUNTIME_TYPE] VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)

[Steve James]

Thanks Jon,

It looks like SQL changed current_versions.runtime_id collation when the column size changed from 10 to 255 (via MSS Mgmt Studio UI). The collation was changed to the database default ie Latin1_General_CI_AS.