project_key: SOA

17 EDS BQT queries are failing when using SqlServer 2005 as a source.   SQLServerException "Type DATE is not a defined system type".  Below is an example.  They are passing in SqlServer 2008.  VDB is attached.


Query:  SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA WHERE BQT1.SmallA.DateValue IN (convert('2000-01-12', date), convert('2000-02-02', date)) ORDER BY IntKey

Log:
2011-06-28 10:35:18,130 DEBUG [org.teiid.CONNECTOR] (Worker16_QueryProcessorQueue136) Source-specific command: SELECT TOP 100 cast(g_0.IntKey AS int) AS c_0 FROM SmallA g_0 WHERE cast(replace(convert(varchar, g_0.DateValue, 102), '.', '-') AS datetime) IN (CAST('2000-01-12' AS DATE), CAST('2000-02-02' AS DATE)) ORDER BY c_0
2011-06-28 10:35:18,179 DEBUG [org.teiid.COMMAND_LOG] (Worker16_QueryProcessorQueue136)         ERROR SRC COMMAND:        endTime=2011-06-28 10:35:18.179        requestID=Q0JsTZlYNGm0.23        sourceCommandID=1        txID=null        modelName=Source        connectorBindingName=sqlserver        sessionID=Q0JsTZlYNGm0        principal=user@teiid-security        finalRowCount=null
2011-06-28 10:35:18,179 WARN  [org.teiid.CONNECTOR] (Worker16_QueryProcessorQueue136) Connector worker process failed for atomic-request=Q0JsTZlYNGm0.23.1.21
[JDBCExecutionException] 243: Error Code:243 Message:'Type DATE is not a defined system type.' error executing statement(s): [Prepared Values: [] SQL: SELECT TOP 100 cast(g_0.IntKey AS int) AS c_0 FROM SmallA g_0 WHERE cast(replace(convert(varchar, g_0.DateValue, 102), '.', '-') AS datetime) IN (CAST('2000-01-12' AS DATE), CAST('2000-02-02' AS DATE)) ORDER BY c_0]
1 [SQLServerException]Type DATE is not a defined system type.
        at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:90)
        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:264)
        at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:338)
        at org.teiid.dqp.internal.process.DataTierTupleSource.access$000(DataTierTupleSource.java:80)
        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:138)
        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:135)
        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
        at java.util.concurrent.FutureTask.run(FutureTask.java:166)
        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:197)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
        at java.lang.Thread.run(Thread.java:636)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Type DATE is not a defined system type.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281)
        at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
        at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:86)
        ... 13 more