New tasks are failing with DB connector, but jsons are working at the application end

[SUMAIYA_BABU]

We are using a DB connector to provision account and access to an oracle DB application. The create account and add access tasks are marked in error state, even when the account is actually created and access is added at the backend and when we reconcile accounts, the accounts are reconciled to saviynt and the tasks are marked as 'No Action Required' when we run the wsretry again.

{ "createAccountQry":["INSERT INTO USERS (ID,TECHNICAL_VERSION,FIRSTNAME,SURNAME,LOGIN,AUDIT_INITIAL_USER,AUDIT_INITIAL_DATE,DOMAIN,EMAIL,QUALIFICATION,EMPID) VALUES(SYS_GUID(),'1','${user.firstname}','${user.lastname}','${user.customproperty1}','SAVIYNT',TO_TIMESTAMP(TO_CHAR(SYSTIMESTAMP, 'DD-MON-RR HH.MI.SS.FF9 AM'), 'DD-MON-RR HH.MI.SS.FF9 AM'),'TT','${user.email}','${user.jobDescription.replace("'","\'")==user.jobDescription}','${user.employeeid}')"] }

[armaanzahir]

@SUMAIYA_BABU 

Can you provide the error snippet from the logs while provisioning?

[adityachadde]

Hi @armaanzahir,

Please find the below snippet from the logs.

This is intermittent issue sometimes tasks are getting completed sometimes now.

Best Regards,

Aditya Chadde

[BalajiEpari]

Hi @armaanzahir 

We are also getting same issue. New Account task is failing with Error, but accounts get creating in Microsoft SQL DB target. Can you pls help on this.

Regards,

Balaji Epari

[Dhruv_Sharma]

Hi @BalajiEpari @SUMAIYA_BABU 

1.  What is the error on the tasks? Could you please share the screenshot.

2. Is everything working as expected and only issue is the error on the tasks?

3. Could you please mention connector name and EIC version?

Regards,

Dhruv Sharma

[BalajiEpari]

@Dhruv_Sharma 

PFA Task Screenshot

2. it creates account in target(database), but the Account is not creating in Saviynt. 

3.  We are using generic connector type as DB, EIC version 23.8

Regards,

Balaji Epari

[Dhruv_Sharma]

Hi @BalajiEpari 

The error mentions we are trying to insert a duplicate value of the EMP ID (HCM041093) which is primary key in HR_DATA_SAV table. Is there any existing user with same emp id in Saviynt?

Can you check if the similar error is observed for other users as well?

Regards,

Dhruv Sharma

[BalajiEpari]

Hi @Dhruv_Sharma 

Yes, it is trying to insert duplicate value. As we observe, first time it creates an account in DB Successfully but not create an account in Saviynt. Due to this in the next run it trys to process the tasks which fails as the account already exists.
This issue occurs for some tasks intermittently.  

Regards,

Balaji Epari

[Dhruv_Sharma]

Hi @BalajiEpari 

Can you check and confirm if there are already existing accounts with same employee id for other users. Since it is happening for some users intermittently, please validate if there is any data issue.

Please share full logs removing sensitive information.

Regards,

Dhruv Sharma

[BalajiEpari]

Hi @Dhruv_Sharma 

We verified this, there are no already existing accounts with same employee id for other users.

Regards,

Balaji Epari

[Dhruv_Sharma]

Hi @BalajiEpari 

We need to look into full logs at the time of issue. 

Regards,

Dhruv Sharma

[Dhruv_Sharma]

Hi @BalajiEpari 

From the logs provided, I can see below pattern. The create account query is failing with below errors.

======================
"2023-11-06T18:01:15.487+00:00","ecm-worker","provisoning.DBProvisioningService","quartzScheduler_Worker-7","ERROR","Error in create account - com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 's'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at

=======================
"2023-11-06T18:01:31.427+00:00","ecm-worker","provisoning.DBProvisioningService","quartzScheduler_Worker-7","ERROR","Error in create account - com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 't'. at
=====================================
"2023-11-06T20:15:03.983+00:00","ecm-worker","provisoning.DBProvisioningService","quartzScheduler_Worker-9","ERROR","Error in create account - com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'l'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at
=======================================
"2023-11-06T18:01:26.703+00:00","ecm-worker","provisoning.DBProvisioningService","quartzScheduler_Worker-7","ERROR","Error in create account - com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'Neill'.
====================================
"2023-11-06T18:01:23.077+00:00","ecm-worker","provisoning.DBProvisioningService","quartzScheduler_Worker-7","ERROR","Error in create account - com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'mt'. at

Observing from the above logs, there seems to be an issue either in the data at these characters or the query where it fails to read data post some characters. Can you check if there are any special characters /apostrophe (') etc in these account details.  

Regards,

Dhruv Sharma

[BalajiEpari]

Hi @Dhruv_Sharma 

Yes, there are apostrophe char in one of the Saviynt attributes which causes to fails the tasks. we modify the code to escape the special chars. 
Not sure Even though New Account tasks fails, how it creates accounts in target DB. 

Regards,

Balaji Epari

[Dhruv_Sharma]

Hi @BalajiEpari 

I would request you to open a Fresh service support ticket for the same with all the supporting details and link to this forum post. Please confirm once done. We will look into it.

Regards,

Dhruv Sharma

[sonamchikorde]

Hi @Dhruv_Sharma, we have already created ticket #INC-2007812 for the same. 

Please help.