RE: DTS Fails even though stored proc is good

RE: DTS Fails even though stored proc is good

 

  

Did you create the DTS packages on the server?

-----Original Message-----
From: Shawn Smith [mailto:[Email Address Removed]
Sent: 07 January 2002 23:14
To: [Email Address Removed] DTS Fails even though stored proc is good


have two DTS packages that are set to run on Sunday at 12:00am when noone
is in the office. They continually fail. I have SQL 2000 installed on my
machine and SQL 7.0 installed on the server. The Stored Procedures the DTS
packages are executing work fine when executed alone. Below is one of the
stored procedures: Truncate Table dbo.FW_ActiveSubscribers

Select Getdate()
INSERT INTO dbo.FW_ActiveSubscribers (
AddressId,
AccountNumber,
LastName,
FirstName,
StreetNumber,
StreetName,
PreDirection,
PostDirection,
StreetType,
Apartment,
Building,
Address,
City,
State,
Zipcode,
County,
SequenceName,
WalkSequence,
HouseholdType,
PhoneId,
SubStartdate,
SubEnddate,
DeliveryServiceId,
DeliveryMethodId,
PDStartDate,
PDEndDate,
RouteId,
TemplateId,
Template,
PeriodType,
PeriodCount,
NoTele,
NoFlyer,
NoKnock,
SourceId,
Source,
SourceData,
EmailAddress)
SELECT
ad.addressid,
hp.accountnumber,
hp.name,
hp.firstname,
substring(ad.streetnumber,1,6) Street#,
substring(sn.description,1,25) StreetName,
pread.Description PreDirection,
postad.Description PostDirection,
st.Description StreetType,
ad.apartmentnumber,
ad.BuildingNumber,
rtrim(ad.StreetNumber) + ' ' + rtrim(sn.Description) + ' ' +
rtrim(ad.ApartmentNumber) + ' ' + rtrim(IsNull(BuildingNumber, ' ')),
substring(ci.description,1,15) City,
substring(ci.provincetext,1,2) State,
ad.zipcode,
c.Description county,
ad.sequencename,
ad.walksequence,
av.description Household,
hp.PhoneId,
Convert(Varchar(12),s.StartDate, 112), Convert(Varchar(12),s.EndDate, 112),
s.DeliveryServiceId, sd.DeliveryMethodId, pd.StartDate, pd.EndDate,
pd.RouteId, t.TemplateId, t.Code, t.PeriodId, t.CountOfPeriod,
hp.NoTelemarketing, hp.NoFlyer, hp.NoKnock, s.SourceId, sc.Description,
ssd.SourceDetailDescription, hp.emailaddress

FROM
dbo_address ad,
dbo_city ci,
dbo_streetname sn,
dbo_attributeaddress aa,
dbo_attributevalue av,
dbo_homephone hp,
dbo_phonedelivery pd,
dbo_subscription s,
dbo_SubscriptionDetail sd,
dbo_AddressDirection pread,
dbo_AddressDirection postad,
dbo_StreetType st,
dbo_County c,
dbo_Template t,
dbo_Source sc,
dbo_SalesSourceDetail ssd
WHERE
GetDate() between s.StartDate and s.EndDate
AND GetDate() between sd.StartDate and sd.Enddate
AND GetDate() between pd.StartDate and pd.Enddate
AND ad.cityid = ci.cityid
AND ad.streetnameid = sn.streetnameid
AND aa.address_id = ad.addressid
AND aa.attribute_id = av.id
AND pd.addressid = ad.addressid
AND hp.phoneid = pd.phoneid
AND s.PhoneId = hp.PhoneId
AND s.SubscriptionId = sd.SubscriptionId
AND sd.ProductId = pd.ProductId
AND pd.ProductId = 1
AND sn.predirectionId = pread.DirectionId
AND sn.postdirectionId = postad.DirectionId
AND sn.StreetTypeId = st.StreetTypeId
AND ad.CountyId = c.CountyId
AND s.Templateid *= t.TemplateId
AND s.SourceId *= sc.SourceId
AND s.SourceDetailID *= ssd.SourceDetailId

select getdate()

/* To get the Route Information */
UPDATE dbo.FW_ActiveSubscribers
SET RouteName = ciname,
RouteStartDate = startdate,
RouteEnddate = enddate
FROM dbo_CiDetail cd
WHERE dbo.FW_ActiveSubscribers.RouteId = cd.Ciid
AND GetDate() between cd.StartDate and cd.EndDate
/* To get the Phone Number for the Subscribers */
Update dbo.FW_ActiveSubscribers
Set PhoneNumber = pp.PhoneNumber
from dbo_PersonPhone pp
Where FW_ActiveSubscribers.PhoneId = pp.PhoneId
And pp.TelephoneTypeId = 1
/* To get the Carrier Details */
UPDATE dbo.FW_ActiveSubscribers
SET CarrierLastName = hp.Name,
CarrierFirstName = hp.FirstName
from dbo_HomePhone hp, dbo_CiPhone cp
Where dbo.FW_ActiveSubscribers.RouteId = cp.Ciid
AND cp.PhoneId = hp.PhoneId
/* To get the Subscription Length */
UPDATE dbo.FW_ActiveSubscribers
SET SubLength =
CASE When DeliveryMethodId = 1 and PeriodType = 3 and PeriodCount =1 then
'1M' WHEN DeliveryMethodId = 1 and PeriodType = 3 and PeriodCount =2 then
'2M' WHEN DeliveryMethodId = 1 and PeriodType = 3 and PeriodCount =3 then
'3M' WHEN DeliveryMethodId = 1 and PeriodType = 3 and PeriodCount =4 then
'4M' WHEN DeliveryMethodId = 1 and PeriodType = 3 and PeriodCount =6 then
'6M' WHEN DeliveryMethodId = 1 and PeriodType = 3 and PeriodCount =9 then
'9M' WHEN DeliveryMethodId = 1 and PeriodType = 4 and PeriodCount =1 then
'1Y' WHEN DeliveryMethodId = 3 then '1M' WHEN DeliveryMethodId = 2 then '1M'
END
/* To get the Subscription Type */
UPDATE dbo.FW_ActiveSubscribers
SET SubType =
CASE when DeliveryServiceId = 1 then 'MW'
when DeliveryServiceId = 2 then '3F'
when DeliveryServiceId = 3 then 'MO'
when DeliveryServiceId = 4 then 'SO'
when DeliveryServiceId = 5 then 'SC'
END
UPDATE dbo.FW_ActiveSubscribers
Set DelType =
CASE when DeliveryMethodId = 1 then 'PP'
when DeliveryMethodId = 2 then 'MS'
When DeliveryMethodID = 3 then 'CC'
END
select getdate()



Shawn Smith
Database Administrator
[Email Address Removed] cell


---------------------------------------------------------------------
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page