Four update SQLs in one DS job

[in_finity307]

Hi,

I need to execute 4 update SQLs on the same table in SQL Server database in a DS job. I cannot put the 4 Update SQLS together in one ODBC Connector stage. I want the SQLS to execute one after the other because if the SQLs are fired simulatneously, then a deadlock of table occurs. If I use 4 different ODBC connector stages one for each SQL, then too the SQLS would get fired simulatneously and the table would get deadlocked. Can you suggest me a way to do this?

[chulett]

Sorry, but what exactly do you mean by "4 update SQLs on the same table"?

[in_finity307]

I mean that there are 4 conditional updates on the table. I can execute them on the database using the CASE statement, but ODBC connector does not accept the CASE synatx.

[chulett]

So... again... based on conditions, you want to update different combinations of columns? Will you always execute all four or always just one of the four or something in-between?

[ray.wurlod]

Commit immedately (transaction size = 1) and keep your transaction isolation level to the minimum possible setting. Otherwise write all the updates to a staging area, and run another job to effect all the updates in a single run.

[priyadarshikunal]

You are on 8.x for windows. You should have a stage called SQL Server Enterprise stage. You might be able to use Case statement in that stage.

Any reason for not using that?

[DrPhys]

I agree with Ray's comments! In my onion it's best to push off the updates to subsequent jobs but there is also the option of using a DRS stage. This stage is very tricky but will allow you to perform multiple SQL statements from 1 stage... just a thought!

[in_finity307]

Thanks. I used the SQL Enterprise stage and the CASE statement worked fine in it.

[priyadarshikunal]

If the problem is resolved then please mark this post as Resolved using the big button on top of the page.