RE: Executing Looping SP within DTS (SP provided)
From: Jay (Jay_at_discussions.microsoft.com)
Date: 12/06/04
- Next message: sudha: "DTS To Create Excel File with Dynamic Variables from VB."
- Previous message: SangHunJung: "DTS pkg runs fine but when I scheduled it to run, it fails."
- In reply to: Ed: "RE: Executing Looping SP within DTS"
- Next in thread: Jay: "RE: Executing Looping SP within DTS (with SP included)"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 6 Dec 2004 08:47:15 -0800
Ed
Please find enclosed the SP. I haven't included the DTS Package. The DTS
Package simply runs this procedure using execute sql task. The command is:
EXECUTE usp_load_volume
The same command works I execute it from Query Analyzer. However, when I
execute it from DTS it only loops once.
CREATE PROCEDURE [dbo].[usp_load_volume]
/***
$One-Liner
Takes wrk_volume data and loads into arc_volume first. Then takes
monthly difference between last periods
cumulative total and insert monthly volume amount into tbl_volume
$Example
EXECUTE usp_load_volume @i_meta_job_hist_id = 1
***/
( /* Input Parameters */
@i_meta_job_hist_id INT = 1,
@i_system_source NCHAR(3) = N'N/A'
)
AS
SET NOCOUNT ON
DECLARE @error INT,
@procedure_name SYSNAME,
@load_table SYSNAME,
@period_id INT,
@scenario_id INT,
@scenario_code VARCHAR(10),
@scenario_desc VARCHAR(50),
@screen_id INT,
@submission_defn_id INT,
@submission_defn_name NVARCHAR(50)
BEGIN
SET @procedure_name = OBJECT_NAME (@@PROCID)
SET @load_table = 'wrk_volume'
SELECT @screen_id = screen_id
FROM tbl_screen
WHERE screen_name = 'VOLUME'
EXEC usp_check_unique_period @i_meta_job_hist_id = @i_meta_job_hist_id,
@i_table_name = @load_table,
@o_period_id = @period_id OUTPUT
EXEC usp_check_unique_scenario @i_meta_job_hist_id = @i_meta_job_hist_id,
@i_table_name = @load_table,
@o_scenario_id = @scenario_id OUTPUT
SELECT @scenario_code = scenario_code,
@scenario_desc = scenario_desc
FROM tbl_scenario
WHERE scenario_id = @scenario_id
--- for monthly actual check that previous period has already been loaded
IF @scenario_code = '12' -- monthly actuals
AND SUBSTRING(CAST(@period_id as CHAR(6)),5,2) != '01' -- current
period not january
AND NOT EXISTS (
SELECT *
FROM arc_volume
WHERE period_id = @period_id - 1
AND scenario_id = @scenario_id
)
BEGIN
EXECUTE KQIS_POUGH_MetaDB..usp_raise_custom_error
@i_meta_job_hist_id = @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_custom_error = 404 -- Data not loaded for
previous period
GOTO error_return
END
--- for reference codes missing then raise error
IF EXISTS (
SELECT *
FROM wrk_volume v
LEFT JOIN tbl_service s
ON v.service_code = s.service_code
LEFT JOIN tbl_customer c
ON c.customer_code= v.customer_code
LEFT JOIN tbl_country cty
ON v.country_code = cty.country_code
LEFT JOIN tbl_coo_detail coo
ON cty.country_id = coo.country_id
LEFT JOIN tbl_currency cur
ON v.report_currency_code= cur.currency_code
LEFT JOIN tbl_currency cur2
ON v.transact_currency_code= cur2.currency_code
LEFT JOIN tbl_planning_entity p
ON p.planning_entity_code =v.planning_entity_code
LEFT JOIN tbl_scenario sc
ON v.scenario_code = sc.scenario_code
LEFT JOIN tbl_station st
ON p.station_id = st.station_id
WHERE s.service_code IS NULL
OR c.customer_code IS NULL
OR cty.country_code IS NULL
OR coo.country_id IS NULL
OR cur.currency_code IS NULL
OR cur2.currency_code IS NULL
OR p.planning_entity_code IS NULL
OR sc.scenario_code IS NULL
OR st.station_code IS NULL
)
BEGIN
EXECUTE KQIS_POUGH_MetaDB..usp_raise_custom_error
@i_meta_job_hist_id = @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_custom_error = 503 -- Missing reference data
GOTO error_return
END
--- delete records to avoid duplicates for the same scenario and period in
case of a re-run
DELETE a
FROM arc_volume a
WHERE a.period_id = @period_id
AND a.scenario_id = @scenario_id
EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = 'deleted records
where same scenario period already exist in arc_volume',
@i_error = @@error,
@i_rows = @@rowcount
--- Insert records from wrk to archive
INSERT INTO arc_volume
(
service_id,
report_currency_id,
local_currency_id,
transact_currency_id,
customer_id,
planning_entity_id,
scenario_id,
period_id,
coo_detail_id,
volume_amount_in_report_currency
)
SELECT
s.service_id,
cur.currency_id report_currency_id,
st.local_currency_id,
cur2.currency_id transact_currency_id,
c.customer_id,
p.planning_entity_id,
sc.scenario_id,
v.period_id,
coo.coo_detail_id,
volume_amount as volume_amount_in_report_currency
FROM wrk_volume v
INNER JOIN tbl_service s
ON v.service_code = s.service_code
INNER JOIN tbl_customer c
ON c.customer_code= v.customer_code
INNER JOIN tbl_country cty
ON v.country_code = cty.country_code
INNER JOIN tbl_coo_detail coo
ON cty.country_id = coo.country_id
INNER JOIN tbl_currency cur
ON v.report_currency_code= cur.currency_code
INNER JOIN tbl_currency cur2
ON v.transact_currency_code= cur2.currency_code
INNER JOIN tbl_planning_entity p
ON p.planning_entity_code =v.planning_entity_code
INNER JOIN tbl_scenario sc
ON v.scenario_code = sc.scenario_code
INNER JOIN tbl_station st
ON p.station_id = st.station_id
EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = 'inserted all
records into arc_volume from wrk_volume with cumulative_totals',
@i_error = @@error,
@i_rows = @@rowcount
-- reconcile all data from wrk_volume has been loaded into arc_volume
DECLARE @arc_volume_amt NUMERIC(18,4),
@wrk_volume_amt NUMERIC(18,4)
SELECT @wrk_volume_amt = SUM( volume_amount ) FROM wrk_volume
SELECT @arc_volume_amt = SUM (volume_amount_in_report_currency )
FROM arc_volume
WHERE period_id = @period_id
AND scenario_id = @scenario_id
IF ABS( @arc_volume_amt - @wrk_volume_amt ) > 1
BEGIN
EXECUTE KQIS_POUGH_MetaDB..usp_raise_custom_error
@i_meta_job_hist_id = @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_custom_error = 405 -- reconciliation mismatch
between staging and archive
GOTO error_return
END
-- Create submission definition
SELECT @submission_defn_name = 'VOLUME ' + scenario_desc + ' ' +
CAST(@period_id as varchar(8))
FROM tbl_scenario
WHERE scenario_id = @scenario_id
EXECUTE usp_create_submission_defn
@i_meta_job_hist_id = @i_meta_job_hist_id,
@i_scenario_id = @scenario_id,
@i_screen_id = @screen_id,
@i_period_id = @period_id,
@i_submission_defn_name = @submission_defn_name,
@o_submission_defn_id = @submission_defn_id OUTPUT
-- Insert individual submissions
INSERT INTO tbl_submission
( submission_datetime,
submission_defn_id,
planning_entity_id)
SELECT distinct getdate() as submission_date,
@submission_defn_id as submission_defn_id,
planning_entity_id
FROM tbl_planning_entity
EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = 'Insert
individual submissions',
@i_error = @@error,
@i_rows = @@rowcount
IF @error <> 0 GOTO error_return
-- load volume by looking at previous month
-- create previous months temp table
DECLARE @previous TABLE (
service_id INT,
coo_detail_id INT,
volume_amount NUMERIC(18,4),
transact_currency_id INT,
local_currency_id INT,
report_currency_id INT,
customer_id INT,
submission_id INT
)
INSERT INTO @previous
SELECT
v.service_id,
v.coo_detail_id,
v.volume_amount_in_report_currency as volume_amount,
v.transact_currency_id,
v.local_currency_id,
v.report_currency_id,
v.customer_id,
s.submission_id
FROM arc_volume v
INNER JOIN tbl_submission s
ON v.planning_entity_id = s.planning_entity_id
AND s.submission_defn_id = @submission_defn_id
WHERE v.period_id = @period_id - 1
AND v.scenario_id = @scenario_id
EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = '@previous
created with volume cumulative for previous month',
@i_error = @@error,
@i_rows = @@rowcount
-- create current months temp table
DECLARE @current TABLE (
service_id INT,
coo_detail_id INT,
volume_amount NUMERIC(18,4),
transact_currency_id INT,
local_currency_id INT,
report_currency_id INT,
customer_id INT,
submission_id INT
)
INSERT INTO @current
SELECT
v.service_id,
v.coo_detail_id,
v.volume_amount_in_report_currency as volume_amount,
v.transact_currency_id,
v.local_currency_id,
v.report_currency_id,
v.customer_id,
s.submission_id
FROM arc_volume v
INNER JOIN tbl_submission s
ON v.planning_entity_id = s.planning_entity_id
AND s.submission_defn_id = @submission_defn_id
WHERE v.period_id = @period_id
AND v.scenario_id = @scenario_id
EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = '@current
created with volume cumulative for current month',
@i_error = @@error,
@i_rows = @@rowcount
IF @error <> 0 GOTO error_return
--- Now load volume data by comparing the two months cumulative totals
INSERT INTO tbl_volume (
service_id,
coo_detail_id,
transact_currency_id,
local_currency_id,
report_currency_id,
customer_id,
submission_id,
volume_amount_in_report_currency )
SELECT
COALESCE(curr.service_id, prev.service_id)
service_id,
COALESCE(curr.coo_detail_id, prev.coo_detail_id)
coo_detail_id,
COALESCE(curr.transact_currency_id, prev.transact_currency_id)
transact_currency_id,
COALESCE(curr.local_currency_id, prev.local_currency_id)
local_currency_id,
COALESCE(curr.report_currency_id, prev.report_currency_id)
report_currency_id,
COALESCE(curr.customer_id, prev.customer_id)
customer_id,
COALESCE(curr.submission_id, prev.submission_id)
submission_id,
ISNULL (curr.volume_amount,0) - ISNULL(prev.volume_amount,0)
volume_amount
FROM @current CURR
FULL OUTER JOIN @previous PREV
ON curr.service_id = prev.service_id
AND curr.coo_detail_id = prev.coo_detail_id
AND curr.customer_id = prev.customer_id
AND curr.report_currency_id = prev.report_currency_id
AND curr.submission_id = prev.submission_id
EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = 'load volume
data with monthly totals calculated',
@i_error = @@error,
@i_rows = @@rowcount
IF @error <> 0 GOTO error_return
-- reconcile by matching cumulative total for that period,scenario from
arc with cumulative total from tbl_volume
DECLARE
@ods_volume_amt NUMERIC(18,4),
@ods_volume_cnt INT
SELECT
@ods_volume_amt = SUM(volume_amount_in_report_currency),
@ods_volume_cnt = COUNT(*)
FROM KQIS_POUGH_ODS..tbl_volume v
INNER JOIN KQIS_POUGH_ODS..tbl_submission s
ON v.submission_id = s.submission_id
INNER JOIN KQIS_POUGH_ODS..tbl_submission_defn sd
ON s.submission_defn_id = sd.submission_defn_id
AND sd.is_active_flag = 1 -- submission is active
WHERE sd.period_id > ROUND(@period_id/100,0) * 100 /** is > begin of
that year **/
AND sd.period_id <= @period_id /** and <= that
period **/
AND sd.scenario_id = @scenario_id /** and belongs to
that scenario **/
IF ABS(@ods_volume_amt - @arc_volume_amt) > 1
BEGIN
SELECT @ods_volume_amt as ods_volume_amt, @arc_volume_amt as
arc_volume_amt
EXECUTE KQIS_POUGH_MetaDB..usp_raise_custom_error
@i_meta_job_hist_id = @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_custom_error = 407 -- reconciliation mismatch
between archive and ODS
GOTO error_return
END
-- also update customer relation table using the relational from updated
volume table
EXECUTE usp_customer_relation_insert @i_meta_job_hist_id
=@i_meta_job_hist_id
success_return:
IF @error <> 0 GOTO error_return
RETURN 0
error_return:
IF @@trancount > 0
ROLLBACK TRANSACTION
RETURN @error
END
GO
"Ed" wrote:
> can you post your stored procedures and the code in DTS, too????
>
> Ed
>
> "Jay" wrote:
>
> > Hi
> >
> > I'm running a stored procedure from DTS. The SP has got a WHILE LOOP that
> > should execute 3 times. When I execute it from QUERY ANALYSER it executes 3
> > times. However, when I execute it from DTS it only executes once. The SP
> > procedure doesn't take any parameters.
> >
> > S/W: SQL Server 2000 - Developer Edition
> >
> > Any ideas what could be the issue?
> >
> > Many thanks
> > --
> > JayU
> >
> > --
> > JayU
- Next message: sudha: "DTS To Create Excel File with Dynamic Variables from VB."
- Previous message: SangHunJung: "DTS pkg runs fine but when I scheduled it to run, it fails."
- In reply to: Ed: "RE: Executing Looping SP within DTS"
- Next in thread: Jay: "RE: Executing Looping SP within DTS (with SP included)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
