IDM SQL error: Could not carry out execCheckSQL

[Former Member]

All,

We currently upgarded our SAP ECC system to ERP 6 EHP 7 (which I would think should not make a difference).   I completely testing our IDM HCM_LDAP update feed in QAS and all provisiong steps worked fine.    We did NOT perform any upgrade or change to our IDM system.

After the upgrade we are now seeing the following error when a user changes positions ONLY.

TaskId:1004914 - Could not carry
out execCheckSQL:(SELECT COUNT(DISTINCT mskey) FROM idmv_vallink_basic_active
WHERE mcidstore=1 AND
((mskey IN (SELECT mcmskey FROM
idmv_vallink_basic_active WHERE mcattrname='AG_HCM_ACTION' AND mcsearchvalue =
'ZF' AND

It looks like the task is not even starting..it is failing to perform the SQL statement completely.   

Steps I have completed to troubleshoot:

1. I have gone back to QAS and tested this process and it works successfully.

2. I have checked the script it calls in QAS to PRD and also the SQL statment in QAS and PRD and they are exactly the same.

3. I have validated that the task is completly the same.

4. I hvae taken the SQL statement and run it on the SQL Server with a valid MSKEY and gotten a correct response in PRD

5. I have run it through a test provisioning process using the ID that failed and it goes through the process to the update role assignments (then fails as technically their are now no new assignments.

When I look at the users HCM data vai the webUI of the managment console it shows the new correct position did apply to the user, it is just no triggering the SQL statment correctly. 

Side Note:   My new employees hired directly on also hit this task and fail but it does not affect them as they would have been a False resut which is "NULL"  (all their role provisioning happens under the "check for New Hire"

This is very frustraiting.

**Query that works perfectly in QAS with above task that is set up exactly the same.

SELECT COUNT(DISTINCT mskey) FROM idmv_vallink_basic_active WHERE mcidstore=1 AND

((mskey IN (SELECT mcmskey FROM idmv_vallink_basic_active WHERE mcattrname='AG_HCM_ACTION' AND mcsearchvalue = 'ZF' AND mcmskey=%MSKEY%))) OR

((mskey IN (SELECT mcmskey FROM idmv_vallink_basic_active WHERE mcattrname='AG_HCM_ACTION' AND mcsearchvalue = 'Z3' AND mcmskey=%MSKEY%)))

[Former Member · Accepted solution]

we rebooted the server and everything came back up.      very weird.

[Steffi_Warnecke · Accepted solution]

Does that mean, it's working now? ^^

[Former Member · Accepted solution]

yes...sorry.       It is working now.

so do I mark it answered.....

[Steffi_Warnecke · Accepted solution]

Your last post was the solution, so I would have marked that one as the correct answer.

[Former Member · Accepted solution]

If "we have no idea what the heck just happened" as a successful answer then cool.

[Steffi_Warnecke · Accepted solution]

"Every reboot is good."

[Former Member]

If I remember correctly the message could also mean that the query returns an invalid result such as 2 rows of data, or permission error or that the SQL is missing a parenthesis or otherwise is broken.

If you can try to run the statement directly in the DB logged in as the _rt account with the mskey that fails in the %MSKEY% positions you might see if there's a SQL query error, but it looks ok.

It looks like the query is made using the "wizard" thing, but my first comment is to try to avoid mixing mskey and mcmskey in a statement where the source views are all the same (idmv_vallink_basic). You can also avoid distinct by changing the outer view to idmv_entry_simple. And the two inner statements can be combined. The expected false result is 0, not null, and true is 1.

SELECT COUNT(mcmskey) FROM idmv_entry_simple WHERE mcidstore=1 AND

mcmskey IN (SELECT mcmskey FROM idmv_vallink_basic_active WHERE mcattrname='AG_HCM_ACTION' AND mcsearchvalue in ('ZF','Z3')  AND mcmskey=%MSKEY%)

Br,

Per Christian

[Former Member]

Thanks Per, but I have already run the statement from a query window with a couple of real values and it works fine.

I also ran the entire "change position" job in test mode using a valid MSKEY and it ran the query and processed through the conditional task successfully...(and again this same thing is working perfectly in QAS)

It is one of the strangest things I have see to date.

I will discuss trying your query out in QAS to validate it's functionality and if successful then try it in PRD to see if that helps.