?
Solved

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

Posted on 2007-10-04
7
Medium Priority
?
1,370 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:hosertoo
7 Comments
 
LVL 14

Expert Comment

by:Jai S
ID: 20014493
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
 
LVL 42

Expert Comment

by:frodoman
ID: 20014650
Are you connecting to the same database that you tested in?

Have you verified that Crystal can connect to this database?

frodoman
0
 

Author Comment

by:hosertoo
ID: 20014660
I tried that too, I combined the two called stored procedures into the calling sp and got the same result...
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:hosertoo
ID: 20014702
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
 

Author Comment

by:hosertoo
ID: 20014723
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
 
LVL 42

Accepted Solution

by:
frodoman earned 2000 total points
ID: 20015593
0
 
LVL 35

Expert Comment

by:James0628
ID: 20018244
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question