I'm trying run a Stored Procedure using Query Analyzer. The Stored Procedure has two parameters (@start_date_CY and @start_date_LY) they are datetime which I do a convert
SELECT CONVERT(varchar(8), @start_date_CY, 1). The following is my exec:
exec [brxmas].[mc_119employment2_sp] @start_date_CY, @start_date_LY
The output from Query Analyzer is as follows:
No column name
06/19/05
No column name
06/20/04
And, it has all the data for the timeframe. When I create a new Crystal Report (11.0) there is NO data and I can't see any fields from the 'Stored Procedure'.
Here is my Stored Procedure
CREATE PROCEDURE [bchristmas].[mc_119employment2_sp]
@start_date_CY smalldatetime = null,
@start_date_LY smalldatetime = null
AS
--Display input dates in mm/dd/yy format
SELECT CONVERT(varchar(8), @start_date_CY, 1)
SELECT CONVERT(varchar(8), @start_date_LY, 1)
SELECT
ad_num = mc_ad_insertion.ad_num
, source = mc_acct_master_view.source
, class_code_int = mc_ad_detail_view.class_code_int
, class_code_desc = mc_class.class_code_desc
, adv_type = mc_ad_detail_view.adv_type
, period_year = mc_period_dates.period_year
, prod_code = mc_ad_insertion.prod_code
, credit_amt = mc_ad_cost_view.credit_amt
, debit_amt = mc_ad_cost_view.debit_amt
, obj_code = mc_ad_cost_view.obj_code
, obj_code_sub = mc_ad_cost_view.obj_code_sub
, obj_code_desc = mc_obj_code_vals.obj_code_desc
, insertion_date = mc_ad_insertion.insertion_date
, effective_lines =
CASE
WHEN mc_ad_cost_view.obj_code_sub = '1'
THEN mc_ad_insertion.effective_lines
ELSE 0
END
INTO #temp5
-- ORDER BY "mc_ad_detail_view"."class_code_int"
FROM mc_acct_master_view
INNER JOIN ((mc_ad_insertion INNER JOIN mc_ad_cost_view
ON (mc_ad_insertion.ad_num = mc_ad_cost_view.ad_num) AND
(mc_ad_insertion.insertion_num = mc_ad_cost_view.trans_id) AND
(mc_ad_insertion.bus_unit_id = mc_ad_cost_view.bus_unit_id) AND
(mc_ad_insertion.pub_id = mc_ad_cost_view.pub_id) AND
(mc_ad_insertion.acct_num = mc_ad_cost_view.acct_num))
INNER JOIN mc_period_dates
ON mc_ad_insertion.insertion_date = mc_period_dates.calendar_date)
INNER JOIN mc_ad_detail_view
ON (mc_ad_insertion.bus_unit_id = mc_ad_detail_view.bus_unit_id) AND
(mc_ad_insertion.pub_id = mc_ad_detail_view.pub_id) AND
(mc_ad_insertion.acct_num = mc_ad_detail_view.acct_num) AND
(mc_ad_insertion.ad_num = mc_ad_detail_view.ad_num) AND
(mc_ad_insertion.entry_date_time = mc_ad_detail_view.entry_date_time)
ON (mc_acct_master_view.bus_unit_id = mc_ad_detail_view.bus_unit_id) AND
(mc_acct_master_view.pub_id = mc_ad_detail_view.pub_id) AND
(mc_acct_master_view.acct_num = mc_ad_detail_view.acct_num)
INNER JOIN mc_class
ON mc_ad_detail_view.class_code =mc_class.class_code
INNER JOIN mc_obj_code_vals
ON mc_ad_cost_view.obj_code = mc_obj_code_vals.obj_code
WHERE
mc_ad_detail_view.bus_unit_id = 'MMB' AND
mc_ad_detail_view.pub_id = 'MMB' AND
mc_ad_detail_view.adv_type IN ( 'CT', 'GT') and
mc_ad_detail_view.class_code_int IN ('510') AND
mc_ad_cost_view.obj_code NOT IN ('15', '19', '29', '50', '59', '485', '900', '905', '915') AND
mc_ad_insertion.prod_code NOT IN ('ELDY','MBEX', 'MBXC') AND
mc_ad_insertion.insertion_date <= @start_date_CY and
mc_ad_insertion.insertion_date >= @start_date_LY
CREATE NONCLUSTERED INDEX indx_temp5 ON #temp5 (class_code_int)
SELECT
ad_num
, source
, class_code_int
, class_code_desc
, adv_type
, period_year
, prod_code
, credit_amt
, debit_amt
, obj_code
, obj_code_sub
, obj_code_desc
, insertion_date
, effective_lines
FROM #temp5
I would appreciate it if someone can tell me what I'm doing wrong. Thanks in advance for your help.
SELECT CONVERT(varchar(8), @start_date_CY, 1). The following is my exec:
exec [brxmas].[mc_119employment2_sp] @start_date_CY, @start_date_LY
The output from Query Analyzer is as follows:
No column name
06/19/05
No column name
06/20/04
And, it has all the data for the timeframe. When I create a new Crystal Report (11.0) there is NO data and I can't see any fields from the 'Stored Procedure'.
Here is my Stored Procedure
CREATE PROCEDURE [bchristmas].[mc_119employment2_sp]
@start_date_CY smalldatetime = null,
@start_date_LY smalldatetime = null
AS
--Display input dates in mm/dd/yy format
SELECT CONVERT(varchar(8), @start_date_CY, 1)
SELECT CONVERT(varchar(8), @start_date_LY, 1)
SELECT
ad_num = mc_ad_insertion.ad_num
, source = mc_acct_master_view.source
, class_code_int = mc_ad_detail_view.class_code_int
, class_code_desc = mc_class.class_code_desc
, adv_type = mc_ad_detail_view.adv_type
, period_year = mc_period_dates.period_year
, prod_code = mc_ad_insertion.prod_code
, credit_amt = mc_ad_cost_view.credit_amt
, debit_amt = mc_ad_cost_view.debit_amt
, obj_code = mc_ad_cost_view.obj_code
, obj_code_sub = mc_ad_cost_view.obj_code_sub
, obj_code_desc = mc_obj_code_vals.obj_code_desc
, insertion_date = mc_ad_insertion.insertion_date
, effective_lines =
CASE
WHEN mc_ad_cost_view.obj_code_sub = '1'
THEN mc_ad_insertion.effective_lines
ELSE 0
END
INTO #temp5
-- ORDER BY "mc_ad_detail_view"."class_code_int"
FROM mc_acct_master_view
INNER JOIN ((mc_ad_insertion INNER JOIN mc_ad_cost_view
ON (mc_ad_insertion.ad_num = mc_ad_cost_view.ad_num) AND
(mc_ad_insertion.insertion_num = mc_ad_cost_view.trans_id) AND
(mc_ad_insertion.bus_unit_id = mc_ad_cost_view.bus_unit_id) AND
(mc_ad_insertion.pub_id = mc_ad_cost_view.pub_id) AND
(mc_ad_insertion.acct_num = mc_ad_cost_view.acct_num))
INNER JOIN mc_period_dates
ON mc_ad_insertion.insertion_date = mc_period_dates.calendar_date)
INNER JOIN mc_ad_detail_view
ON (mc_ad_insertion.bus_unit_id = mc_ad_detail_view.bus_unit_id) AND
(mc_ad_insertion.pub_id = mc_ad_detail_view.pub_id) AND
(mc_ad_insertion.acct_num = mc_ad_detail_view.acct_num) AND
(mc_ad_insertion.ad_num = mc_ad_detail_view.ad_num) AND
(mc_ad_insertion.entry_date_time = mc_ad_detail_view.entry_date_time)
ON (mc_acct_master_view.bus_unit_id = mc_ad_detail_view.bus_unit_id) AND
(mc_acct_master_view.pub_id = mc_ad_detail_view.pub_id) AND
(mc_acct_master_view.acct_num = mc_ad_detail_view.acct_num)
INNER JOIN mc_class
ON mc_ad_detail_view.class_code =mc_class.class_code
INNER JOIN mc_obj_code_vals
ON mc_ad_cost_view.obj_code = mc_obj_code_vals.obj_code
WHERE
mc_ad_detail_view.bus_unit_id = 'MMB' AND
mc_ad_detail_view.pub_id = 'MMB' AND
mc_ad_detail_view.adv_type IN ( 'CT', 'GT') and
mc_ad_detail_view.class_code_int IN ('510') AND
mc_ad_cost_view.obj_code NOT IN ('15', '19', '29', '50', '59', '485', '900', '905', '915') AND
mc_ad_insertion.prod_code NOT IN ('ELDY','MBEX', 'MBXC') AND
mc_ad_insertion.insertion_date <= @start_date_CY and
mc_ad_insertion.insertion_date >= @start_date_LY
CREATE NONCLUSTERED INDEX indx_temp5 ON #temp5 (class_code_int)
SELECT
ad_num
, source
, class_code_int
, class_code_desc
, adv_type
, period_year
, prod_code
, credit_amt
, debit_amt
, obj_code
, obj_code_sub
, obj_code_desc
, insertion_date
, effective_lines
FROM #temp5
I would appreciate it if someone can tell me what I'm doing wrong. Thanks in advance for your help.