ODS Invalid object name SQL Error

[Former Member]

Hi,

I have done the set-up of ODS transfer for a planned production environment.
The current SAP ME version for it is 6.0.4.6.

In the odslog i get the following messages about not finding the objects. I can log in to the wip database

as WIP DB user and manually do the select query successfully. My SAP ME application works with no
problems.

Anybody have an idea about the reason.

Best Regards,

Johan Nordebrink

Thu May 30 15:54:13 CEST 2013 ODSProductionLogExtract: Started
Thu May 30 15:54:19 CEST 2013 ODSProductionLogExtract: [SQL][BaseDataSource].....The following fatal SQL exception has occurred:
SELECT EXTRACT_DATE_TIME FROM ODS_EXTRACT_CONTROL_DATE  WHERE TABLE_NAME ='PRODUCTION_LOG'  AND TRANS_TYPE ='1'
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'ODS_EXTRACT_CONTROL_DATE'.
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 com.sap.me.ods.database.ODSBaseDataSource$StatementWrapper.executeQuery(ODSBaseDataSource.java:1107)
at com.sap.me.frame.jdbc.DynamicQuery.executeQuery(DynamicQuery.java:837)
at com.sap.me.frame.AbstractSqlImplementation.executeQueryAsResultSet(AbstractSqlImplementation.java:167)
at com.sap.me.ods.database.ODSBaseDataSource.executeQuery(ODSBaseDataSource.java:114)
at com.sap.me.ods.olap.ODSBase.getExtractDates(ODSBase.java:1569)
at com.sap.me.ods.olap.ODSProductionLogRollupProcess.ODSExtract(ODSProductionLogRollupProcess.java:77)
at com.sap.me.ods.ODSProductionLogExtract.main(ODSProductionLogExtract.java:40)

Thu May 30 15:54:19 CEST 2013 ODSProductionLogExtract: Exception Occurred.
javax.ejb.EJBException: nested exception is: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'ODS_EXTRACT_CONTROL_DATE'.
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'ODS_EXTRACT_CONTROL_DATE'.
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 com.sap.me.ods.database.ODSBaseDataSource$StatementWrapper.executeQuery(ODSBaseDataSource.java:1107)
at com.sap.me.frame.jdbc.DynamicQuery.executeQuery(DynamicQuery.java:837)
at com.sap.me.frame.AbstractSqlImplementation.executeQueryAsResultSet(AbstractSqlImplementation.java:167)
at com.sap.me.ods.database.ODSBaseDataSource.executeQuery(ODSBaseDataSource.java:114)
at com.sap.me.ods.olap.ODSBase.getExtractDates(ODSBase.java:1569)
at com.sap.me.ods.olap.ODSProductionLogRollupProcess.ODSExtract(ODSProductionLogRollupProcess.java:77)
at com.sap.me.ods.ODSProductionLogExtract.main(ODSProductionLogExtract.java:40)
javax.ejb.EJBException: nested exception is: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'ODS_EXTRACT_CONTROL_DATE'.
at com.sap.me.ods.database.ODSBaseDataSource.executeQuery(ODSBaseDataSource.java:121)
at com.sap.me.ods.olap.ODSBase.getExtractDates(ODSBase.java:1569)
at com.sap.me.ods.olap.ODSProductionLogRollupProcess.ODSExtract(ODSProductionLogRollupProcess.java:77)
at com.sap.me.ods.ODSProductionLogExtract.main(ODSProductionLogExtract.java:40)
Thu May 30 15:54:19 CEST 2013 ODSProductionLogExtract: Completed
Thu May 30 15:54:21 CEST 2013 ODSShopOrderExtract: Started
Thu May 30 15:54:25 CEST 2013 ODSShopOrderExtract: [SQL][BaseDataSource].....The following fatal SQL exception has occurred:
SELECT EXTRACT_DATE_TIME FROM ODS_EXTRACT_CONTROL_DATE  WHERE TABLE_NAME ='SHOP_ORDER'  AND TRANS_TYPE ='1'
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'ODS_EXTRACT_CONTROL_DATE'.
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 com.sap.me.ods.database.ODSBaseDataSource$StatementWrapper.executeQuery(ODSBaseDataSource.java:1107)
at com.sap.me.frame.jdbc.DynamicQuery.executeQuery(DynamicQuery.java:837)
at com.sap.me.frame.AbstractSqlImplementation.executeQueryAsResultSet(AbstractSqlImplementation.java:167)
at com.sap.me.ods.database.ODSBaseDataSource.executeQuery(ODSBaseDataSource.java:114)
at com.sap.me.ods.olap.ODSBase.getExtractDates(ODSBase.java:1569)
at com.sap.me.ods.olap.ODSShopOrderRollupProcess.ODSExtract(ODSShopOrderRollupProcess.java:53)
at com.sap.me.ods.ODSShopOrderExtract.main(ODSShopOrderExtract.java:40)

[sergiy_katerinich · Accepted solution]

Hi Johan,

Please check collation of ODS database and default database of ODS user.

Regards,

Sergiy

[Former Member · Accepted solution]

Hi,

It was the ods user that did have master as default database instead of the ODS database.

After fixing this ODS transfer works without any errors.

Thank you.

Best Regards,

Johan

[sergiy_katerinich · Accepted solution]

Johan,

Just to let you know: this is a requirement in section 3.1 (3.1.2) of SAP ME Installation Guide.

Regards,

Sergiy

[Former Member · Accepted solution]

Hi Sergiy,

Yes I know. Have done it numerous times, but this time I managed to miss it anyway

Best Regards,

Johan

[alexander_teslyuk]

Hi Johan,

It seems that it does not see the table ODS_EXTRACT_CONTROL_DATE. Could you check synonyms? Can you select data from this table using SQL tool under manufacturing-admin context?

Regards,

Alex.