2 Replies Latest reply on Nov 3, 2017 11:29 AM by mirco73

    Error in select on Source Model

    mirco73

      Hi,

      in our domain environment, trying to make a simple select * from a Source Model, I get the followinf error:

       

      2017-11-03 12:56:26,268 WARN  [org.teiid.CONNECTOR] (Worker23_QueryProcessorQueue49198) Connector worker process failed for atomic-request=c3TkG9ShqlYA.39.0.24516: org.teiid.translator.jdbc.JDBCExecutionException: 102 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0."Mandante" AS c_0, g_0."Proj_Number_Int" AS c_1, g_0."Project_Number" AS c_2, g_0."Short_Description" AS c_3, g_0."Object_Number" AS c_4, g_0."Project_Manager_Number" AS c_5, g_0."Project_Manager" AS c_6, g_0."Company_Code" AS c_7, g_0."Profit_Center" AS c_8, g_0."Planned_Start_Date" AS c_9, g_0."Planned_Finish_Date" AS c_10, g_0."Customer" AS c_11, g_0."Plant" AS c_12, g_0."Job_Type" AS c_13, g_0."Job_Type_Descr" AS c_14, g_0."Field_of_Application" AS c_15, g_0."Field_of_Application_Descr" AS c_16, g_0."Extension" AS c_17, g_0."Extension_Descr" AS c_18, g_0."Hardware" AS c_19, g_0."Hardware_Descr" AS c_20, g_0."Software" AS c_21, g_0."Software_Descr" AS c_22, g_0."Plant_Description" AS c_23, g_0."Location" AS c_24, g_0."Country_Key" AS c_25, g_0."Load_Date" AS c_26, g_0."Load_Time" AS c_27 FROM "BODM"."dbo"."PROJECT_D" g_0 ORDER BY c_0 OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY]

          at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)

          at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)

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

          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

          at java.lang.reflect.Method.invoke(Method.java:498)

          at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:220)

          at com.sun.proxy.$Proxy44.execute(Unknown Source)

          at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)

          at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)

          at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)

          at java.util.concurrent.FutureTask.run(FutureTask.java:266)

          at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)

          at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)

          at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

          at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

          at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

          at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

          at java.lang.Thread.run(Thread.java:748)

      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'OFFSET'.

          at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)

          at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)

          at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)

          at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)

          at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)

          at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2444)

          at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)

          at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)

          at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:297)

          at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)

          at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)

          ... 17 more

       

      2017-11-03 12:56:26,269 WARN  [org.teiid.PROCESSOR] (Worker22_QueryProcessorQueue49199) TEIID30020 Processing exception for request c3TkG9ShqlYA.39 'TEIID30504 BODM: 102 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0."Mandante" AS c_0, g_0."Proj_Number_Int" AS c_1, g_0."Project_Number" AS c_2, g_0."Short_Description" AS c_3, g_0."Object_Number" AS c_4, g_0."Project_Manager_Number" AS c_5, g_0."Project_Manager" AS c_6, g_0."Company_Code" AS c_7, g_0."Profit_Center" AS c_8, g_0."Planned_Start_Date" AS c_9, g_0."Planned_Finish_Date" AS c_10, g_0."Customer" AS c_11, g_0."Plant" AS c_12, g_0."Job_Type" AS c_13, g_0."Job_Type_Descr" AS c_14, g_0."Field_of_Application" AS c_15, g_0."Field_of_Application_Descr" AS c_16, g_0."Extension" AS c_17, g_0."Extension_Descr" AS c_18, g_0."Hardware" AS c_19, g_0."Hardware_Descr" AS c_20, g_0."Software" AS c_21, g_0."Software_Descr" AS c_22, g_0."Plant_Description" AS c_23, g_0."Location" AS c_24, g_0."Country_Key" AS c_25, g_0."Load_Date" AS c_26, g_0."Load_Time" AS c_27 FROM "BODM"."dbo"."PROJECT_D" g_0 ORDER BY c_0 OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY]'. Originally TeiidProcessingException 'Incorrect syntax near 'OFFSET'.' SQLServerException.java:217. Enable more detailed logging to see the entire stacktrace.

       

       

      This happens randomly, but especially when the server's been up for some days. We have some materialized tables and while we got the error on one node, the other is ok

      After restarting the node everythig is fine (ver. 9.3.3 on Linux RedHat, the source DB is a MSServer 2016)

       

      thanks,

      Mirco

        • 1. Re: Error in select on Source Model
          shawkins

          > the source DB is a MSServer 2016

           

          That is valid 2012+ syntax as far as I can see.  Is there a possibility that you are connecting to a different instance or some legacy flag is being inadvertently set?  If you run the sql from the exception directly against the source, do you still see an error?

          • 2. Re: Error in select on Source Model
            mirco73

            We tried to run the query from SQL develop Studio and we get the error.

            Strange thing is that normally from Teiid this does not happen and the query is ok, but sometimes we get that errror