UCCX Script - SQL Error on DB Write

[joe.conger]

I created a post-call survey scripts that records the survey results to a MS SQL 2014 Database. I use the PostCallTreatment final String variable to trigger the Survey.aef script when the agent uses the [End call] within Finesse. The DB Write works 95% of the time.  The other 5% it generates the DB Write / SQL Error.  I added a "Send eMail" object to the SQL Error branch so that I know when it fails.  Are there best practices for the the UCCX script or DB configuration to avoid these errors.  I do use the "On Exception(ContactInactiveException) Goto WRITE SQL DATA)" object to write the SQL record even if the caller hangs up during the survey.  The call center manager wanted to know if the agent terminated the call correctly using the Finesse - End call. 

Basically I have the following:

• Label: WRITE SQL DATA
• DB Write: Survey
• Delay 1 sec 
• DB Release: Survey
• Clear Exception (ContactInactiveException)

[mateev.borislav]

Why don't you use a stored procedure instead of DB Write? 

Can you post the log from RTMT after you enable STEPS_DB traces?

[Elliot Dierksen]

First off, I don't think CCX supports the use of stored procedures. I know it didn't some time back.

Regarding storing the survey results, why not store them in the custom call variables and then pull them out of the database later?

Is it possible that a caller is hanging up prior to completing the survey and that is causing the failed writes? Are there constraints on the table that wouldn't allow NULL is the caller didn't answer a question?

[mateev.borislav]

Well, there isn't an explicit way i.e. a step to execute store proc but what you do is use a DB Read step.

Execute the store proc with params:

dbo.sp_test @param1=$var1, @param2=$var2, .......... , @paramN=$varN; 

[joe.conger]

There were not enough custom variables to handle the survey results.  I added the "On Exception(ContactInactiveException) to handle the caller disconnects.  My UCCX debugs indicate that the DB Write step is still processing on a hang up.  I have the script initialize each survey answer to "0".

The DB Write script generates a new row in the Survey MSSQL table every time the agent presses Finesse [End] call.  As you can image most customers do not complete the Survey so 90+% of the Survey results contain "0" for the Survey answers.   I have about 30 new survey records a day and maybe I get one failure a day at random times.