Problems with good SPs going bad in Agent!
-
I am trying to install 2 different SPs, both of which do what they should in QA, as 'Jobs'
Both have problems under Agent (only!):
1. The first, which just goes and summarises a few numbers in a table, ALWAYS returns as failing.... but it has done it's job
2. More seriously, the second - which does a very intense job of summarising a lot of numbers into another table, seems to do it's job (at 70-100% cpu), then cpu usage dips momentarily (end of proper processing?) - then cpu goes to 100% and stays there!!!
I reiterate - both SPs work as they should under Query Analyser.
What the $&#$ is going on?... Any ideas?
(I have tried adding a 'RETURN 0' to both btw)
tia
Steve
-
How about providing the SP's and maybe the exact errors that you're receiving. Then we could aid you further without doing too much guesswork
--------------------
Colt 45 - the original point and click interface
-
Fine... didn't want to drown you in code, first up! <g>
I should say that it appears that I'm quite wrong about item #2 - it was just my impatience, because apparently that query does go to 100% cpu for 45secs or more. In other words it appears as if it is actually behaving correctly when run via Agent.
The issue with item #1 remains: has the 'Failed' status in Agent again this morning.
The SP called is this:
ALTER PROCEDURE dbo.TCSDailyNumbersIntoConfig @CountryCode INT = 61
AS
DECLARE @StartDate AS SMALLDATETIME, @EndDate AS SMALLDATETIME, @EndDateF AS VARCHAR(20)
SET @EndDateF = CONVERT(VARCHAR(12), DATEADD(d, -1, GETDATE()), 111) -- 'yesterday' as yy/mm/dd, w/out time component
SET @EndDate = CAST(@EndDateF AS SMALLDATETIME) -- 'yesterday', w/out time component
SET @StartDate = CAST(DATEPART(yy, @EndDate) AS VARCHAR(4)) + '-1-1' -- 1st Jan of Year of yesterday
SET @EndDateF = CONVERT(VARCHAR(12), @EndDate, 107) -- re-cast to dd/mm/yy
DECLARE @SectionName AS VARCHAR(20), @ItemName VARCHAR(20), @HeadOfficeID INT, @ConfigText VARCHAR(200)
SET @SectionName = 'DailySummaryNumbers'
SET @HeadOfficeID = -1
-- /* REMOVE SECTION */
-- DECLARE @CountryCode int
-- SET @CountryCode = 61
-- SET @startdate = '2004-1-1'
-- SET @EndDate = '2004-3-31'
-- /* end REMOVE SECTION */
/* start : write the date record, with 'Yesterday' date, in dd/mm/yy */
SET @ItemName = 'ToDate'
DELETE Config WHERE SectionName = @SectionName AND ItemName = @ItemName AND StoreSysID = @HeadOfficeID
INSERT INTO Config (StoreSysID, SectionName, ItemName, ItemValue, DTstamp)
SELECT @HeadOfficeID, @SectionName, @ItemName, @EndDateF, dbo.ToDTstamp(GETDATE())
SET @EndDate = DATEADD(d, 1, @EndDate) -- 'today', w/out time component
/* Phase 1 : Total Customers this Year */
SET @ItemName = 'Item1'
DECLARE @Customers AS INT
SET @Customers = ( SELECT SUM(C.GuestQty) FROM Crtots C
WHERE C.StoreSysID IN
(Select StoreSysID From Stores S Join Franchises F on F.MFnum = S.MFnum Where F.CCode = @CountryCode)
AND C.TotalsDateTime > @StartDate AND C.TotalsDateTime < @EndDate )
SET @ConfigText = 'TCS is proud to have served ' + dbo.fnCommaFormatInteger(@Customers) + ' customers in Australia this year (to ' + @EndDateF + ')'
DELETE FROM Config WHERE SectionName = @SectionName AND ItemName = @ItemName AND StoreSysID = @HeadOfficeID
INSERT INTO Config (StoreSysID, SectionName, ItemName, ItemValue, DTstamp)
SELECT @HeadOfficeID, @SectionName, @ItemName, @ConfigText, dbo.ToDTstamp(GETDATE())
/* Phase 2 : Best Selling Product */
SET @ItemName = 'Item2'
DECLARE @ProductName AS VARCHAR(40)
SET @ProductName = ( SELECT TOP 1 P.Description FROM DayTrans D
JOIN Products P ON D.ProductID = P.ProductID
JOIN ProductPurchaseUnits U on U.UnitID = D.PurchaseUnitID
WHERE (P.Description NOT LIKE 'PROMO%') AND D.StoreSysID IN
(Select StoreSysID From Stores S Join Franchises F on F.MFnum = S.MFnum Where F.CCode = @CountryCode)
AND D.SaleDateTime > @StartDate AND D.SaleDateTime < @EndDate
GROUP by P.Description
ORDER by SUM(ROUND(D.Sales /U.QuantityPerFull, 2)) DESC )
SET @ConfigText = 'Our best-selling product this year is the ' + @ProductName + ' (based on volume)'
DELETE FROM Config WHERE SectionName = @SectionName AND ItemName = @ItemName AND StoreSysID = @HeadOfficeID
INSERT INTO Config (StoreSysID, SectionName, ItemName, ItemValue, DTstamp)
SELECT @HeadOfficeID, @SectionName, @ItemName, @ConfigText, dbo.ToDTstamp(GETDATE())
-- SELECT TOP 1 P.Description FROM DayTrans D
-- JOIN Products P ON D.ProductID = P.ProductID
-- JOIN ProductPurchaseUnits U on U.UnitID = D.PurchaseUnitID
-- WHERE (SaleDateTime BETWEEN @StartDate and @EndDate) AND (P.Description NOT LIKE 'PROMO%')
-- Group by P.Description
-- Order by SUM(ROUND(D.Sales /U.QuantityPerFull, 2)) DESC
/* Phase 3 : Sizes Sale Breakdown */
-- Portion of sales nationally for year: ??% Full / ?? Half / ?? Midi / ?? Quarters / ?? Slice / ??Slab
SET @ItemName = 'Item3'
DROP TABLE #SizeSalesSummary
CREATE TABLE #SizeSalesSummary (SizeUnitID int, SizeLabel VARCHAR(20), SalesQty int)
INSERT INTO #SizeSalesSummary (SizeUnitID, SizeLabel, SalesQty)
SELECT D.PurchaseUnitID, U.UnitLabel, SUM(D.Sales) as SalesQty
FROM DayTrans D
JOIN Products P ON P.ProductID = D.ProductID
JOIN ProductPurchaseUnits U on U.UnitID = D.PurchaseUnitID
JOIN Stores S ON S.StoreSysID = D.StoreSysID
JOIN Franchises F ON F.MFnum = S.MFnum
WHERE (SaleDateTime BETWEEN @StartDate and @EndDate) AND
(F.CCode = @CountryCode) AND
(P.Description NOT LIKE 'PROMO%') AND
(P.Discontinued = 0) AND
(P.ProductID IN (SELECT ProductID FROM Products WHERE EXISTS
(SELECT * FROM ProductPriceLink L
WHERE L.ProductID = P.ProductID AND L.PurchaseUnitID > 1 AND L.StoreSysID IN
(Select StoreSysID From Stores S Join Franchises F on F.MFnum = S.MFnum
Where F.CCode = @CountryCode))))
GROUP BY D.PurchaseUnitID, U.UnitLabel
ORDER BY SalesQty DESC
DECLARE @TotalSizeSales AS INT, @PerCentage DECIMAL(10,4)
SET @TotalSizeSales = (SELECT SUM(SalesQty) FROM #SizeSalesSummary)
DECLARE size_curs CURSOR FOR
SELECT RTRIM(SizeLabel), SalesQty FROM #SizeSalesSummary
DECLARE @SizeLabel VARCHAR(20), @SalesQty INT
SET @ConfigText = 'Portion of sales nationally for year: '
OPEN size_curs
FETCH NEXT from size_curs into @SizeLabel, @SalesQty
WHILE @@FETCH_STATUS = 0
BEGIN
IF @SalesQty > 0
BEGIN
SET @PerCentage = 100 * @SalesQty / @TotalSizeSales
IF @Percentage >= 1
SET @ConfigText = @ConfigText + CAST(CEILING(ROUND(@PerCentage, 0)) AS VARCHAR(3)) + '% ' + @SizeLabel + ', '
-- SELECT @SizeLabel, @SalesQty, @TotalSizeSales, @PerCentage
-- IF @Percentage < 1
-- SET @ConfigText = @ConfigText + '<1% ' + @SizeLabel + ', '
-- ELSE
-- SET @ConfigText = @ConfigText + CAST(CEILING(ROUND(@PerCentage, 0)) AS VARCHAR(3)) + '% ' + @SizeLabel + ', '
END
FETCH NEXT from size_curs into @SizeLabel, @SalesQty
END
CLOSE size_curs
DEALLOCATE size_curs
DROP TABLE #SizeSalesSummary
SET @ConfigText = @ConfigText + 'all others <1% each (for products sold in more than one size)'
--SET @ConfigText = SUBSTRING(@ConfigText, 1, LEN(@ConfigText) -1) + ' - (for products sold in more than one size)'
DELETE FROM Config WHERE SectionName = @SectionName AND ItemName = @ItemName AND StoreSysID = @HeadOfficeID
INSERT INTO Config (StoreSysID, SectionName, ItemName, ItemValue, DTstamp)
SELECT @HeadOfficeID, @SectionName, @ItemName, @ConfigText, dbo.ToDTstamp(GETDATE())
-- SELECT D.PurchaseUnitID, U.UnitLabel, SUM(D.Sales) as SalesQty
-- FROM DayTrans D
-- JOIN Products P ON P.ProductID = D.ProductID
-- JOIN ProductPurchaseUnits U on U.UnitID = D.PurchaseUnitID
-- JOIN Stores S ON S.StoreSysID = D.StoreSysID
-- JOIN Franchises F ON F.MFnum = S.MFnum
-- WHERE (SaleDateTime BETWEEN @StartDate and @EndDate) AND
-- (F.CCode = @CountryCode) AND
-- (P.Description NOT LIKE 'PROMO%') AND
-- (P.Discontinued = 0) AND
-- (P.ProductID IN (SELECT ProductID FROM Products WHERE EXISTS
-- (SELECT * FROM ProductPriceLink L
-- WHERE L.ProductID = P.ProductID AND L.PurchaseUnitID > 1 AND L.StoreSysID IN
-- (Select StoreSysID From Stores S Join Franchises F on F.MFnum = S.MFnum
-- Where F.CCode = @CountryCode))))
-- GROUP BY D.PurchaseUnitID, U.UnitLabel
-- ORDER BY SalesQty DESC
/* Phase 4 : Average Retail Sale */
SET @ItemName = 'Item4'
--DECLARE @Customers AS INT already set in Phase 1
DECLARE @TotalSumSales MONEY, @TotalSumSalesWT money
SET @TotalSumSales = ( SELECT SUM(C.TotalSalesRetail - C.RetailTax) FROM Crtots C
WHERE C.StoreSysID IN
(Select StoreSysID From Stores S Join Franchises F on F.MFnum = S.MFnum Where F.CCode = @CountryCode)
AND C.TotalsDateTime > @StartDate AND C.TotalsDateTime < @EndDate )
SET @TotalSumSalesWT = ( SELECT SUM(C.TotalSalesRetail) FROM Crtots C
WHERE C.StoreSysID IN
(Select StoreSysID From Stores S Join Franchises F on F.MFnum = S.MFnum Where F.CCode = @CountryCode)
AND C.TotalsDateTime > @StartDate AND C.TotalsDateTime < @EndDate )
SET @ConfigText = 'The Average Retail Sale is $' + CAST(ROUND(@TotalSumSales / @Customers, 2) AS varchar(12)) + ' (ex. GST), $' + CAST(ROUND(@TotalSumSalesWT / @Customers, 2) AS varchar(12)) + ' (incl. GST)'
DELETE FROM Config WHERE SectionName = @SectionName AND ItemName = @ItemName AND StoreSysID = @HeadOfficeID
INSERT INTO Config (StoreSysID, SectionName, ItemName, ItemValue, DTstamp)
SELECT @HeadOfficeID, @SectionName, @ItemName, @ConfigText, dbo.ToDTstamp(GETDATE())
RETURN 0 -- mark Success
------------------------------------------- end ----------------------------------------------------------------
This SP runs without problem in QA... in fact it also runs successfully in SQLAgent - it just says that it Fails!!
Event Log says:
"SQL Server Scheduled Job 'RunTCSDailyNumbers' (0xD3DABF17893D0743A0E278FCC7608570) - Status: Failed - Invoked on: 2007-05-30 05:05:00 - Message: The job failed. The Job was invoked by Schedule 16 (RunDaily). The last step to run was step 1 (Run Daily Numbers SP). "
Any thoughts?
Steve
-
Hmmm ... I can understand how you can be frustrated with this. What you have a first look is not much help as the specific failure message from the procedure is not visible.
Does the job history have anything different from the Event Log entry?
You could also try saving the T-SQL output to a text file. This is done on the advanced tab of the step properties.
--------------------
Colt 45 - the original point and click interface -
You shouldn't have a query that pegs the CPU at 100% for that long. Must be runnning really badly for that to happen. How many rows get added to the temp table thats created? If you're inserting 1000's of rows you may need to consider putting an index on the table.
I notice that quite a few of the queries use
"WHERE fieldname IN (SELECT ... FROM ... )
These should really be changed to joins in the FROM clause.
Have you checked the execution plans for each of the queries in the procedure? You might need to add some indexes to allow the data to be returned without performing costly table or index scans.
Also, you may be able to remove the cursor with something like this,
select @configText = @ConfigText + ',' + qry.config_string
from (
select
CAST(CEILING(ROUND((100 * Smy.SalesQty / ttl.TotalSizeSales), 0)) AS VARCHAR(3)) + '% ' + RTRIM(Smy.SizeLabel)
from #SizeSalesSummary Smy, (SELECT SUM(SalesQty) as TotalSizeSales FROM #SizeSalesSummary) as ttl
) as qry
--------------------
Colt 45 - the original point and click interface -
Can find no other history info that tells me any more.
Output (file) just tells me it's failed on Step 1 !!!!
-
Stranger and Stranger ...
Put a couple of PRINT statements in the procedure as status messages. These will show up in the output file.
--------------------
Colt 45 - the original point and click interface -
This supplied code is *not* the one running for a long time - that was my item #2 which in fact *is* working.
That one is very intensive.... about 12000s rows to insert.
Just FYI, this is the code (running ok):
CREATE PROCEDURE dbo.sfbUpdateProductSalesSummary
(
@ToDate SMALLDATETIME = NULL,
@DaysBack SMALLINT = 28,
@CountryCode INT = 61
 
AS
/*
Default use is from a server scheduled task, with no parameters: for 'Yesterday' and 28 days before,
aggregate ALL Product/Size sales, FOR the Country (ie ALL Stores FOR country), grouped BY Date, Product, Size.
Insert into summary table, for quicker read: just 1 record FOR each product/size comb.
*/
-- create temp. table to hold the Average Price (ex. Tax) for every Product/Size for the country
-- this allows us not to worry about PPL values missing/null in this date range - a valid-range Price is always returned
CREATE TABLE #AveragePrices ( ProductID INT, SizeID TINYINT, AveragePriceExTax MONEY)
INSERT INTO #AveragePrices (ProductID, SizeID, AveragePriceExTax)
SELECT L.ProductID, L.PurchaseUnitID, AVG(L.UnitPrice * (100 / (100 + L.TaxRate)))
FROM ProductPriceLink L
JOIN Stores S ON L.StoreSysID = S.StoreSysID
JOIN Franchises F ON S.MFnum = F.MFnum
WHERE F.CCode = @CountryCode
GROUP BY L.ProductID, L.PurchaseUnitID
DECLARE @ToDateF AS VARCHAR(20), @FromDate AS SMALLDATETIME
IF @ToDate IS NULL
SET @ToDate = DateAdd(d, -1, GETDATE()) -- default yesterday
SET @ToDateF = CONVERT(VARCHAR(20), @ToDate, 111) -- as yy/mm/dd, w/out time component
SET @ToDate = CAST(@ToDateF AS SMALLDATETIME) -- w/out time component
SET @FromDate = DateAdd(d, -(@DaysBack), @ToDate) -- set to 'DaysBack' before ToDate
DELETE FROM ProductSalesSummary WHERE SaleDate BETWEEN @FromDate and @ToDate
INSERT INTO ProductSalesSummary (SaleDate, ProductID, PurchaseUnitID, SalesQty, SalesValue)
SELECT D.SaleDateTime, D.ProductID, D.PurchaseUnitID,
SUM(D.Sales) as SalesQty,
SUM(D.Sales * AP.AveragePriceExTax) AS SalesValue
FROM DayTrans D
JOIN Stores S ON S.StoreSysID = D.StoreSysID
JOIN Franchises F ON F.MFnum = S.MFnum
JOIN #AveragePrices AP ON AP.ProductID = D.ProductID AND AP.SizeID = D.PurchaseUnitID
WHERE (D.SaleDateTime BETWEEN @FromDate and @ToDate) AND (D.Sales > 0) AND (F.CCode = @CountryCode)
GROUP BY D.SaleDateTime, D.ProductID, D.PurchaseUnitID
DROP TABLE #AveragePrices
RETURN 0
Thanks for suggestions on the OTHER SP - I will see if appropriate.
But my 'failure' on that one remains.....
Steve
-
Just to make sure we're on the same page
Procedure sfbUpdateProductSalesSummary always returns failure in SQL Agent
Procedure TCSDailyNumbersIntoConfig pegs the CPU at 100%
Correct ?
--------------------
Colt 45 - the original point and click interface -
No mate - the other way around!
"TCSDailyNumbersIntoConfig" is the SP from my #1
"sfbUpdateProductSalesSummary" is the SP from my #2
Forget about #2... it is taking a while but it does complete.
#1 is the code I originally sent you...."TCSDailyNumbersIntoConfig"... always 'fails' - except it *doesn't* - all numbers are posted into Config!
FWIW I just replaced all "Stores IN..." with JOINS.... (no discernible difference).
About to sign off for the night - seems I've got your grey matter ticking over
-
Obviously my grey matter isn't ticking over hard enough
Given that "TCSDailyNumbersIntoConfig" reports the failure, I'm betting that it's the "DROP TABLE #SizeSalesSummary" statement at the start of Phase 3. If the table doesn't exist then it'll return an error. You should wrap this in an "IF EXISTS(SELECT...)" check.
--------------------
Colt 45 - the original point and click interface -
I'd also bet that the "sfbUpdateProductSalesSummary" SP is badly in need of some indexes, or maybe updating stats for existing indexes.
--------------------
Colt 45 - the original point and click interface -
Make sure the "On Success" of the step in the job is report success (not report failure) - sounds dumb, but I have seen this happen by accident before.
Script the job and post the script here - if the procedure works in QA and is actually completing in the job agent, I suspect it is a job setup problem.
-
Phil - I think that's it.
In fact last night I had already completely taken that line out, on the basis that it was a local, temporary table - therefore couldn't possibly exist before being created in this SP!
This morning, the Job has completed - with success!
thanks
-
I hear you... but the Joins are on PKs:
Products: ProductID
Stores: StoreSysID
Franchises: MFnum
DayTrans: StoreSysID, SaleDateTime, ProductID, PurchaseUnitID
ProductPriceLink: StoreSysID, ProductID, PurchaseUnitID
I don't think I can optimise those more!
Now I have to confess I don't understand "updating stats" at all....
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply