Job Hanging while updating sql server table
Moderators: chulett, rschirm, roy
Job Hanging while updating sql server table
Hi All,
I am trying to select and update the same table in the job and while running the job it is keep on running.
I am trying to update 3 different tables with 3 different odbc connector stages .
job design is like this:
odbc -->Transformer-->odbc
in the first odbc using select statement and in the second odbc using update statement on same table.
I ran with out any locks till last week..while i run today i am able to see the same in development.
Looking forward for any suggestions on this.
I am trying to select and update the same table in the job and while running the job it is keep on running.
I am trying to update 3 different tables with 3 different odbc connector stages .
job design is like this:
odbc -->Transformer-->odbc
in the first odbc using select statement and in the second odbc using update statement on same table.
I ran with out any locks till last week..while i run today i am able to see the same in development.
Looking forward for any suggestions on this.
RD
The subject line indicates SQL Server. With sufficient access rights, you can see the individual threads and locks from the SQL Server Management Studio program.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Where are the locks coming from? If it is from the job itself, have you tried running on a single node as previously noted? That would eliminate the target as the source of the locks but would still lock if they are being generated on the source side.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
However, assuming the job itself is not the problem and the record lock is from something external, setting Isolation Level to "Read Uncommitted" should work.
If the entire table is locked, then you are probably out of luck, since Read Uncommitted only allows you to "get around" record level locks. Note that this will allow you to read "dirty" data, so use with caution.
If the entire table is locked, then you are probably out of luck, since Read Uncommitted only allows you to "get around" record level locks. Note that this will allow you to read "dirty" data, so use with caution.