February 22, 2011 at 12:08 am
Hi,
I have an OLEDB command which performs a row by row insert into a database. When the command runs it does not insert any value and just locks the table. Is there any reason why this occurs.
February 22, 2011 at 12:37 am
There's always a reason, though with that amount of information it's not easy to say more.
That sounds like a very slow way to insert records - can you describe the scenario a little so that we can understand why you are doing this?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 22, 2011 at 5:54 am
There is a merge join that filters out rows to update and insert; The update works fine and as soon as insert happens the rows dont get inserted and table is locked with no select also possible on the table.
February 22, 2011 at 6:12 am
Add a Conditional Split after the MERGE JOIN to break your pipeline into two outputs: updates (Output1) and inserts (Output2).
Send Output1 to the OLEDB Command - as currently.
Output2 can map directly into the table destination.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 22, 2011 at 11:56 am
Maybe there is a blocking issue because you are updating and inserting in the same table at the same time, while one of the processes has a table lock?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 22, 2011 at 10:40 pm
Is there any workaround for the blockage due to update, not allowing any insert.
February 22, 2011 at 11:36 pm
While selecting the rows from the table I used a With NoLOCK option and it started working. Seems the select had set a lock on the table. Will using a NOLOCK option have any other effect?
February 22, 2011 at 11:36 pm
ns.sharath (2/22/2011)
Is there any workaround for the blockage due to update, not allowing any insert.
Do a set based insert with the OLE DB Destination Fast Load. Write the updates to a staging table.
After the dataflow has finished, update the destination with a set based update.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 23, 2011 at 12:06 am
ns.sharath (2/22/2011)
While selecting the rows from the table I used a With NoLOCK option and it started working. Seems the select had set a lock on the table. Will using a NOLOCK option have any other effect?
Yes, you can have dirty reads.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply