| Summary: | EDS BQT Queries failing on SqlServer 2005 with SQLServerException "Type DATE is not a defined system type" | ||||||
|---|---|---|---|---|---|---|---|
| Product: | [JBoss] JBoss Enterprise SOA Platform 5 | Reporter: | Warren Gibson <wgibson> | ||||
| Component: | EDS | Assignee: | Van Halbert <vhalbert> | ||||
| Status: | CLOSED NEXTRELEASE | QA Contact: | |||||
| Severity: | high | Docs Contact: | |||||
| Priority: | high | ||||||
| Version: | 5.2.0 GA | ||||||
| Target Milestone: | --- | ||||||
| Target Release: | 5.2.0 ER1 | ||||||
| Hardware: | Unspecified | ||||||
| OS: | Unspecified | ||||||
| URL: | http://jira.jboss.org/jira/browse/SOA-3134 | ||||||
| Whiteboard: | |||||||
| Fixed In Version: | Doc Type: | Bug Fix | |||||
| Doc Text: | Story Points: | --- | |||||
| Clone Of: | Environment: | ||||||
| Last Closed: | 2011-10-11 17:12:59 UTC | Type: | Bug | ||||
| Regression: | --- | Mount Type: | --- | ||||
| Documentation: | --- | CRM: | |||||
| Verified Versions: | Category: | --- | |||||
| oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | |||||
| Cloudforms Team: | --- | Target Upstream Version: | |||||
| Attachments: |
|
||||||
Attachment: Added: QT_sqls2005ds_Push.vdb Link: Added: This issue Cloned to SOA-3135 Release Notes Docs Status: Added: Not Yet Documented Writer: Added: dlesage Release Notes Docs Status: Removed: Not Yet Documented Added: Not Required 10/11/2011 - Passed. |
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