Results 1 to 4 of 4

Thread: Table Locking?

  1. #1
    Sam Abraham Guest

    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



  2. #2
    Vikram Swami Guest

    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 &#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



  3. #3
    Sam Abraham Guest

    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 &#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



  4. #4
    Sam Abraham Guest

    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 &#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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •