Am facing the issue in SAP on MSSQL database SQL server agent jobs.
One of the job failing (SAP_sid_SID_MSSQL_COLLECTOR) frequently and it was scheduled to run every twenty minutes due to this the SAP_COLLECTOR_FOR_PERFMONITOR job failing in sap for every two hours and the strange thing is the job scheduled for every one hour and for one hour its excuting successfully and another hour its failing due to sql agent job.
The job log and sql agent job logs are same as below.
SQL error 512: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,>, >= or when the subquery is used as an expressio
Job cancelled after system exception ERROR_MESSAGE
Executed as user: dea. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. [SQLSTATE 21000] (Error 512). The step failed.
I tried all the notes mentioned below but still the job getting cancelled for every alternate hour.
1834490 - SAP_COLLECTOR_FOR_PERFMONITOR fails with SQL Error 512
1519991 - Error in DBACOCKPIT: Sub query returned more than 1 value
Please help me to resolve this job as soon as possible.