Update a table with Trigger on Table

[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.

[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.

[LeonHo]

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.

[ray.wurlod]

Run the ODBC Connector stage in Sequential mode. Set this property on the Advanced tab in stage properties.

[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

[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).

[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?

[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.

[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...