Hi:

I have a script below, when I run it on its own, it is great, but when I run
it as an "Execute SQL" task as part of a DTS package, the step fails with a
"division by zero" error. Shouldn't it ignore this error due to setting
ANSI_WARNINGS and ARITHABORT both to OFF? I get the error in Query Analyzer,
but it ignores it and moves on, just setting the result column to NULL when
there is a divide by zero, as it should.

Thanks,
Kayda
__________________________________________________ ______________

INSERT INTO ASFinal (WeekEnding, AgentLogin, AgentName)
SELECT WeekEnding, AgentLogin, AgentGivenName + ' ' + AgentSurName AS
AgentName
FROM AgentInformation
GO

SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
GO
UPDATE ASFinal SET
Corp_Calls = T2.TotalCallsAnswered,
Corp_TotalTalkTime = T2.TotalTalkTime,
Corp_AvgTalkTime = T2.TotalTalkTime / T2.TotalCallsAnswered,
Corp_Manned_Time = T2.TotalMannedTime
FROM
ASFinal T1, ASMain T2
WHERE
T1.WeekEnding = T2.WeekEnding AND
T1.AgentLogin = T2.AgentLogin AND
T2.Skillset = 'SC_Corp_Sk'

Try,

....
Corp_AvgTalkTime = T2.TotalTalkTime / nullif(T2.TotalCallsAnswered, 0),
....



AMB

"Kayda" wrote:

I think the DTS step will always fail on a Division By Zero condition. To
avoid this, try changing your expression as follows:

...
corp_avgtalktime = T2.totaltalktime / NULLIF(T2.totalcallsanswered,0)
...

This will substitute NULLs for zeros and won't throw an error.

--
David Portas
SQL Server MVP
--