Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
urpalshu
Starting Member
21 Posts |
 Posted - 2006-04-18 : 18:04:09
|
| I am working on a report in Crystal Report,I have a stored Procedure like thisALTER 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 = @carrierSELECT DISTINCT fld_btn INTO #cdrall FROM tbl_cdrSELECT DISTINCT fld_btn INTO #cdrPrevMonth FROM tbl_cdr WHERE (DATEDIFF(m, fld_call_date, Convert(nvarchar,DATEADD("Month", -1,GETDATE()),101)) = 0)--reportsSELECT '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 ALLSELECT 'Active Cust' as custStatus,COUNT(*) as total FROM #custAUNION ALLSELECT '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 ALLSELECT '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, |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-18 : 18:06:27
|
| You don't need to drop the temporary table. They only exist in the session that is running the stored procedure. Once the stored procedure is finished, the temporary tables no longer exist.Tara Kizeraka tduggan |
 |
|
|
urpalshu
Starting Member
21 Posts |
Posted - 2006-04-18 : 19:34:15
|
quote:
Originally posted by tkizer
You don't need to drop the temporary table. They only exist in the session that is running the stored procedure. Once the stored procedure is finished, the temporary tables no longer exist.Tara Kizeraka tduggan
I have comented the part where I drop the tables....Thank you, |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-04-19 : 04:00:03
|
quote:
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
You need to use seperate name for temp tables. It seems you used same name #custAMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Crystal Report