Table Locking?

[Sam Abraham]

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

[Vikram Swami]

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 &#39;@DDept&#39;
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

[Sam Abraham]

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 &#39;@DDept&#39;
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 &#39;@DDept&#39;
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

[Sam Abraham]

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 &#39;@DDept&#39;

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 &#39;@DDept&#39;
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 &#39;@DDept&#39;
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