Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Sproc not Executing as expected

    I use the following sproc to populate a table that is used as the base recordset for a report.

    For some reason, when the sproc is run from a scheduled job, it doesn't repopulate the table. It does, however, truncate the table. If I run it manually from query analyzer, it works fine.

    I've checked all the permissions on all the object touched by the sproc, and everything looks right there. Is there another problem I should be looking for?

    Code:
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS OFF 
    GO
    
    setuser N'mcorron'
    GO
    
    CREATE PROCEDURE mcorron.CreateDiscOrders
    AS
    /*
     Creates table for Orders with disc items Actuate report
    */
    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF
    
    TRUNCATE TABLE dbo.rptDiscOrders
    
    
    INSERT INTO dbo.rptDiscOrders 
    SELECT     *
    FROM         (SELECT     ORD.product as prod_XREF, ORD.ORDER_NUMB, ORD.CustName, ord.units as ordunits, INV.Product, 
                                                  INV.Units 
                           FROM          (SELECT     TOP 100 PERCENT f.PARENT_SITE, f.SITE, dbo.vwCustBillTo.CustName, o.ORDER_NUMB, p.Prod_Xref, o.PRODUCT, 
                          o.ORDER_TONS * 2000 / m.part_wt AS UNITS
    FROM         dbo.Lawn_Orders o INNER JOIN
                          dbo.PRODUCT_XREF p ON o.PRODUCT = p.Product INNER JOIN
                          dbo.FACILITY_MASTER f ON o.WHSE = f.SITE INNER JOIN
                          dbo.Lawn_PartMstr m ON o.PRODUCT = m.part_code INNER JOIN
                          dbo.vwCustBillTo ON o.BILLTO = dbo.vwCustBillTo.BillToNum
    WHERE     (o.SHIP_DATE < DATEADD(d, 30, GETDATE())) and prod_xref not like 'dead%') ORD INNER JOIN
                                                      (SELECT     f.PARENT_SITE, x.Prod_Xref, i. Product, SUM(i.Qty) AS Units
                                                        FROM          dbo.Lawn_Inventory i INNER JOIN
                                                                               dbo.FACILITY_MASTER f ON i.Whse = f.SITE INNER JOIN
                                                                               dbo.PRODUCT_XREF x ON i. Product = x. Product
                                                        WHERE      (f.WHSE_TYPE = 'ship')
                                                        GROUP BY f.PARENT_SITE, x.Prod_Xref, i. Product) INV ON ORD.PARENT_SITE = INV.PARENT_SITE AND ORD.Prod_Xref = INV.Prod_Xref)
                           ordinv
    WHERE     (Prod_Xref <> Product)
    GO
    setuser
    GO
    
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    Thanks
    Inspiration Through Fermentation

  2. #2
    Join Date
    May 2002
    Posts
    299
    remark out the "insert" and just return the "select". Do you get anything back?

    If need to, fire up profile and see if the statement get executed.
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Thanks for the reply, but...

    You lost me on "fire up profile..."

    What do you mean?
    Inspiration Through Fermentation

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    i think he meant profiler :-)

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Ok. I found that, read up a little on it in BOL. Looks like something I could
    easily hose up, so I think I'll just ask someone in central IT to look at it.
    Thanks!
    Inspiration Through Fermentation

Posting Permissions

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