I am attempting to add a stored procedure to my Crystal report but keep running into the "Database Connector Error" Cannot obtain error message from serve" error.
Not sure what I'm doing wrong. The stored procedure works fine in SQL Server and returns one value, requires three parameters. i have tried setting the values to "allow nulls" as well as tried adding some sample data that I knew worked.
Here is a copy of my stored procedure:
ALTER PROCEDURE dbo.sp_GetCurrentPremiumforCrystal @PlanCode VARCHAR(20), @IMISID VARCHAR(10), @IS_SUPP_SPOUSE VARCHAR(1)
AS
BEGIN
DECLARE @MyAge INT
DECLARE @MyCoverageAmt MONEY
DECLARE @MyPremium MONEY
DECLARE @MyCoverageType VARCHAR(1)
SET @MyCoverageAmt = dbo.fn_GetCurrentCvg(@IMISID, @PlanCode, @IS_SUPP_SPOUSE)
SET @MyCoverageType = dbo.fn_GetCvgType(@IMISID, @PlanCode)
SET @MyAge = dbo.fn_GetMemberAge(@IMISID, @IS_SUPP_SPOUSE)
SET @MyPremium = 0
IF (@IS_SUPP_SPOUSE = 'Y')
BEGIN
SET @MyPremium =
CASE @PlanCode
WHEN 'UBA' THEN (SELECT [PremiumAmt] FROM dbo.[Lookup_Premium_UBA] WHERE ([CoverageType] = 'S') AND ([CoverageAmt] = @MyCoverageAmt) AND ([MinAge] <= @MyAge) AND ([MaxAge] >= @MyAge))
WHEN 'UBB' THEN (SELECT [PremiumAmt] FROM dbo.[Lookup_Premium_UBB] WHERE ([CoverageType] = 'S') AND ([CoverageAmt] = @MyCoverageAmt) AND ([MinAge] <= @MyAge) AND ([MaxAge] >= @MyAge))
ELSE 0
END
END
ELSE
BEGIN
SET @MyPremium =
CASE @PlanCode
WHEN 'GAP-Mutual' THEN (SELECT [PremiumAmt] FROM dbo.[Lookup_Premium_GAP_Mutual] WHERE ([CoverageType] = @MyCoverageType) AND ([CoverageAmt] = @MyCoverageAmt))
WHEN 'GAP-NatWide' THEN (SELECT [PremiumAmt] FROM dbo.[Lookup_Premium_GAP_NatWide] WHERE ([CoverageType] = @MyCoverageType) AND ([CoverageAmt] = @MyCoverageAmt))
WHEN 'Income' THEN (SELECT [PremiumAmt] FROM dbo.[Lookup_Premium_IRP] WHERE ([CoverageType] = @MyCoverageType) AND ([CoverageAmt] = @MyCoverageAmt) AND ([MinAge] <= @MyAge) AND ([MaxAge] >= @MyAge))
WHEN 'UBA' THEN (SELECT [PremiumAmt] FROM dbo.[Lookup_Premium_UBA] WHERE ([CoverageType] = @MyCoverageType) AND ([CoverageAmt] = @MyCoverageAmt) AND ([MinAge] <= @MyAge) AND ([MaxAge] >= @MyAge))
WHEN 'UBB' THEN (SELECT [PremiumAmt] FROM dbo.[Lookup_Premium_UBB] WHERE ([CoverageType] = @MyCoverageType) AND ([CoverageAmt] = @MyCoverageAmt) AND ([MinAge] <= @MyAge) AND ([MaxAge] >= @MyAge))
ELSE 0
END
END
IF (@MyPremium IS NULL) SET @MyPremium = 0
RETURN @MyPremium
END
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.