Crystal Reports

pts.
Tags:
Certifications
Crystal Reports
Data warehousing applications
Development
Project management
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,

Answer Wiki

Thanks. We'll let you know when a new response is added.

i just wanna know

r u using this procedure in crystal report or in ur prog.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Stephen09
    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
    15 pointsBadges:
    report
  • Jeager66
    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
    40 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following