Page 1 of 1
Update a table with Trigger on Table
Posted: Tue Feb 11, 2014 2:26 pm
by Roopanwita
Hi,
I am trying to load a table(SQL Server 2008) using ODBC Connector Stage with load strategy : Update then Insert.
I have Trigger written on the table(Table I am trying to Update), which updates another table i.e. I am trying to Update Table_A and Table_A has a trigger Trig_A which updates Table_B .
With load strategy Update / Insert process is failing with Error :
SQLSTATE = 24000: Native Error Code = 0: Msg = [Microsoft][SQL Server Native Client 10.0]Invalid cursor state
When I perform the update from SQL server Management Studio , it is working fine (it is giving trigger updates also)
Is there a way to update the table without disabling the trigger?
Thank you for help.
Posted: Tue Feb 11, 2014 2:40 pm
by asorrell
Is your ODBC Connector running in parallel? The invalid cursor state may be caused by triggered updates colliding on table B.
How many records are you processing? Can you try single-threading the ODBC Connector and see if that fixes the problem? If it does, then you've confirmed its a parallelism issue.
Having the same issue with ODBC Connector
Posted: Tue Feb 18, 2014 4:46 pm
by LeonHo
asorrell wrote: Can you try single-threading the ODBC Connector and see if that fixes the problem?.
Hi, I am having the same error while trying to update my target.
Is there a way to force single threading in ODBC Connector stage? I do not find this option in the tabs. We're running version 8.7 of DataStage.
Posted: Tue Feb 18, 2014 7:41 pm
by ray.wurlod
Run the ODBC Connector stage in Sequential mode. Set this property on the Advanced tab in stage properties.
I think I'm missing something?
Posted: Wed Feb 19, 2014 12:20 pm
by LeonHo
Thanks for the reply. It doesn't look like the option is available. Please see below screenshot.
http://farm3.staticflickr.com/2847/1263 ... 6637_b.jpg
Posted: Wed Feb 19, 2014 12:34 pm
by asorrell
It is a "stage" option, not a "link" option. In the upper left-hand corner click on the stage icon in the picture. Then look at the advanced tab for execution mode.
(Click on link in picture to go back to other options).
Same error experienced after setting to sequential mode
Posted: Wed Feb 19, 2014 2:28 pm
by LeonHo
Hi,
Thanks for that. I am able to set the stage to Sequential now. However the same "Invalid Cursor State" still occurs, and no rows are updated in the target.
Could this be an issue with the driver?
DB Trigger was the cause
Posted: Thu Feb 20, 2014 8:37 am
by LeonHo
Hi,
We were able to get a hold of the DBA to disable the triggers. The DataStage job worked perfectly when the update triggers are inactive. In terms of # records I was only updating 5 records so I don't think it was a volume issue. I tried both a DS generated SQL and a user sql with the same results.
Posted: Thu Feb 20, 2014 9:57 am
by asorrell
At this point you might want to call your service provider, you might actually have a driver bug. If you do resolve it, please post...
