I have a stored procedure I have to execute weekly. It updates active
subscribers to the newspaper. I created a DTS to exec the stored procedure.
It displays old data. November the 5 was the most recent subscriber. I the
chalked it up to DTS failure and created a automated job to exec the stored
proc. It comes up with the same old data. When I run the Stored proc
manually ( exec stored proc) it runs fine and the data is current. Not sure
why this is happening. The stored proc is below. It is on SQL 7.
/****** Object: Stored Procedure dbo.FW_MarketInfoActiveSubs Script
Date: 10/17/2000 4:59:53 PM ******/
CREATE Procedure dbo.FW_MarketInfoActiveSubs as
/***************************************************************************
*******************
** STORED PROCEDURE: FW_MarketInfoActiveSubs
**
** PURPOSE: To get all the Active Subscribers as of current date
**
** CREATED BY: Shailaja Gummaraju
**
** CREATION DATE: Oct 13 2000
**
** CREATION PURPOSE: To load data into the Market info Database
**
** DATA SOURCES: dbo_CiDetail, dbo_HomePhone, dbo_Address, dbo_StreetName,
dbo_City,
** dbo_AddressAttribute, dbo_AddressAttributeValue, dbo_Subscription,
** dbo_PhoneAddress, dbo_PhoneDelivery, dbo_SusbcriptionDetail,
** dbo_DeliveryService, dbo_DeliveryMethod
**
** INPUT PARAMETERS: None
**
** OUTPUT PARAMETERS: None
**
****************************************************************************
********************/
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()
MS Sql Server LazyDBA home page