July 24, 2002 at 9:04 am
I have a DTS package that has an Execute SQL Task with the following code:
/* Set initial value for flag showing that the record was deleted
from Syteline. If it is still in Syteline, the second Update will
reset the value to false.*/
UPDATE Booking
SETdeltd_from_syteline_indcr = -1;
GO
UPDATE Booking
SET custmr_dmnsn_id =V.custmr_dmnsn_id,
initl_cntct_mrktng_evnt_dmnsn_id = V.initl_cntct_mrktg_evnt_dmnsn_id,
order_class_id = V.order_class_id,
prspct_dmnsn_id = V.prspct_dmnsn_id,
rspbl_catalog_litrtr_dmnsn_id = V.mrktg_litrtr_dmnsn_id,
buyer_or_prospect_cd = V.buyer_or_prospect_cd,
clv_year_no = V.clv_year_no,
deltd_from_syteline_indcr = 0,
discount_perctg = V.discount_perctg,
freight_charge = V.freight_charge,
gross_price = V.gross_price,
misc_charge = V.misc_charge,
net_price = V.net_price,
response_interval_days = V.response_interval_days,
response_interval_weeks =V.response_interval_weeks,
sales_tax_charge = V.sales_tax_charge,
valid_order_flag = V.valid_order_indcr,
catalog_sent_dt_calndr_id = V.catalog_sent_dt_calndr_id,
custmr_first_order_dt_calndr_id = V.custmr_first_order_dt_calndr_id,
first_catalog_dt_calndr_id = V.first_catalog_dt_calndr_id,
first_due_dt_calndr_id = V.first_due_dt_calndr_id,
last_ship_dt_calndr_id = V.last_ship_dt_calndr_id,
order_dt_calndr_id = V.order_dt_calndr_id,
rspbl_mrktg_evnt_dmnsn_id = V.rspbl_mrktg_evnt_dmnsn_id,
-- 4/2/2002 wlm Added.
entrd_mrktg_evnt_dmnsn_id = V.entrd_mrktg_evnt_dmnsn_id,
entrd_mrktg_litrtr_dmnsn_id = V.entrd_mrktg_litrtr_dmnsn_id
FROMTMStagingArea.dbo.Booking_Load_V V
WHERE V.custmr_order_no = Booking.custmr_order_no;
GO
This code has run successfully in the past. Now however the task fails. Here is the log for the package:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14)
Error string: Internal SQL Server error.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
Error Detail Records:
Error: -2147217900 (80040E14); Provider Error: 8624 (21B0)
Error string: Internal SQL Server error.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun: Package execution complete.
I can find nothing on Microsoft’s KB that applies to my situation. I am running SQL7 SP4 on NT4. Any thoughts???
Thanks.
Jonathan
July 25, 2002 at 7:34 am
By commenting out the entire SQL and uncommenting one line at a time and parsing each time I found that the following version of my original update works! For some reason the prspct_dmnsn_id update must be done seperatly. prspct_dmnsn_id is a simple surragate key (int,autonumber)in a warehouse dimension table. Any ideas why this version works and the other generates an internal SQL Server error?
Thanks
/* Set initial value for flag showing that the record was deleted
from Syteline. If it is still in Syteline, the second Update will
reset the value to false.*/
UPDATE Booking
SET deltd_from_syteline_indcr = -1;
GO
UPDATE Booking
SET custmr_dmnsn_id =V.custmr_dmnsn_id,
initl_cntct_mrktng_evnt_dmnsn_id =V.initl_cntct_mrktg_evnt_dmnsn_id,
order_class_id = V.order_class_id
FROM TMStagingArea.dbo.Booking_Load_V V
WHERE V.custmr_order_no = Booking.custmr_order_no;
GO
UPDATE Booking
SET prspct_dmnsn_id = V.prspct_dmnsn_id
FROM TMStagingArea.dbo.Booking_Load_V V
WHERE V.custmr_order_no = Booking.custmr_order_no;
GO
UPDATE Booking
SET rspbl_catalog_litrtr_dmnsn_id = V.mrktg_litrtr_dmnsn_id,
buyer_or_prospect_cd = V.buyer_or_prospect_cd,
clv_year_no = V.clv_year_no,
deltd_from_syteline_indcr = 0,
discount_perctg = V.discount_perctg,
freight_charge = V.freight_charge,
gross_price = V.gross_price,
misc_charge = V.misc_charge,
net_price = V.net_price,
response_interval_days = V.response_interval_days,
response_interval_weeks =V.response_interval_weeks,
sales_tax_charge = V.sales_tax_charge,
valid_order_flag = V.valid_order_indcr,
catalog_sent_dt_calndr_id = V.catalog_sent_dt_calndr_id,
custmr_first_order_dt_calndr_id = V.custmr_first_order_dt_calndr_id,
first_catalog_dt_calndr_id = V.first_catalog_dt_calndr_id,
first_due_dt_calndr_id = V.first_due_dt_calndr_id,
last_ship_dt_calndr_id = V.last_ship_dt_calndr_id,
order_dt_calndr_id = V.order_dt_calndr_id,
rspbl_mrktg_evnt_dmnsn_id = V.rspbl_mrktg_evnt_dmnsn_id,
entrd_mrktg_evnt_dmnsn_id = V.entrd_mrktg_evnt_dmnsn_id,
entrd_mrktg_litrtr_dmnsn_id = V.entrd_mrktg_litrtr_dmnsn_id,
FROM TMStagingArea.dbo.Booking_Load_V V,
WHERE V.custmr_order_no = Booking.custmr_order_no;
GO
July 25, 2002 at 7:34 am
By commenting out the entire SQL and uncommenting one line at a time and parsing each time I found that the following version of my original update works! For some reason the prspct_dmnsn_id update must be done seperatly. prspct_dmnsn_id is a simple surragate key (int,autonumber)in a warehouse dimension table. Any ideas why this version works and the other generates an internal SQL Server error?
Thanks
/* Set initial value for flag showing that the record was deleted
from Syteline. If it is still in Syteline, the second Update will
reset the value to false.*/
UPDATE Booking
SET deltd_from_syteline_indcr = -1;
GO
UPDATE Booking
SET custmr_dmnsn_id =V.custmr_dmnsn_id,
initl_cntct_mrktng_evnt_dmnsn_id =V.initl_cntct_mrktg_evnt_dmnsn_id,
order_class_id = V.order_class_id
FROM TMStagingArea.dbo.Booking_Load_V V
WHERE V.custmr_order_no = Booking.custmr_order_no;
GO
UPDATE Booking
SET prspct_dmnsn_id = V.prspct_dmnsn_id
FROM TMStagingArea.dbo.Booking_Load_V V
WHERE V.custmr_order_no = Booking.custmr_order_no;
GO
UPDATE Booking
SET rspbl_catalog_litrtr_dmnsn_id = V.mrktg_litrtr_dmnsn_id,
buyer_or_prospect_cd = V.buyer_or_prospect_cd,
clv_year_no = V.clv_year_no,
deltd_from_syteline_indcr = 0,
discount_perctg = V.discount_perctg,
freight_charge = V.freight_charge,
gross_price = V.gross_price,
misc_charge = V.misc_charge,
net_price = V.net_price,
response_interval_days = V.response_interval_days,
response_interval_weeks =V.response_interval_weeks,
sales_tax_charge = V.sales_tax_charge,
valid_order_flag = V.valid_order_indcr,
catalog_sent_dt_calndr_id = V.catalog_sent_dt_calndr_id,
custmr_first_order_dt_calndr_id = V.custmr_first_order_dt_calndr_id,
first_catalog_dt_calndr_id = V.first_catalog_dt_calndr_id,
first_due_dt_calndr_id = V.first_due_dt_calndr_id,
last_ship_dt_calndr_id = V.last_ship_dt_calndr_id,
order_dt_calndr_id = V.order_dt_calndr_id,
rspbl_mrktg_evnt_dmnsn_id = V.rspbl_mrktg_evnt_dmnsn_id,
entrd_mrktg_evnt_dmnsn_id = V.entrd_mrktg_evnt_dmnsn_id,
entrd_mrktg_litrtr_dmnsn_id = V.entrd_mrktg_litrtr_dmnsn_id,
FROM TMStagingArea.dbo.Booking_Load_V V,
WHERE V.custmr_order_no = Booking.custmr_order_no;
GO
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply