October 29, 2003 at 11:11 am
what is wrong wid this procedure, when i execute this via java it execute fine once and then second time the database is locked when i fetch data from the updated table.
CREATE PROCEDURE sp_updateNodeName
@pie_id integer
AS
declare @count integer ,@pie_rep varchar(10) , @rep_id as varchar(10),@rep_count integer , @rep_name varchar(15) , @i integer , @pie_split_by varchar(5)
select @count = count(node_id) from tbl_pie_nodes where pie_id =@pie_id
select @pie_rep = pie_rep_id , @pie_split_by = isnull(pie_split_by,'NO') from tbl_pie_mast where pie_id = @pie_id
set @rep_id = substring(@pie_rep,4,len(@pie_rep))
set @rep_count = convert(integer ,@rep_id )
set @i = 0
if(@pie_split_by <> 'NO')
begin
while(@i < @count)
begin
set @rep_count = @rep_count +1
if(@pie_split_by = 'CM')
set @rep_name = convert(varchar(3),DateAdd(mm,-@i,getDate()),100)
else if(@pie_split_by = 'M')
set @rep_name = convert(varchar(7),DateAdd(mm,-@i,getDate()),100) + ' - '+ convert(varchar(7),DateAdd(mm,-(@i+1),getDate()),100)
else if(@pie_split_by = 'D')
begin set @rep_name = Convert(Varchar(12),DateAdd(dd,@i,getDate()),103) end
begin
set @rep_id = 'PIE'+convert(varchar(3) , @rep_count )
begin transaction InProc
update tbl_pie_nodes set node_name = @rep_name , node_pie_disp_name = @rep_name where pie_id = @pie_id and report_id = @rep_id
commit transaction InProc
end
set @i = @i+1
end
end
the following sql locks the table tbl_pie_nodes
select node_id , node_name , node_pie_disp_name, report_id, node_level ,node_parent_id from tbl_pie_nodes where pie_id = 31 order by node_id
October 29, 2003 at 3:08 pm
Are you by chance getting an error on the update that is not getting rolled back and thus locking the table?
begin
set @rep_id = 'PIE'+convert(varchar(3) , @rep_count )
begin transaction InProc
update tbl_pie_nodes set node_name = @rep_name , node_pie_disp_name = @rep_name where pie_id = @pie_id and report_id = @rep_id
IF @@ERROR != 0
BEGIN
RAISERROR('Unable to update value',16,1)
ROLLBACK TRAN InProc
END
ELSE
BEGIN
commit transaction InProc
END
end
set @i = @i+1
end
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 30, 2003 at 2:59 am
Thanks for the reply thou that did not help much, when i call the procedure thru sql anyalyzer it works fine but when i call it thru java and try to select value from the updated table immediatly , the procedure is still holding the lock and hence the select is waiting for the block to clear. I have to manually kill the process in the enterprise manager.
October 30, 2003 at 7:41 pm
Can you check with Profiler what is going on? It sounds like you are having a problem with not getting the transaction committed.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 31, 2003 at 2:26 am
Yes that was the problem, the after execution of the procedure the connection wasnt commited and hence the table was blocked. Thanks for your help.
cheers
have a good day.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply