Skip to Content
avatar image
Former Member

Database connector error in Crystal Report when adding stored procedure

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jan 12, 2015 at 10:09 PM

    Hello,

    try et the end of the procedure

    IF (@MyPremium IS NULL) SET @MyPremium = 0

      RETURN @MyPremium

    END

    IF (@MyPremium IS NULL) SET @MyPremium = 0

         select @MyPremium

    END

    I hope it helps.

    János

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      This actually solved two problems for me!  I was also having a rounding issue when I executed directly through in SQL Server.  PERFECT!  Thank you!