Execute SQL Script from Crystal Report

[alectek]

Hi!
I have CR11 and SQL Server 2005. I created Stored Procedure to Delete all records from table and than base on Select Statement Insert back to this table new records base on parameter sent from Crystal Report. Later, I use this table as a Data Source for other report.
I created report base on this Stored Procedure and created Parameter in this report. However, when I run the report it just Alter Stored Procedure and do not run it. (do not' execute the script for Delete and Insert).
Any suggestions how to handle this situation? Thanks at advance.


Thanks.
Alec.

 

[hilfy]

Does your stored proc return a result set? If not, that may be part of the problem. Crystal will run stored procs that have a parameter that returns a Cursor. In Oracle, this is an IN OUT parameter and the parameter is a Ref Cursor data type. I'm not sure what the corresponding syntax/type is in SQL Server.

So, your stored proc needs to return the dataset for this report and then you can use the data out of the table for your other reports.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[alectek]

Dell, here is like it is look like:
set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go











-- =============================================

-- Author: AY

-- Create date: 7/28/2011

-- To create Table for UC417 report

-- =========================================



ALTER PROCEDURE [dbo].[MFS_Prior_Prev_Month_Balance]

@Location nvarchar(50) = null

AS



SET NOCOUNT ON



SET ANSI_WARNINGS OFF





Begin



if @Location = 'DuPage' Delete from [Prior_Prev_Mnth_SelfPay_Balance_DuPage]

if @Location = 'Blue Island' Delete from [Prior_Prev_Mnth_SelfPay_Balance_BlueIsland]





end





if @Location = 'DuPage'

INSERT INTO Prior_Prev_Mnth_SelfPay_Balance_Dupage



([Location]



,ClientOID

,ClientID

,ClientFName

,ClientLName

,NbrOfOpenCC

,SelfPayBalance

,UnappliedPayment

,ClaimBalanceForward

,TotalBalance)





SELECT "MFS_V_Agency_Location_Current"."NAME_LINE_1" As Location,

"Client"."OID" As ClientOID,

"Client"."ID" As ClientID,

"Client"."FName" As ClientFName,

"Client"."LName" As ClientLName,

max("MFS_V_Number_of_Current_Open_CC_per_Client"."NumberOfOpenCc") as NbrOfOpenCC,

Max("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."SelfPayBal") as SelfPayBalance,

Max("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."UnapPayment") as UnappliedPayment,

Max("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."ClaimBalanceForwardSum") AS ClaimBalanceForward,

max((isnull("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."SelfPayBal",0)

+

isnull("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."UnapPayment", 0)+

isnull("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."ClaimBalanceForwardSum",0))) As TotalBalance



FROM ((((."Client" "Client" LEFT OUTER JOIN

dbo."CostCtrGrp_Helper" "CostCtrGrp_Helper" ON

"Client"."OID"="CostCtrGrp_Helper"."Client_OID") LEFT OUTER JOIN

dbo."MFS_V_Number_of_Current_Open_CC_per_Client"

"MFS_V_Number_of_Current_Open_CC_per_Client"

ON "Client"."OID"="MFS_V_Number_of_Current_Open_CC_per_Client"."Client_OID") LEFT OUTER JOIN

dbo."MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"

"MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt" ON

"Client"."OID"="MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."Client_OID") LEFT OUTER JOIN

dbo."CostCtrGrp" "CostCtrGrp" ON

"CostCtrGrp_Helper"."CostCtrGrp_OID"="CostCtrGrp"."OID") LEFT OUTER JOIN

dbo."MFS_V_Agency_Location_Current" "MFS_V_Agency_Location_Current" ON

"CostCtrGrp"."AGENCY_LOCATION_MONIKER"="MFS_V_Agency_Location_Current"."OID"



Group by "MFS_V_Agency_Location_Current"."NAME_LINE_1",

"Client"."OID",

"Client"."ID",

"Client"."FName",

"Client"."LName"

having "MFS_V_Agency_Location_Current"."NAME_LINE_1" is not null

and "MFS_V_Agency_Location_Current"."NAME_LINE_1" = @Location



if @Location = 'Blue Island'

INSERT INTO Prior_Prev_Mnth_SelfPay_Balance_BlueIsland

([Location]



,ClientOID

,ClientID

,ClientFName

,ClientLName

,NbrOfOpenCC

,SelfPayBalance

,UnappliedPayment

,ClaimBalanceForward

,TotalBalance)





SELECT "MFS_V_Agency_Location_Current"."NAME_LINE_1" As Location,

"Client"."OID" As ClientOID,

"Client"."ID" As ClientID,

"Client"."FName" As ClientFName,

"Client"."LName" As ClientLName,

max("MFS_V_Number_of_Current_Open_CC_per_Client"."NumberOfOpenCc") as NbrOfOpenCC,

Max("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."SelfPayBal") as SelfPayBalance,

Max("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."UnapPayment") as UnappliedPayment,

Max("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."ClaimBalanceForwardSum") AS ClaimBalanceForward,

max((isnull("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."SelfPayBal",0)

+

isnull("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."UnapPayment", 0)+

isnull("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."ClaimBalanceForwardSum",0))) As TotalBalance



FROM ((((."Client" "Client" LEFT OUTER JOIN

dbo."CostCtrGrp_Helper" "CostCtrGrp_Helper" ON

"Client"."OID"="CostCtrGrp_Helper"."Client_OID") LEFT OUTER JOIN

dbo."MFS_V_Number_of_Current_Open_CC_per_Client"

"MFS_V_Number_of_Current_Open_CC_per_Client"

ON "Client"."OID"="MFS_V_Number_of_Current_Open_CC_per_Client"."Client_OID") LEFT OUTER JOIN

dbo."MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"

"MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt" ON

"Client"."OID"="MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."Client_OID") LEFT OUTER JOIN

dbo."CostCtrGrp" "CostCtrGrp" ON

"CostCtrGrp_Helper"."CostCtrGrp_OID"="CostCtrGrp"."OID") LEFT OUTER JOIN

dbo."MFS_V_Agency_Location_Current" "MFS_V_Agency_Location_Current" ON

"CostCtrGrp"."AGENCY_LOCATION_MONIKER"="MFS_V_Agency_Location_Current"."OID"



Group by "MFS_V_Agency_Location_Current"."NAME_LINE_1",

"Client"."OID",

"Client"."ID",

"Client"."FName",

"Client"."LName"

having "MFS_V_Agency_Location_Current"."NAME_LINE_1" is not null

and "MFS_V_Agency_Location_Current"."NAME_LINE_1" = @Location







SET ANSI_WARNINGS ON



SET NOCOUNT OFF



RETURN



Thanks.
Alec.

 

[hilfy]

Altering a procedure does not run it. So, if this is the code that's in your report, it's not really going to do anything for you.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[alectek]

This is actually my question. How to call this procedure from report to run it, not just alter.

Thanks.
Alec.

 

[hilfy]

You will need to write a command to get the data for your report instead of linking tables together. At the beginning of the command, put somthing like this:

MFS_Prior_Prev_Month_Balance({?Location})
GO
Select....

I don't know if Crystal will let you do this, but I think this is the format it needs to be in in order to get it to work.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.