10-27-2011 07:56 AM - edited 03-14-2019 08:47 AM
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.
10-27-2011 07:41 PM
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
10-28-2011 07:02 AM
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.
10-28-2011 09:05 AM
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?
10-28-2011 09:38 AM
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?
10-28-2011 09:48 AM
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
10-28-2011 10:32 AM
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')
10-28-2011 10:57 AM
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.
10-28-2011 01:15 PM
This query runs perfectly fine in the SQL studio and inserts the data into the pointed table and does not set implicit_transactions on.
10-28-2011 02:15 PM
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.
11-02-2011 05:23 AM
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.
11-02-2011 06:12 AM
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
Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: