IVR integration with SQL DB in UCCX 7

[rehan_uet]

I am configuring an ivr script to write data in SQL DB, when I execute the query in the script it gives me message that '1' row has been altered. Practically it should write the data in DB but DBA confirmed me that it is not writting and DB is rolling back the transaction. What could be the possible problem.

[Clifford McGlamry]

Is your database MS SQL? Can you post your script and the SQL query? 

The most likely candidates for the cause of this would be either:

1.  Something is wrong with the query

2.  The sql user that is executing the query doesn't have permissions necessary to do so. 

If you debug the script and can capture the exact contents of your variables, populate them into the SQL query you have assembled, and then open a desktop query window logging into the SQL server using the same user ID that you're using on the UCCX box, you can see whether or not it works with the UCCX server out of the mix.

Cliff

Please rate helpful posts

[rehan_uet]

Here are the findings based on your suggestions

-     User has read / write rights in the databae

-     The Query works fine locally on the DB machine

Here is what we have observed in the SQL Server Profiler when we execuate the query

ExistingConnection -- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
SQL Server Profiler - 9e2cc07d-4491-46e3-a4cb-c4be1dab3bb4 webapps PK-WEBAPPS2-BK\webapps     2444 54 2011-10-28 15:52:05.217  

SQL:BatchStarting set implicit_transactions on  Cisco CRS Application  AppUser     432 53 2011-10-28 17:52:15.077  
SQL:BatchStarting INSERT INTO Complaint (ConsumerID,Status,Type,SeverityLevel,CreateDate,CreatedBy,ComplaintThrough,ContactInformation) VALUES (1234567,1,1,1,GETDATE(),'System',1,'IVR') Cisco CRS Application  AppUser     432 53 2011-10-28 17:52:15.077  
SQL:BatchStarting IF @@TRANCOUNT > 0 ROLLBACK TRAN Cisco CRS Application  AppUser     432 53 2011-10-28 17:52:15.077  
SQL:BatchStarting set implicit_transactions off  Cisco CRS Application  AppUser     432 53 2011-10-28 17:52:16.200  

In the SQL Profiler logs we see that when we execute our query the "implicit_transaction" parameter is set to "on" and transaction is rolled back. As per DBA this parameter should remain off (by default) to write the transaction properly.

[Clifford McGlamry]

Does it set the implicit_transaction parameter to on when you run it on the DB machine?

You could try adding the SQL command COMMIT at the end of the statement:

INSERT INTO Complaint (ConsumerID,Status,Type,SeverityLevel,CreateDate,CreatedBy,ComplaintThrough,ContactInformation) VALUES (1234567,1,1,1,GETDATE(),'System',1,'IVR');

GO

COMMIT TRANSACTION;

GO

Problem is that when you are working through ODBC, sometimes something like this won't work.  In my personal experience, often problems like this are best solved by setting the whole thing up as a stored procedure.  When you execute the stored procedure, you get the same results, but due to differences in the way the whole thing functions, it may work where the method you're using now may not.

I don't know why it would be setting implicit_transaction.  Could it possibly be in the setup you did on the ODBC driver you are using?

[Gergely Szabo]

Hi, I'm not quite sure you can do anything with the implicit transactions - that's why they're implicit :-)

To the original poster: can you please upload the script or at least the step that's generating the problems?

[Clifford McGlamry]

There are commands that can be used to turn them on and off.  Problem is that it's being turned on by something outside the SQL statement he's submitting and then turned back off.  That means something in the setup of either the ODBC client or on the server itself. 

A stored procedure may work around this, but we've yet to get a response back on that approach.

Cliff

[rehan_uet]

Let me explaint the actual situation; I have added a node in my script to write the data into database, in the actual implementation we'll be getting the ConsumerID & Complaint Type from the customer and we'll populate other columns with dummy entries. But just to test the query we are using some values for ConsumerID (123456) & Complaint Type (1) and for other columns.

When we test the execution of following query in the script; the data is not written in the database and database is rolling back and observed reason is that "set implicit_transactions on " 

INSERT INTO Complaint (ConsumerID,Status,Type,SeverityLevel,CreateDate,CreatedBy,ComplaintThrough,ContactInformation) VALUES (1234567,1,1,1,GETDATE(),'System',1,'IVR')

[Gergely Szabo]

Hi, what happens if you run this insert for instnace in SQL studio or Query Analyzer or (your favorite IDE)?

I believe implicit transactions on is not the problem; they do the rollback when while running this insert a problem occurs. What kind of a problem I don't know but there must be something that triggers the rollback. For instance, a primary/foreign key violation or something like that.

G.

[rehan_uet]

This query runs perfectly fine in the SQL studio and inserts the data into the pointed table and does not set implicit_transactions on.

[Clifford McGlamry]

Were you able to try doing it as a stored procedure?  Or is this something you'd need some help on? 

It's pretty easy to set up, but can make a big difference in your IVR script. 

[Sheraz Saeed Lodhi]

hi clifford this issue is happening with Read node as well. Test dispays 1 raw return while when we run the script and Get the Data it drops the call.

[Clifford McGlamry]

Again, tough to troubleshoot without being able to see your script. 

Are you calling your data as a stored procedure?  Do you have a dummy table that is formatted to exactly match the schema of the return values if you're not retrieving all fields?  Have you run the script in debug mode to see what error you're getting when you attempt to execute that step?

There are error handling steps you can add to force a call to jump to a certain place in the script when you encounter an error.  If your script doesn't have one, it will simply terminate the call. 

Lots of stuff you can do to troubelshoot this, but I can't get more specific without more information. 

Cliff