Stored Procedure locks tempdb 
Author Message
 Stored Procedure locks tempdb

I have been given the assignment to fix the following stored procedure.
We are running SQL 7, SP1. Whenever this SP runs it completely locks the
tempdb. I can't figure out why it is doing this. Please take a look at
the code. Any suggestions will be appreciated.
Thanks.


AS
begin transaction







Update mv_SalesGeography
Set Distance = 9999
Where distance is null

Update mv_SalesGeography
Set Assignment = 0



        BEGIN
        create Table #GMax1
        (store_id char(6),
        Geography char(10),
        Distance Float,
        Sales Int)

        insert #GMax1
        Select Store_id,Geography,Distance,sales
        from mv_SalesGeography

             and     Store_id   in    ( Select  DISTINCT Store_id
                                                 From
mv_storeLocations

                                                 and      active = '1' )
             order by Geography,sales DESC , Distance ,Store_id

             declare Temp_Cursor scroll cursor
             for
             select store_id,Geography,Distance,Sales
             from  #GMax1

            open Temp_Cursor



              begin
                update mv_SalesGeography







                              begin

                              end
                           else
                              begin



                              end

               end
        close Temp_Cursor
             deallocate Temp_Cursor

             Drop table #GMax1
      END     Else
      BEGIN
             create Table #GMaxsum
        (store_id char(10),
         orderby  float)

        insert #GMaxsum
        select l.store_id,l.sales / (select sum(sales)
                                     from nsa.dbo.mv_salesgeography c
                                      where         c.store_id =

        from nsa.dbo.mv_storelocations l

        create Table #GMax2
        (store_id char(6),
        Geography char(10),
        Distance Float,
        Sales Int,
        neworder Float)

        insert #GMax2
        Select s.Store_id,s.Geography,s.Distance,s.sales , s.sales *
g.orderby as neworder
        from mv_SalesGeography s,#GMaxsum g

             and     s.store_id=g.store_id
             and     s.Store_id   in    ( Select  DISTINCT Store_id
                                                   From
mv_storeLocations

                                                   and      active = '1'
)
            order by s.Geography, neworder desc, s.Distance

             declare Temp_Cursor insensitive scroll cursor
             for
             select store_id, Geography, Distance, Sales, neworder
             from  #GMax2
        order by Geography, neworder desc, Distance

            open Temp_Cursor



              begin
                update mv_SalesGeography







                              begin

                              end
                           else
                              begin



                              end

               end
        close Temp_Cursor
             deallocate Temp_Cursor
        Drop table #GMaxsum
             Drop table #GMax2

      END

begin
        Rollback transaction
End
Else
begin
        commit transaction
End

Sent via Deja.com http://www.***.com/
Before you buy.



Tue, 30 Jul 2002 03:00:00 GMT
 Stored Procedure locks tempdb

I solved this myself by putting in commits after each table create.

Thanks.


Quote:

> I have been given the assignment to fix the following stored
procedure.
> We are running SQL 7, SP1. Whenever this SP runs it completely locks
the
> tempdb. I can't figure out why it is doing this. Please take a look at
> the code. Any suggestions will be appreciated.
> Thanks.

> CREATE PROCEDURE [sp_mv_Assign]

> AS
> begin transaction







> Update mv_SalesGeography
> Set Distance = 9999
> Where distance is null

> Update mv_SalesGeography
> Set Assignment = 0



>         BEGIN
>    create Table #GMax1
>    (store_id char(6),
>    Geography char(10),
>    Distance Float,
>    Sales Int)

>    insert #GMax1
>    Select Store_id,Geography,Distance,sales
>    from mv_SalesGeography

>              and     Store_id   in    ( Select  DISTINCT Store_id
>                                                  From
> mv_storeLocations

>                                                  and      active = '1'
)
>              order by Geography,sales DESC , Distance ,Store_id

>              declare Temp_Cursor scroll cursor
>              for
>              select store_id,Geography,Distance,Sales
>              from  #GMax1

>             open Temp_Cursor




>               begin
>            update mv_SalesGeography








>                               begin

>                               end
>                            else
>                               begin



>                               end

>                end
>    close Temp_Cursor
>              deallocate Temp_Cursor

>              Drop table #GMax1
>       END      Else
>       BEGIN
>              create Table #GMaxsum
>         (store_id char(10),
>          orderby  float)

>         insert #GMaxsum
>    select l.store_id,l.sales / (select sum(sales)
>                                      from nsa.dbo.mv_salesgeography c
>                                       where         c.store_id =

>         from nsa.dbo.mv_storelocations l

>    create Table #GMax2
>    (store_id char(6),
>    Geography char(10),
>    Distance Float,
>    Sales Int,
>         neworder Float)

>    insert #GMax2
>    Select s.Store_id,s.Geography,s.Distance,s.sales , s.sales *
> g.orderby as neworder
>    from mv_SalesGeography s,#GMaxsum g

>              and     s.store_id=g.store_id
>              and     s.Store_id   in    ( Select  DISTINCT Store_id
>                                                    From
> mv_storeLocations

>                                                    and      active =
'1'
> )
>             order by s.Geography, neworder desc, s.Distance

>              declare Temp_Cursor insensitive scroll cursor
>              for
>              select store_id, Geography, Distance, Sales, neworder
>              from  #GMax2
>    order by Geography, neworder desc, Distance

>             open Temp_Cursor




>               begin
>            update mv_SalesGeography








>                               begin

>                               end
>                            else
>                               begin



>                               end

>                end
>    close Temp_Cursor
>              deallocate Temp_Cursor
>    Drop table #GMaxsum
>              Drop table #GMax2

>       END

> begin
>    Rollback transaction
> End
> Else
> begin
>    commit transaction
> End

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Tue, 30 Jul 2002 03:00:00 GMT
 Stored Procedure locks tempdb
I solved this myself by putting in commits after each table create.

Thanks.


Quote:

> I have been given the assignment to fix the following stored
procedure.
> We are running SQL 7, SP1. Whenever this SP runs it completely locks
the
> tempdb. I can't figure out why it is doing this. Please take a look at
> the code. Any suggestions will be appreciated.
> Thanks.

> CREATE PROCEDURE [sp_mv_Assign]

> AS
> begin transaction







> Update mv_SalesGeography
> Set Distance = 9999
> Where distance is null

> Update mv_SalesGeography
> Set Assignment = 0



>         BEGIN
>    create Table #GMax1
>    (store_id char(6),
>    Geography char(10),
>    Distance Float,
>    Sales Int)

>    insert #GMax1
>    Select Store_id,Geography,Distance,sales
>    from mv_SalesGeography

>              and     Store_id   in    ( Select  DISTINCT Store_id
>                                                  From
> mv_storeLocations

>                                                  and      active = '1'
)
>              order by Geography,sales DESC , Distance ,Store_id

>              declare Temp_Cursor scroll cursor
>              for
>              select store_id,Geography,Distance,Sales
>              from  #GMax1

>             open Temp_Cursor




>               begin
>            update mv_SalesGeography








>                               begin

>                               end
>                            else
>                               begin



>                               end

>                end
>    close Temp_Cursor
>              deallocate Temp_Cursor

>              Drop table #GMax1
>       END      Else
>       BEGIN
>              create Table #GMaxsum
>         (store_id char(10),
>          orderby  float)

>         insert #GMaxsum
>    select l.store_id,l.sales / (select sum(sales)
>                                      from nsa.dbo.mv_salesgeography c
>                                       where         c.store_id =

>         from nsa.dbo.mv_storelocations l

>    create Table #GMax2
>    (store_id char(6),
>    Geography char(10),
>    Distance Float,
>    Sales Int,
>         neworder Float)

>    insert #GMax2
>    Select s.Store_id,s.Geography,s.Distance,s.sales , s.sales *
> g.orderby as neworder
>    from mv_SalesGeography s,#GMaxsum g

>              and     s.store_id=g.store_id
>              and     s.Store_id   in    ( Select  DISTINCT Store_id
>                                                    From
> mv_storeLocations

>                                                    and      active =
'1'
> )
>             order by s.Geography, neworder desc, s.Distance

>              declare Temp_Cursor insensitive scroll cursor
>              for
>              select store_id, Geography, Distance, Sales, neworder
>              from  #GMax2
>    order by Geography, neworder desc, Distance

>             open Temp_Cursor




>               begin
>            update mv_SalesGeography








>                               begin

>                               end
>                            else
>                               begin



>                               end

>                end
>    close Temp_Cursor
>              deallocate Temp_Cursor
>    Drop table #GMaxsum
>              Drop table #GMax2

>       END

> begin
>    Rollback transaction
> End
> Else
> begin
>    commit transaction
> End

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Tue, 30 Jul 2002 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. ODBC Stored Procedure Locks in TEMPDB ???

2. tempdb filling with odbc stored procedures

3. Stored Procedures in tempdb

4. Stored Procedures in tempdb

5. Performance of SQL 7.0 : Stored procedure involving excessive usage of tempdb

6. Sue: Store Procedure in Master to create TempDB Users

7. tempdb and odbc stored procedure

8. Accessing tempdb from a stored procedure

9. VB execution of Stored Procedures fills TempDB

10. Accessing tempdb from stored procedure

11. Calling a Java Stored Procedure from another Java Stored Stored Procedure

12. Row Locking In a Stored Procedure


 
Powered by phpBB® Forum Software