DTS error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS error

Author

Topic

1fred
Posting Yak Master

158 Posts

Posted - 2002-06-18 : 16:10:33

Hi, I am running an Sql Task in a DTS. Here is the code below. The thing is that I get an error when trying to execute it in the DTS and when I run it in SQL Query Analyser, it works just fine. Here is the error too.

Error Source : Microsoft OleDB Provider for SQL Server
Error Description :Insert Failed because the following SET options have incorrect settings : ARITHABORT
Delete Failed because the following SET options have incorrect settings : ARITHABORT

I don't think that that connection source is the error, since other sql task uses the same connection.

Thanks

begin transaction CVisitTotalT
delete PRD_DW.dbo.CVisitTotalT
Insert into PRD_DW.dbo.CVisitTotalT (
Visit_key,
WindowRandom,
Session_key,
Sent_key,
ProspectSolicited_key,
ProspectList_key,
Prospect_key,
MassiveSent_key,
Country_key,
employee_key,
Customer_key,
SenderCustomer_key,
SentProduct_key,
CardLanguage_key,
CustomerLanguage_key,
Card_key,
VisitType_key,
DirectIndirect_key,
NoCookie_key,
Noflash_key,
PlatformKBPS_key,
PlatformMS_key,
PlatformTotal_key,
EndProduct_key,
EndClick_key,
EndIncrement,
EndTrackNo,
NumberOfClicks,
KBPS,
MS,
Sound,
email,
message,
FlashVersion,
ScreenResolution,
BrowserLanguage,
Browser,
BrowserVersion,
OperatingSystem,
DNS,
IP,
Referrer,
VisitDay_key,
VisitingTimeMinutes,
VisitingTimeSeconds,
SentDateStamp,
VisitDateStamp,
EndDateStamp)
SELECT
dbo.SAVisitT.Visit_key,
case when (dbo.SAVisitInfo3T.WindowRandom is null) then 0 else dbo.SAVisitInfo3T.WindowRandom end as WindowRandom,
dbo.SAVisitT.Session_key,
dbo.SAVisitT.Sent_key,
PRD_Live.dbo.LSentT.ProspectSolicited_key,
PRD_DW.dbo.CProspectTotalT.ProspectList_key,
PRD_DW.dbo.CProspectTotalT.Prospect_key,
PRD_DW.dbo.CProspectTotalT.MassiveSent_key,
dbo.SAVisitInfo4T.Country_key,
case when (dbo.SAVisitInfo1T.IP = '205.151.202.234') then 1 end as employee_key,
dbo.SAVisitInfo4T.Customer_key,
PRD_Live.dbo.LSentT.Customer_key as SenderCustomer_key,
PRD_Live.dbo.LSentT.Product_key as SentProduct_key,
PRD_Live.dbo.LSentT.language_key as CardLanguage_key,
dbo.SAVisitInfo4T.Language_key as CustomerLanguage_key,
PRD_Live.dbo.LSentT.card_key,
dbo.SAVisitT.VisitType_key,
dbo.SAVisitInfo2T.DirectIndirect as DirectIndirect_key,
prd_sa.dbo.SaVisitNoCookieV.visitNocookie_key as NoCookie_key,
prd_sa.dbo.SaVisitNoflashV.flashVersion as Noflash_key,
-- les trois champ de plate-formes
case
when (dbo.SAVisitInfo2T.KBPS <4) and (dbo.SAVisitInfo2T.KBPS >0) then 1
when (dbo.SAVisitInfo2T.KBPS >=4) and (dbo.SAVisitInfo2T.KBPS <10) then 2
when (dbo.SAVisitInfo2T.KBPS >=10) then 3
else 0 end as PlatformKBPS_key,
case
when (dbo.SAVisitInfo2T.MS >= 50) then 1
when (dbo.SAVisitInfo2T.MS >=25) and (dbo.SAVisitInfo2T.MS <50) then 2
when (dbo.SAVisitInfo2T.MS < 25) then 3
else 0 end as PlatformMS_key,
case
when
(dbo.SAVisitInfo2T.KBPS <4) and (dbo.SAVisitInfo2T.KBPS >0) and
(dbo.SAVisitInfo2T.MS >= 50) and
(dbo.SAVisitInfo3T.sound = 1) then 1
when
(dbo.SAVisitInfo2T.KBPS <4) and (dbo.SAVisitInfo2T.KBPS >0) and
(dbo.SAVisitInfo2T.MS >=25) and (dbo.SAVisitInfo2T.MS <50) and
(dbo.SAVisitInfo3T.sound = 1) then 2
when
(dbo.SAVisitInfo2T.KBPS <4) and (dbo.SAVisitInfo2T.KBPS >0) and
(dbo.SAVisitInfo2T.MS < 25) and
(dbo.SAVisitInfo3T.sound = 1) then 3
when
(dbo.SAVisitInfo2T.KBPS >=4) and (dbo.SAVisitInfo2T.KBPS <10) and
(dbo.SAVisitInfo2T.MS >= 50) and
(dbo.SAVisitInfo3T.sound = 2) then 4
when
(dbo.SAVisitInfo2T.KBPS >=4) and (dbo.SAVisitInfo2T.KBPS <10) and
(dbo.SAVisitInfo2T.MS >= 50) and
(dbo.SAVisitInfo3T.sound = 3) then 5
when
(dbo.SAVisitInfo2T.KBPS >=4) and (dbo.SAVisitInfo2T.KBPS <10) and
(dbo.SAVisitInfo2T.MS >=25) and (dbo.SAVisitInfo2T.MS <50) and
(dbo.SAVisitInfo3T.sound = 2) then 6
when
(dbo.SAVisitInfo2T.KBPS >=4) and (dbo.SAVisitInfo2T.KBPS <10) and
(dbo.SAVisitInfo2T.MS >=25) and (dbo.SAVisitInfo2T.MS <50) and
(dbo.SAVisitInfo3T.sound = 3) then 7
when
(dbo.SAVisitInfo2T.KBPS >=4) and (dbo.SAVisitInfo2T.KBPS <10) and
(dbo.SAVisitInfo2T.MS < 25) and
(dbo.SAVisitInfo3T.sound = 2) then 8
when
(dbo.SAVisitInfo2T.KBPS >=4) and (dbo.SAVisitInfo2T.KBPS <10) and
(dbo.SAVisitInfo2T.MS < 25) and
(dbo.SAVisitInfo3T.sound = 2) then 9
when
(dbo.SAVisitInfo2T.KBPS >=10) and
(dbo.SAVisitInfo2T.MS >= 50) and
(dbo.SAVisitInfo3T.sound = 2) then 10
when
(dbo.SAVisitInfo2T.KBPS >=10) and
(dbo.SAVisitInfo2T.MS >=25) and (dbo.SAVisitInfo2T.MS <50) and
(dbo.SAVisitInfo3T.sound = 2) then 11
when
(dbo.SAVisitInfo2T.KBPS >=10) and
(dbo.SAVisitInfo2T.MS < 25) and
(dbo.SAVisitInfo3T.sound = 2) then 12
when
(dbo.SAVisitInfo2T.KBPS >=10) and
(dbo.SAVisitInfo2T.MS >= 50) and
(dbo.SAVisitInfo3T.sound = 3) then 13
when
(dbo.SAVisitInfo2T.KBPS >=10) and
(dbo.SAVisitInfo2T.MS >=25) and (dbo.SAVisitInfo2T.MS <50) and
(dbo.SAVisitInfo3T.sound = 3) then 14
when
(dbo.SAVisitInfo2T.KBPS >=10) and
(dbo.SAVisitInfo2T.MS < 25) and
(dbo.SAVisitInfo3T.sound = 3) then 15
else 0 end
as PlatformTotalT_key,
EndProduct_key,
EndClick_key,
EndIncrement,
EndTrackNo,
EndTrackNo as NumberOfClicks,
dbo.SAVisitInfo2T.KBPS,
dbo.SAVisitInfo2T.MS,
dbo.SAVisitInfo3T.sound,
PRD_Live.dbo.LSentT.email,
PRD_Live.dbo.LSentT.message,
dbo.SAVisitInfo1T.FlashVersion,
dbo.SAVisitInfo1T.ScreenResolution,
dbo.SAVisitInfo1T.BrowserLanguage,
dbo.SAVisitInfo1T.Browser,
dbo.SAVisitInfo1T.BrowserVersion,
dbo.SAVisitInfo1T.OperatingSystem,
dbo.SAVisitInfo1T.DNS,
dbo.SAVisitInfo1T.IP,
dbo.SAVisitInfo1T.Referrer,
datepart (dw, dbo.SAVisitT.DateStamp) as VisitDay_key,
PRD_SA.dbo.SAVisitEndV.VisitingTimeMinutes,
PRD_SA.dbo.SAVisitEndV.VisitingTimeSeconds,
PRD_Live.dbo.LSentT.datestamp as SentDateStamp,
dbo.SAVisitT.DateStamp as VisitDateStamp,
EndDateStamp
FROM
PRD_SA.dbo.SAVisitT LEFT OUTER JOIN
PRD_Live.dbo.LSentT ON PRD_SA.dbo.SAVisitT.Sent_key = PRD_Live.dbo.LSentT.sent_key LEFT OUTER JOIN
PRD_SA.dbo.SAVisitInfo3T ON dbo.SAVisitT.Visit_key = dbo.SAVisitInfo3T.Visit_key LEFT OUTER JOIN
PRD_SA.dbo.SAVisitInfo4T ON dbo.SAVisitT.Visit_key = dbo.SAVisitInfo4T.Visit_key LEFT OUTER JOIN
PRD_SA.dbo.SAVisitInfo2T ON dbo.SAVisitT.Visit_key = dbo.SAVisitInfo2T.Visit_key LEFT OUTER JOIN
PRD_SA.dbo.SAVisitEndV ON dbo.SAVisitT.Visit_key = dbo.SAVisitEndV.Visit_key LEFT OUTER JOIN
PRD_SA.dbo.SAVisitInfo1T ON dbo.SAVisitT.Visit_key = dbo.SAVisitInfo1T.visit_key left outer join
prd_sa.dbo.SaVisitNoCookieV on dbo.SAVisitT.Visit_key = prd_sa.dbo.SaVisitNoCookiev.visit_key left outer join
PRD_DW.dbo.CProspectTotalT ON PRD_DW.dbo.CProspectTotalT.ProspectSolicited_key = PRD_Live.dbo.LSentT.ProspectSolicited_key LEFT OUTER JOIN
prd_sa.dbo.SaVisitNoflashV ON dbo.SAVisitT.Visit_key = prd_sa.dbo.SaVisitNoflashV.visit_key
commit transaction CVisitTotalT

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-18 : 16:24:52

it sounds as though you've got a materialized view defined in your database. Read up on them in BOL to see what the SET option constraint issue is all about.

setBasedIsTheTruepath
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources