Dead Lock

Hello !!!

I have 2 transactions, the first one has MANY updates to the table A and it finishes with a commit or rollback (ONLY AT THE END), the second one has only one insert into the table A that finishes with a commit or rollback, the problem is that the update process takes a long time to finish, and the insert process could be thrown during the first process, there's where I get everything locked cause the table A is locked and my java aplication gets stuck.

Note: When I execute each transaction independient I have no problems.

Is there any possibility to lock table A completly for the first transaction and release It for second one ??

Could you give me any suggestion of what to do step by step ?

Thanks !!!Are you running these processes by submitting SQL commands from your Java App, or are you just executing existing Stored Procedures?|||begin tran
select 1 from tableA (tablock) where 1=2
update...
if @.@.error != 0 begin
...
rollback tran
return (1)
end
...
commit tran

DBSTATUS_UNAVAILABLE error

I have a pretty complex Data Flow that finishes with OLE DB Command. This calls a Stored Procedure with parameters (60-70 of them). The packgaes compiles OK and in run time, I get error below. When I go ahead and edit the field - remove it - save the package - add it back and re-run the package - it generates same error for a different field. When I re-assign all fields and re-run it again - it starts over to give me same error.

Help!

Error: 0xC0202009 at Load Divisions, Insert New Division Data record [82749]: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".
Error: 0xC020901C at Load Divisions, Insert New Division Data record [82749]: There was an error with input column "exists_divid" (86315) on input "OLE DB Command Input" (82754). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Load Divisions, Insert New Division Data record [82749]: The "input "OLE DB Command Input" (82754)" failed because error code 0xC020906E occurred, and the error row disposition on "input "OLE DB Command Input" (82754)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047022 at Load Divisions, DTS.Pipeline: The ProcessInput method on component "Insert New Division Data record" (82749) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread2" has exited with error code 0xC0209029.
Error: 0xC0047039 at Load Divisions, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Error: 0xC0047039 at Load Divisions, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

heh...it works now. It was varchar source column and target INT column. The issue is it never errored this column - but told me about some other issue not even related.

Once I manually matched each field and fixed it - it started going through.

It's gotta be fixed...

|||Also - the same error message appears when your OLE DB command invalidates constraints - for example I experienced this error when I was trying to do UPDATE of a Column with a NULL value - and the column had not null constraint.

Thanks Microsoft for such a "helpful" error message!

DBSTATUS_UNAVAILABLE error

I have a pretty complex Data Flow that finishes with OLE DB Command. This calls a Stored Procedure with parameters (60-70 of them). The packgaes compiles OK and in run time, I get error below. When I go ahead and edit the field - remove it - save the package - add it back and re-run the package - it generates same error for a different field. When I re-assign all fields and re-run it again - it starts over to give me same error.

Help!

Error: 0xC0202009 at Load Divisions, Insert New Division Data record [82749]: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".
Error: 0xC020901C at Load Divisions, Insert New Division Data record [82749]: There was an error with input column "exists_divid" (86315) on input "OLE DB Command Input" (82754). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Load Divisions, Insert New Division Data record [82749]: The "input "OLE DB Command Input" (82754)" failed because error code 0xC020906E occurred, and the error row disposition on "input "OLE DB Command Input" (82754)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047022 at Load Divisions, DTS.Pipeline: The ProcessInput method on component "Insert New Division Data record" (82749) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread2" has exited with error code 0xC0209029.
Error: 0xC0047039 at Load Divisions, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Error: 0xC0047039 at Load Divisions, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

heh...it works now. It was varchar source column and target INT column. The issue is it never errored this column - but told me about some other issue not even related.

Once I manually matched each field and fixed it - it started going through.

It's gotta be fixed...

|||Also - the same error message appears when your OLE DB command invalidates constraints - for example I experienced this error when I was trying to do UPDATE of a Column with a NULL value - and the column had not null constraint.

Thanks Microsoft for such a "helpful" error message!