-
Table Locking?
Gurus,
I am trying to execute this stored procedure when I try to change all occurences of a field in a table.
(
@Dept char(8),
@DDept char(8)
)
As
Set NoCOUNt On
Begin
Select '@DDept'
update phodept set fo_dept = @Ddept
where fo_dept = @Dept
update phone set fo_dept = @Ddept where fo_dept = @Dept
End
GO
The table/database is being used by others, generally in a read only mode.
via a VB 5.0 FE program.
The Stored procedure, when it is invoked, just hangs like it is waiting for exclusive use of the table.
Is there a way around it, without doing major surgery on the VB code?
Thanks.
Sam
-
Table Locking? (reply)
Try the following : This may help as the table locking will be reduced considerably
(
@Dept char(8),
@DDept char(8)
)
As
declare @count int
select @count = 1
Set NOCOUNT On
Begin
Select '@DDept'
set rowcount 100
While @count <> 0
BEGIN
update phodept set fo_dept = @Ddept
where fo_dept = @Dept
select @count = @@ROWCOUNT
END
While @count <> 0
BEGIN
update phone set fo_dept = @Ddept where fo_dept = @Dept
select @count = @@ROWCOUNT
END
set rowcount 0
End
GO
------------
Sam Abraham at 2/7/2002 2:41:48 PM
Gurus,
I am trying to execute this stored procedure when I try to change all occurences of a field in a table.
(
@Dept char(8),
@DDept char(8)
)
As
Set NoCOUNt On
Begin
Select '@DDept'
update phodept set fo_dept = @Ddept
where fo_dept = @Dept
update phone set fo_dept = @Ddept where fo_dept = @Dept
End
GO
The table/database is being used by others, generally in a read only mode.
via a VB 5.0 FE program.
The Stored procedure, when it is invoked, just hangs like it is waiting for exclusive use of the table.
Is there a way around it, without doing major surgery on the VB code?
Thanks.
Sam
-
Table Locking? (reply)
Vikram,
Thanks for the suggestion. However, when we invoke it from the FE which is a VB 5.0 program, it still times out. Your script updates a 100 rows at a time but is it possible it is still locking the table for the duration.
Sam
Vikram Swami at 2/8/2002 5:45:23 AM
Try the following : This may help as the table locking will be reduced considerably
(
@Dept char(8),
@DDept char(8)
)
As
declare @count int
select @count = 1
Set NOCOUNT On
Begin
Select '@DDept'
set rowcount 100
While @count <> 0
BEGIN
update phodept set fo_dept = @Ddept
where fo_dept = @Dept
select @count = @@ROWCOUNT
END
While @count <> 0
BEGIN
update phone set fo_dept = @Ddept where fo_dept = @Dept
select @count = @@ROWCOUNT
END
set rowcount 0
End
GO
------------
Sam Abraham at 2/7/2002 2:41:48 PM
Gurus,
I am trying to execute this stored procedure when I try to change all occurences of a field in a table.
(
@Dept char(8),
@DDept char(8)
)
As
Set NoCOUNt On
Begin
Select '@DDept'
update phodept set fo_dept = @Ddept
where fo_dept = @Dept
update phone set fo_dept = @Ddept where fo_dept = @Dept
End
GO
The table/database is being used by others, generally in a read only mode.
via a VB 5.0 FE program.
The Stored procedure, when it is invoked, just hangs like it is waiting for exclusive use of the table.
Is there a way around it, without doing major surgery on the VB code?
Thanks.
Sam
-
Table Locking? (reply)
Vikram,
Thanks for pointing us in the right direction.
The stored procedure works with one additional modification involving bein tran and commit tran.
See below (the first table is really quite small so we do not use your technique there)
(
@Dept char(8),
@DDept char(8)
)
As
declare @count int
Select @count = 1
Set NoCOUNt On
Begin
Select '@DDept'
set rowcount 100
update phodept set fo_dept = @Ddept
where fo_dept = @Dept
While @count <> 0
Begin
begin tran
update phone set fo_dept = @Ddept where fo_dept = @Dept
select @count = @@RowCount
commit tran
End
set rowcount 0
End
GO
------------
Vikram Swami at 2/8/2002 5:45:23 AM
Try the following : This may help as the table locking will be reduced considerably
(
@Dept char(8),
@DDept char(8)
)
As
declare @count int
select @count = 1
Set NOCOUNT On
Begin
Select '@DDept'
set rowcount 100
While @count <> 0
BEGIN
update phodept set fo_dept = @Ddept
where fo_dept = @Dept
select @count = @@ROWCOUNT
END
While @count <> 0
BEGIN
update phone set fo_dept = @Ddept where fo_dept = @Dept
select @count = @@ROWCOUNT
END
set rowcount 0
End
GO
------------
Sam Abraham at 2/7/2002 2:41:48 PM
Gurus,
I am trying to execute this stored procedure when I try to change all occurences of a field in a table.
(
@Dept char(8),
@DDept char(8)
)
As
Set NoCOUNt On
Begin
Select '@DDept'
update phodept set fo_dept = @Ddept
where fo_dept = @Dept
update phone set fo_dept = @Ddept where fo_dept = @Dept
End
GO
The table/database is being used by others, generally in a read only mode.
via a VB 5.0 FE program.
The Stored procedure, when it is invoked, just hangs like it is waiting for exclusive use of the table.
Is there a way around it, without doing major surgery on the VB code?
Thanks.
Sam
