How Do I Use a SQL Stored Procedure with Crystal Reports 8.5

I have a non parmed stored procedure listed below:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER           Procedure R_SLM_APS1
as
begin
exec R_SLM_APS2
exec R_SLM_APS3
end


SELECT
    ENCOUNTER_OUTBOUND_FLAG,
    SESSION_END_DT,
    CONTRACT_NM,
    FIRST_DISPLAY,
    LAST_DISPLAY,
    BIRTH_DT,
    FIELD_VALUE,
    REFERRING_PHYSICIAN,
    PROVIDER_NM  


   
FROM
R_SLM_APS


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The Stored Procedures in the exec  portion are just building data in the report table that is displayed in the bottom of my stored procedure.

The stored procedure works perfectly in the SQL environment and displays the table.  When I insert the stored procedure into crystal reports 8.5, an error box pops up twice that no rows were returned and error in dll..    Does anyone have information on what i am doing wrong and/or how to rectify my situation
hosertooAsked:
Who is Participating?
 
Jai STech ArchCommented:
can you just try using the QUERY in the SQL rather than using it as a stored proc so that you can know where the actual problem...as far as i know SP's can be used in CR without any problems...
0
 
frodomanCommented:
Are you connecting to the same database that you tested in?

Have you verified that Crystal can connect to this database?

frodoman
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
hosertooAuthor Commented:
I tried that too, I combined the two called stored procedures into the calling sp and got the same result...
0
 
hosertooAuthor Commented:
I am connecting to the same database I tested in and I have verified that crystal  can connect.

I know the issue is in the execed sp's because I commented the exec's and ran again and it ran correctty.

0
 
hosertooAuthor Commented:
Here is one of the other sp's:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO







ALTER        Procedure R_SLM_APS2
as

truncate table R_SLM_APS
insert into R_SLM_APS (ENCOUNTER_OUTBOUND_FLAG,SESSION_END_DT,CONTRACT_NM,FIRST_DISPLAY,LAST_DISPLAY,BIRTH_DT,FIELD_VALUE,REFERRING_PHYSICIAN,PROVIDER_NM)
SELECT
    V_SESSION."ENCOUNTER_OUTBOUND_FLAG",
    V_SESSION."SESSION_END_DT",
    V_CONTRACT."CONTRACT_NM",
    V_PERSON."FIRST_DISPLAY",
    V_PERSON."LAST_DISPLAY",
    V_PERSON."BIRTH_DT",
    V_SESSION_USER_EXTENSION."FIELD_VALUE",
    V_TI_AHG_ENCOUNTER_UDFS."REFERRING_PHYSICIAN",
    V_EMBEDDED_REFERRAL_."PROVIDER_NM"

   
FROM
    ((((((cecc.dbo.V_SESSION V_SESSION INNER JOIN cecc.dbo.V_SESSION_SERVICE V_SESSION_SERVICE ON
        V_SESSION."SESSION_ID" = V_SESSION_SERVICE."SESSION_ID" AND
        V_SESSION."CONTRACT_ID" = V_SESSION_SERVICE."CONTRACT_ID")
    INNER JOIN cecc.dbo.V_CONTRACT V_CONTRACT ON
        V_SESSION."CONTRACT_ID" = V_CONTRACT."CONTRACT_ID")
    INNER JOIN cecc.dbo.V_PERSON V_PERSON ON
        V_SESSION."SESSION_PERSON_ID" = V_PERSON."PERSON_ID")
    INNER JOIN cecc.dbo.V_SESSION_USER_EXTENSION V_SESSION_USER_EXTENSION ON
        V_SESSION."SESSION_ID" = V_SESSION_USER_EXTENSION."SESSION_ID")
    INNER JOIN cecc.dbo.V_TI_AHG_ENCOUNTER_UDFS V_TI_AHG_ENCOUNTER_UDFS ON
        V_SESSION."SESSION_ID" = V_TI_AHG_ENCOUNTER_UDFS."SESSION_ID")
    INNER JOIN cecc.dbo."V_EMBEDDED_REFERRAL " V_EMBEDDED_REFERRAL_ ON
        V_SESSION_SERVICE."SERVICE_NUM" = V_EMBEDDED_REFERRAL_."SERVICE_NUM")
    INNER JOIN cecc.dbo.V_PROVIDER_SPECIALTY V_PROVIDER_SPECIALTY ON
        V_EMBEDDED_REFERRAL_."PROVIDER_PARTY_ID" = V_PROVIDER_SPECIALTY."PROVIDER_PARTY_ID"
WHERE
    V_SESSION."ENCOUNTER_OUTBOUND_FLAG" = 1 AND
    V_EMBEDDED_REFERRAL_."PROVIDER_LAST_NAME" IS NOT NULL AND
    V_TI_AHG_ENCOUNTER_UDFS."REFERRING_PHYSICIAN" IS NOT NULL AND
    (V_CONTRACT."CONTRACT_NM" LIKE 'AHG - Walker Internal Medicine' OR
    V_CONTRACT."CONTRACT_NM" LIKE 'AHG - Sherwood Fam Medical' OR
    V_CONTRACT."CONTRACT_NM" LIKE 'AHG - Pleasant Valley Fam Clin' OR
    V_CONTRACT."CONTRACT_NM" LIKE 'AHG - NLR Fam Practice Clinic' OR
    V_CONTRACT."CONTRACT_NM" LIKE 'AHG - Bryant Medical Clinic' OR
    V_CONTRACT."CONTRACT_NM" LIKE 'AHG - BH Occupational Hlth Cl.' OR
    V_CONTRACT."CONTRACT_NM" LIKE 'AHG - BH Fam Clinic-West' OR
    V_CONTRACT."CONTRACT_NM" LIKE 'AHG - BH Fam Clinic-Perryville' OR
    V_CONTRACT."CONTRACT_NM" LIKE 'AHG - BH Fam Clinic-Maumelle' OR
    V_CONTRACT."CONTRACT_NM" LIKE 'AHG - BH Fam Clinic-Malvern' OR
    V_CONTRACT."CONTRACT_NM" LIKE 'AHG - BH Fam Clinic-Lakewood' OR
    V_CONTRACT."CONTRACT_NM" LIKE 'AHG - BH Fam Clinic-England' OR
    V_CONTRACT."CONTRACT_NM" LIKE 'AHG - BH Fam Clinic-Cabot' OR
    V_CONTRACT."CONTRACT_NM" LIKE 'AHG - BH Fam Clinic-Bryant' OR
    V_CONTRACT."CONTRACT_NM" LIKE 'AHG - Benton Family Clinic' OR
    V_CONTRACT."CONTRACT_NM" LIKE 'AHG - Anderson Medical Clinic') AND
    V_PROVIDER_SPECIALTY."IS_PRIMARY_SPECIALTY" = 1 and
    V_SESSION_USER_EXTENSION."FIELD_LABEL" = 'Logician Date: MMDDYYYY Format'  
ORDER BY
    V_CONTRACT."CONTRACT_NM" ASC,
    V_TI_AHG_ENCOUNTER_UDFS."REFERRING_PHYSICIAN" ASC,
    V_PROVIDER_SPECIALTY."prof_specialty_desc" ASC,
    V_EMBEDDED_REFERRAL_."PROVIDER_NM" ASC







GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

0
 
James0628Commented:
As frodoman suggested, you probably need to turn on NOCOUNT (no "_" in the middle).  When you build temp tables or fill table variables, you'll normally get some messages, like row counts, before the actual data, and CR doesn't like that.  When you execute the procedure in a query window, you can see the messages being generated by the procedure.

 If the procedure generates any warning messages, like about nulls being ignored in aggregates, you'll probably also need to turn ANSI_WARNINGS off.

 If you want the final row count or need ANSI_WARNINGS on for part of the procedure, they can be reset after the temp tables or tables variables are filled.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.