Crystal Reports
I am working on a report in Crystal Report,
I have a stored Procedure like this
ALTER PROCEDURE [dbo].[usp_cust_by_activity]
@carrier as VARCHAR(10)
AS
--drop table #custA
--drop table #custPA
--drop table #cdrall
--drop table #cdrPrevMonth
--SET @carrier = 'PR' --'PR', 'RG'
SELECT DISTINCT fld_cust_id INTO #custA FROM tbl_cust_ld WHERE fld_ld_status = 'active' and fld_cust_id in (SELECT fld_cust_id FROM tbl_cust_ld_phone WHERE fld_carrier = @carrier)
SELECT fld_cust_id, fld_btn INTO #custPA FROM dbo.tbl_cust_ld_phone WHERE fld_status = 'active' and fld_cust_id in (SELECT fld_cust_id FROM #custA) and fld_carrier = @carrier
SELECT DISTINCT fld_btn INTO #cdrall FROM tbl_cdr
SELECT DISTINCT fld_btn INTO #cdrPrevMonth FROM tbl_cdr WHERE (DATEDIFF(m, fld_call_date, Convert(nvarchar,DATEADD("Month", -1,GETDATE()),101)) = 0)
--reports
SELECT 'Total Cust' as custStatus, COUNT(*) as total FROM tbl_cust_ld WHERE fld_cust_id in (SELECT fld_cust_id FROM tbl_cust_ld_phone WHERE fld_carrier = @carrier)
UNION ALL
SELECT 'Active Cust' as custStatus,COUNT(*) as total FROM #custA
UNION ALL
SELECT 'Active Cust with Activity' as custStatus,COUNT( DISTINCT fld_cust_id) AS total FROM #custPA WHERE fld_btn IN (SELECT fld_btn FROM #cdrall)
UNION ALL
SELECT 'Active Cust with Activity in Previous Month' as custStatus, COUNT( DISTINCT fld_cust_id) AS total_cust_active_with_activity_previous_month FROM #custPA WHERE fld_btn IN (SELECT fld_btn FROM #cdrPrevMonth)
-------------------------------
I want to be able to drop the #custPA ....and other temporary tables each time I execute this procedure.
First time it goes thru, but next time it gives errors that it can't delete the temp tables.
--------------------------------
I also want to be able to use this StoredProc in my Crystal Report.
When I say new item and select DataSet, then when I drag and Drop my Stored Proc
I get this error
"The XML Schema could not be interpretted from this object"
Please could some body help me resolve this problem.
I want to be able to use Stored Procedures with parameters in Crystal Report.
Thank you,
Software/Hardware used:
Software/Hardware used:
ASKED:
April 18, 2006 6:08 PM
UPDATED:
June 11, 2011 3:23 AM
Answer Wiki:
i just wanna know
r u using this procedure in crystal report or in ur prog.
To see all answers submitted to the Answer Wiki: View Answer History.
If you are planning on dropping and recreating the tables used in the stored procedure, why not use tables in a regular database instead of tempdb? Then, you could use IF EXISTS to check that the table is there before trying to delete it. Also, temp tables are destroyed whevever your connection ends. I’m guessing that you can’t delete the temp tables the second time around becuase they don’t exist anymore.
Hope this helps,
Stephen
you could use this code before dropping each of your temp tables
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N’tempdb..#tmp’))
DROP TABLE #tmp
the #tmp in each case is the name of your temp table