Re: Multiple problems with a running job

From: Scott M. Lyon (scott.RED.lyon.WHITE_at_rapistan.BLUE.com)
Date: 11/03/04 

Date: Wed, 3 Nov 2004 11:21:08 -0500

I can, but it's a bit on the long side, so consider yourself warned (I've
included the narrative in comments):

ALTER PROCEDURE dbo.BUILD_FROM_LEGACY_DATA_PROC
(
 @ProjectNumber varchar(6)
AS

-- This stored procedure is designed to copy data from a database of legacy
data (PROJECTORDERSYSTEM) into the new table structure (to be run on the new
database)
-- Basically we have a bunch of spares lists, where the lists are made up of
segments, and each segment is made up of materials. 

--
-- The legacy data consists of Spare Parts lists, where the tables are as
follows:
--    SPARESUTILITY - contains the list number and project number for the
list (has a key SPARESKEYID)
--    SPAREPARTSMASTER - This contains the "header" information for the
list - things such as customer information, list status, etc.
--    SPAREPARTSPARTS - This contains all parts on the list, plus a lot of
information on each part - note: although a part may be on a list multiple
times, this will only have one record per part
--    SEGMENTLISTINCLUDED - This is the list of all segments included in the
list
--    SPARESEGMENTMARK - This maps the parts to the segments - this is where
it can designate a part being on a list multiple times
--
-- As for inserting to the new database, I already had stored procedures for
inserting all of this data, so instead of reinventing the wheel, I'm just
calling those stored procedures here
-- Most (if not all) of my stored procedures return an OUTPUT parameter
called @err_message which is set to 'Done' if successful, or the error if
there's an problem in the procedure
--
-- The only exception to this is at the end of this where it calculates the
"Recommended Qty" which is stored in a table mapping the list directly to
the part (so that there's only one "recommended qty"
--    per part per list, even if that part is used multiple times on
segments on the list)
DECLARE @Project_Number char(6)
DECLARE @List_Number char(2)
DECLARE @List_Description varchar(255)
DECLARE @Author char(3)
DECLARE @Target char(255)
DECLARE @Customer_Number char(6)
DECLARE @Pricing_Date smalldatetime
DECLARE @List_Status_Code char(1)
DECLARE @List_Exception_Code char(1)
DECLARE @Division_abbreviation char(2)
DECLARE @Business_Unit_abbreviation char(2)
DECLARE @err_message varchar(255)
-- Add all lists for the given project
 DECLARE SparesListCursor CURSOR LOCAL STATIC FOR
  SELECT  SPARESUTILITY.PROJECT_NUMBER AS Project_number,
   SPARESUTILITY.LIST_NUMBER AS List_number,
   SPAREPARTSMASTER.[DESCRIPTION] AS List_description,
   SPAREPARTSMASTER.CREATED_BY_OPID AS Author,
   SPAREPARTSMASTER.REQUESTED_FOR AS Target,
   SPAREPARTSMASTER.CUSTOMER_NUMBER AS Customer_Number,
   SPAREPARTSMASTER.STATUS_CODE AS List_Status_Code,
   SPAREPARTSMASTER.EXCEPTIONS_CODE AS List_Exception_Code
  FROM PROJECTORDERSYSTEM.DBO.SPAREPARTSMASTER SPAREPARTSMASTER WITH
(NOLOCK, READUNCOMMITTED)
  INNER JOIN PROJECTORDERSYSTEM.DBO.SPARESUTILITY SPARESUTILITY WITH
(NOLOCK, READUNCOMMITTED)
   ON (SPARESUTILITY.SPARESKEYID = SPAREPARTSMASTER.SPARESKEYID)
  WHERE SPARESUTILITY.PROJECT_NUMBER = @ProjectNumber
OPEN SparesListCursor
FETCH NEXT FROM SparesListCursor INTO @Project_number, @List_number,
@List_description, @Author, @Target, @Customer_Number, @List_Status_Code,
@List_Exception_Code
 WHILE(@@FETCH_STATUS=0)
  BEGIN
   EXEC dbo.NEW_SPARES_LIST_PROC @Project_number, @List_number,
@List_description, @Author, @Target, @Customer_Number, @Customer_Number,
@Customer_Number,
    @List_Status_Code, @List_Exception_Code, @Business_Unit_abbreviation,
@Division_Abbreviation, 1, @err_message OUTPUT
   FETCH NEXT FROM SparesListCursor INTO @Project_number, @List_number,
@List_description, @Author, @Target, @Customer_Number, @List_Status_Code,
@List_Exception_Code
  END
CLOSE SparesListCursor
DEALLOCATE SparesListCursor
DECLARE @Segment as varchar(2)
DECLARE @Mark_Number as int
-- Add all segments for all lists for the given project
 DECLARE SparesListSegmentCursor CURSOR LOCAL STATIC FOR
  SELECT  DISTINCT SPARESUTILITY.PROJECT_NUMBER AS Project_number,
   SPARESUTILITY.LIST_NUMBER AS List_number,
   SEGMENTLISTINCLUDED.SEG_LIST AS Segment
  FROM PROJECTORDERSYSTEM.DBO.SPAREPARTSMASTER SPAREPARTSMASTER WITH
(NOLOCK, READUNCOMMITTED)
  INNER JOIN PROJECTORDERSYSTEM.DBO.SPARESUTILITY SPARESUTILITY WITH
(NOLOCK, READUNCOMMITTED)
   ON (SPARESUTILITY.SPARESKEYID = SPAREPARTSMASTER.SPARESKEYID)
  INNER JOIN PROJECTORDERSYSTEM.DBO.SEGMENTLISTINCLUDED SEGMENTLISTINCLUDED
WITH (NOLOCK, READUNCOMMITTED)
   ON (SEGMENTLISTINCLUDED.SPARESKEYID = SPAREPARTSMASTER.SPARESKEYID)
  WHERE SPARESUTILITY.PROJECT_NUMBER = @ProjectNumber
OPEN SparesListSegmentCursor
FETCH NEXT FROM SparesListSegmentCursor INTO @Project_number, @List_number,
@Segment
 WHILE(@@FETCH_STATUS=0)
  BEGIN
   EXEC dbo.ADD_SEGMENT_TO_LIST_PROC @Project_number, @List_number,
@Segment, @err_message OUTPUT
   FETCH NEXT FROM SparesListSegmentCursor INTO @Project_number,
@List_number, @Segment
  END
CLOSE SparesListSegmentCursor
DEALLOCATE SparesListSegmentCursor
DECLARE @Material_number char(10)
DECLARE @Used_Qty int
DECLARE @Material_Exception_Code char(1)
DECLARE @Lead_Time int
DECLARE @Spares_Type_Code char(1)
DECLARE @Category_Code char(3)
DECLARE @Proprietary_Code char(1)
DECLARE @Critical_Level char(1)
DECLARE @Marks_Printed_Code char(1)
DECLARE @Order_Group_Code char(1)
DECLARE @Qty_Formula_Code char(1)
DECLARE @Spares_Indicator_Code char(1)
DECLARE @Formula_Qty int
DECLARE @UnitNumber varchar(50)
-- Add all materials (for all segments, for all lists, for the given
project)
 DECLARE SparesListMaterialCursor CURSOR LOCAL STATIC FOR
  SELECT  SPARESUTILITY.PROJECT_NUMBER AS Project_number,
   SPARESUTILITY.LIST_NUMBER AS List_number,
   SPAREPARTSPARTS.PART_NUMBER AS Material_number,
   SPARESEGMENTMARK.MARK_SEGMENT_NUMBER AS Segment_number,
   SPARESEGMENTMARK.MARK_NUMBER AS Mark_number,
   SPARESEGMENTMARK.QUANTITY AS Used_qty,
   SPAREPARTSPARTS.EXCEPTIONS_CODE AS Material_Exception_Code,
   SPAREPARTSPARTS.ORDERING_LEAD_TIME AS Lead_Time,
   SPAREPARTSPARTS.SPARES_TYPE_CODE AS Spares_Type_Code,
   SPAREPARTSPARTS.CATEGORY_CODE AS Category_Code,
   SPAREPARTSPARTS.PROPRIETARY_CODE AS Proprietary_Code,
   SPAREPARTSPARTS.CRITICAL_LEVEL_CODE AS Critical_Level,
   SPAREPARTSPARTS.MARKS_PRINT AS Marks_Printed_Code,
   SPAREPARTSPARTS.ORDERING_GROUP_CODE AS Order_Group_Code,
   SPAREPARTSPARTS.QUANTITY_FORMULA_CODE AS Qty_Formula_Code,
   SPAREPARTSPARTS.SPARE_PART_INDICATOR AS Spares_Indicator_Code,
   SPAREPARTSPARTS.FORMULA_QUANTITY AS Formula_Qty
  FROM PROJECTORDERSYSTEM.DBO.SPARESUTILITY SPARESUTILITY
  INNER JOIN PROJECTORDERSYSTEM.DBO.SPAREPARTSPARTS SPAREPARTSPARTS WITH
(NOLOCK, READUNCOMMITTED)
   ON (SPAREPARTSPARTS.SPARESKEYID = SPARESUTILITY.SPARESKEYID)
  INNER JOIN PROJECTORDERSYSTEM.DBO.SPARESEGMENTMARK SPARESEGMENTMARK WITH
(NOLOCK, READUNCOMMITTED)
   ON (SPARESEGMENTMARK.SPARESKEYID = SPAREPARTSPARTS.SPARESKEYID
    AND SPARESEGMENTMARK.PART_NUMBER = SPAREPARTSPARTS.PART_NUMBER)
  WHERE SPARESUTILITY.PROJECT_NUMBER = @ProjectNumber
OPEN SparesListMaterialCursor
FETCH NEXT FROM SparesListMaterialCursor INTO @Project_number, @List_number,
@material_number, @Segment, @Mark_Number, @Used_qty,
@Material_Exception_Code, @Lead_Time, @Spares_Type_Code, @Category_Code,
@Proprietary_Code, @Critical_Level, @Marks_Printed_Code, @Order_Group_Code,
@Qty_Formula_Code, @Spares_Indicator_Code, @Formula_Qty
 WHILE(@@FETCH_STATUS=0)
  BEGIN
   SET @UnitNumber = ''
   SET @Spares_Indicator_Code = ISNULL(@Spares_Indicator_Code,'')
   EXEC dbo.ADD_MATERIAL_TO_SPARES_LIST_nocheck_PROC @Project_number,
@List_number, @material_number, @Segment, @Mark_number, @Used_qty, 0,
@Formula_Qty, @UnitNumber, @err_message OUTPUT
-- This stored procedure returns 'Done' in @err_message if it completes
successfully, or an error if not, and then continues processing accordingly
   IF @err_message <> 'Done'
    BEGIN
     IF @err_message = 'Segment not selected or found'
      BEGIN
       EXEC dbo.ADD_SEGMENT_TO_LIST_PROC @Project_number, @List_number,
@Segment, @err_message OUTPUT
       IF @err_message <> 'Done'
        BEGIN
         Print 'ADD_SEGMENT_TO_LIST_PROC error:' + ISNULL(@err_message,'')
         Print ' Add segment to project ' + ISNULL(@project_number,'NULL') +
', segment:' + ISNULL(@Segment,'NULL') + ', list:' +
ISNULL(@list_number,'NULL')
        END
       ELSE
        BEGIN
         EXEC dbo.ADD_MATERIAL_TO_SPARES_LIST_PROC @Project_number,
@List_number, @material_number, @Segment, @Mark_Number, @Used_qty, 0,
@Material_Exception_Code, @Lead_Time, @Spares_Type_Code, @Category_Code,
@Proprietary_Code, @Critical_Level, @Marks_Printed_Code, @Order_Group_Code,
@Qty_Formula_Code, @Spares_Indicator_Code, @Formula_Qty, @UnitNumber,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, @err_message OUTPUT
         IF @err_message <> 'Done'
          BEGIN
           Print 'ADD_MATERIAL_TO_SPARES_LIST_PROC(attempt #2) error:' +
ISNULL(@err_message,'')
           Print ' Add material:' + ISNULL(@material_number,'NULL') + ' to
project ' + ISNULL(@project_number,'NULL') + ', segment:' +
ISNULL(@Segment,'NULL') + ', list:' + ISNULL(@list_number,'NULL')
          END
        END
      END
     ELSE
      BEGIN
       Print 'ADD_MATERIAL_TO_SPARES_LIST_PROC error:' +
ISNULL(@err_message,'')
       Print ' Add material:' + ISNULL(@material_number,'NULL') + ' to
project ' + ISNULL(@project_number,'NULL') + ', segment:' +
ISNULL(@Segment,'NULL') + ', list:' + ISNULL(@list_number,'NULL')
      END
    END
   FETCH NEXT FROM SparesListMaterialCursor INTO @Project_number,
@List_number, @material_number, @Segment, @Mark_Number, @Used_qty,
@Material_Exception_Code, @Lead_Time, @Spares_Type_Code, @Category_Code,
@Proprietary_Code, @Critical_Level, @Marks_Printed_Code, @Order_Group_Code,
@Qty_Formula_Code, @Spares_Indicator_Code, @Formula_Qty
  END
CLOSE SparesListMaterialCursor
DEALLOCATE SparesListMaterialCursor
DECLARE @list_id as uniqueidentifier
DECLARE @material_id as uniqueidentifier
DECLARE @recommended_qty as int
DECLARE @Temp_Recommended_Qty as int
-- Add materials recommended qty
DECLARE SparesListMaterialRecommendedQtyCursor CURSOR LOCAL STATIC FOR
 SELECT
  SPARESUTILITY.PROJECT_NUMBER AS Project_number,
  SPARESUTILITY.LIST_NUMBER AS List_number,
  SPAREPARTSPARTS.PART_NUMBER AS Material_number,
  SUM(SPAREPARTSPARTS.RECOMENDED_QUANTITY) AS Recommended_Qty
 FROM PROJECTORDERSYSTEM.DBO.SPARESUTILITY SPARESUTILITY  WITH (NOLOCK,
READUNCOMMITTED)
 INNER JOIN PROJECTORDERSYSTEM.DBO.SPAREPARTSPARTS SPAREPARTSPARTS WITH
(NOLOCK, READUNCOMMITTED)
  ON (SPAREPARTSPARTS.SPARESKEYID = SPARESUTILITY.SPARESKEYID)
 WHERE SPARESUTILITY.PROJECT_NUMBER = @ProjectNumber
 GROUP BY
  SPARESUTILITY.PROJECT_NUMBER,
  SPARESUTILITY.LIST_NUMBER,
  SPAREPARTSPARTS.PART_NUMBER
OPEN SparesListMaterialRecommendedQtyCursor
FETCH NEXT FROM SparesListMaterialRecommendedQtyCursor INTO @Project_number,
@List_number, @material_number, @Recommended_qty
 WHILE(@@FETCH_STATUS=0)
  BEGIN
   SELECT @material_id = material_id
   FROM MATERIAL
   WHERE material_number = @material_number
   SELECT @list_id = SL.list_id
   FROM SPARES_LIST SL
   INNER JOIN PROJECT_SPARE_LISTS P
    ON P.list_id = SL.list_id
   WHERE P.project_number = @project_number AND SL.list_number =
@list_number
-- Does that material already exist in MATERIAL_RECOMMENDED_QTY for that
list?
--    If so, Insert. If not, Update by adding the prior value to the new
value.
   SET @Temp_Recommended_Qty = NULL
   SELECT @Temp_Recommended_Qty = @Temp_Recommended_Qty
   FROM MATERIAL_RECOMMENDED_QTY
   WHERE list_id = @list_ID AND material_id = @material_id
   IF @Temp_Recommended_Qty IS NULL
    INSERT INTO MATERIAL_RECOMMENDED_QTY
     (material_id, list_id, recommended_qty, active, update_personid)
     VALUES (@material_id, @list_id, @recommended_qty, 1, 'LEGACY')
   ELSE
    UPDATE MATERIAL_RECOMMENDED_QTY
     SET recommended_qty = @Temp_Recommended_Qty + @recommended_qty
    WHERE list_id = @list_ID AND material_id = @material_id
   FETCH NEXT FROM SparesListMaterialRecommendedQtyCursor INTO
@Project_number, @List_number, @material_number, @Recommended_qty
  END
CLOSE SparesListMaterialRecommendedQtyCursor
DEALLOCATE SparesListMaterialRecommendedQtyCursor
"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:%23XmuksbwEHA.1564@TK2MSFTNGP09.phx.gbl...
> Can you post your code as well as a narrative about what it's doing and
why?
>
> -- 
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>
> "Scott M. Lyon" <scott.RED.lyon.WHITE@rapistan.BLUE.com> wrote in message
> news:eZSsYobwEHA.3292@TK2MSFTNGP15.phx.gbl...
> > I've got a stored procedure that essentially consists of about a half
> dozen
> > CURSORs (one after the other, not nested), each of which processes some
> > data. Each CURSOR has a source that is a SELECT with at least one INNER
> JOIN
> > (which I cannot change).
> >
> >
> > I'm trying to run this stored procedure as a job on our SQL server (so
it
> > will run regardless of whether I'm there or not, or if my PC is on or
> not).
> >
> >
> > Here are the two major problems I'm having:
> >
> > 1) The job is locking my source database (despite the fact that it's a
> > database that I know is unchanging), making it impossible for me to run
> > multiple instances of the job simultaneously
> > 2) When the job is running, I cannot even go into Enterprise Manager,
and
> > the databas in question, and Management->Current Activity, as it locks
up
> my
> > machine (never coming back) - I suspect this may be related to the
locking
> > issue mentioned above.
> >
> >
> > First of all, I'm declaring the cursors as LOCAL STATIC.
> >
> > Second, for every FROM and JOIN, after the table name I'm including WITH
> > (NOLOCK, READUNCOMMITTED) to prevent locks. Obviously this isn't working
> as
> > planned.
> >
> >
> >
> > Can anyone offer suggestions what I can try to resolve both issues?
> >
> >
> > Thanks!
> >
> >
>
>

Relevant Pages

  • Re: how to speed up some lisp code?
    ... >> representing these as lists. ... declare the variables to be FIXNUM. ...
    (comp.lang.lisp)
  • Re: Static/Strong/Implicit Typing
    ... >> accept both lists and vectors. ... >> arithmetic functions. ... > You could if the functions had multiple versions, and the compiler inserted ... code wanted a static declaration and the programmer chose to declare it ...
    (comp.lang.lisp)
  • Re: static typing
    ... I would like to be able to declare a type like list ... First, if you're processing lists, you shouldn't use NULL, CONS, CAR or CDR! ... The public API for list processing is ENDP, LIST, FIRST and REST. ... A general library of Lisp 'linting' utilities might be very useful. ...
    (comp.lang.lisp)
  • RE: Parsing XML (in a text field) from a stored procedure
    ... declare @doc varchar ... EXEC sp_xml_preparedocument @i OUTPUT, @doc ... > Project segment, ... > Is there an easy way I can do this parsing within a stored procedure? ...
    (microsoft.public.sqlserver.programming)
  • Re: how to speed up some lisp code?
    ... > The integers in the lists are small: ... I was hoping that since I could use arithmetic operations ... declare the variables to be FIXNUM. ... Make sure you declare all the temporary variables as well. ...
    (comp.lang.lisp)
Loading