Crystal crashing when fetching stored procedure

[MSGCR]

I have a stored procedure which executes another stored procedure and uses those results in the final dataset being returned by my stored proc. Using Crystal 9 Report Designer, the report stalls when trying to access this stored proc. Does CR 9 have a problem w/stored procs when they have other embedded sp's? Any suggestions. Thanks

This is my stored proc:

-- ----------------------------------------------------------------------------
CREATE PROCEDURE
-- ----------------------------------------------------------------------------
sp_UIHODeclarations
-- ----------------------------------------------------------------------------
@Policy decimal(15, 0),
@PolicyVersion smallint
-- ----------------------------------------------------------------------------
AS
-- ----------------------------------------------------------------------------
DECLARE @Endorsements varchar(600)
-- ----------------------------------------------------------------------------
BEGIN
-- ----------------------------------------------------------------------------
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- ----------------------------------------------------------------------------
SELECT
-- ----------------------------------------------------------------------------
@Endorsements = T.Endorsements
-- ----------------------------------------------------------------------------
FROM
-- ----------------------------------------------------------------------------
UIHO T
-- ----------------------------------------------------------------------------
WHERE
-- ----------------------------------------------------------------------------
T.Id = @Policy AND
T.VersionNo = @PolicyVersion
-------------------------------------------------------------------------------
CREATE TABLE #Temp_UIHOEndorsements_ForDec (FormNo varchar(10), Premium decimal(11, 2))
-------------------------------------------------------------------------------
EXECUTE sp_UIHOEndorsements '#Temp_UIHOEndorsements_ForDec', @endorsements
-- ----------------------------------------------------------------------------
SELECT
-- ----------------------------------------------------------------------------
HO101 = MAX(CASE WHEN FormNo = 'HO101' THEN 'Y' ELSE 'N' END),
HO101Premium = MAX(CASE WHEN FormNo = 'HO101' THEN Premium ELSE 0 END),
HO105 = MAX(CASE WHEN FormNo = 'HO105' THEN 'Y' ELSE 'N' END),
HO105Premium = MAX(CASE WHEN FormNo = 'HO105' THEN Premium ELSE 0 END),
HO110 = MAX(CASE WHEN FormNo = 'HO110' THEN 'Y' ELSE 'N' END),
HO110Premium = MAX(CASE WHEN FormNo = 'HO110' THEN Premium ELSE 0 END),
HO112 = MAX(CASE WHEN FormNo = 'HO112' THEN 'Y' ELSE 'N' END),
HO112Premium = MAX(CASE WHEN FormNo = 'HO112' THEN Premium ELSE 0 END),
HO113 = MAX(CASE WHEN FormNo = 'HO113' THEN 'Y' ELSE 'N' END),
HO113Premium = MAX(CASE WHEN FormNo = 'HO113' THEN Premium ELSE 0 END),
HO120 = MAX(CASE WHEN FormNo = 'HO120' THEN 'Y' ELSE 'N' END),
HO120Premium = MAX(CASE WHEN FormNo = 'HO101' THEN Premium ELSE 0 END),
HO126 = MAX(CASE WHEN FormNo = 'HO126' THEN 'Y' ELSE 'N' END),
HO126Premium = MAX(CASE WHEN FormNo = 'HO126' THEN Premium ELSE 0 END),
HO140 = MAX(CASE WHEN FormNo = 'HO140' THEN 'Y' ELSE 'N' END),
HO140Premium = MAX(CASE WHEN FormNo = 'HO140' THEN Premium ELSE 0 END),
HO145 = MAX(CASE WHEN FormNo = 'HO145' THEN 'Y' ELSE 'N' END),
HO145Premium = MAX(CASE WHEN FormNo = 'HO145' THEN Premium ELSE 0 END),
HO160 = MAX(CASE WHEN FormNo = 'HO160' THEN 'Y' ELSE 'N' END),
HO160Premium = MAX(CASE WHEN FormNo = 'HO160' THEN Premium ELSE 0 END),
HO170 = MAX(CASE WHEN FormNo = 'HO170' THEN 'Y' ELSE 'N' END),
HO170Premium = MAX(CASE WHEN FormNo = 'HO170' THEN Premium ELSE 0 END),
HO201 = MAX(CASE WHEN FormNo = 'HO201' THEN 'Y' ELSE 'N' END),
HO201Premium = MAX(CASE WHEN FormNo = 'HO201' THEN Premium ELSE 0 END),
HO215 = MAX(CASE WHEN FormNo = 'HO215' THEN 'Y' ELSE 'N' END),
HO215Premium = MAX(CASE WHEN FormNo = 'HO215' THEN Premium ELSE 0 END),
HO225 = MAX(CASE WHEN FormNo = 'HO225' THEN 'Y' ELSE 'N' END),
HO225Premium = MAX(CASE WHEN FormNo = 'HO225' THEN Premium ELSE 0 END),
HO301 = MAX(CASE WHEN FormNo = 'HO301' THEN 'Y' ELSE 'N' END),
HO301Premium = MAX(CASE WHEN FormNo = 'HO301' THEN Premium ELSE 0 END),
HO330 = MAX(CASE WHEN FormNo = 'HO330' THEN 'Y' ELSE 'N' END),
HO330Premium = MAX(CASE WHEN FormNo = 'HO330' THEN Premium ELSE 0 END)
-- ----------------------------------------------------------------------------
INTO
-- ----------------------------------------------------------------------------
#Temp_Endorsements
-- ----------------------------------------------------------------------------
FROM
-- ----------------------------------------------------------------------------
#Temp_UIHOEndorsements_ForDec
-- ----------------------------------------------------------------------------
SELECT
-- ----------------------------------------------------------------------------
A.Id,
A.RealEffDate,
A.renewalOfPolicyNo,
A.name,
Address = REPLACE(A.Address,'##',' '),
A.lot,
A.block,
A.addition,
A.ChangeType,
A.city,
A.county,
A.state,
A.zipCode,
MailingAddress = REPLACE(A.mailingAddress,'##',' '),
A.mailingCity,
A.mailingState,
A.mailingZipCode,
PolicyType =
(CASE PolicyType
WHEN '1' THEN 'HO-A'
WHEN '2' THEN 'HO-B'
WHEN '3' THEN 'HO-C'
WHEN '4' THEN 'HO-BT'
WHEN '5' THEN 'HO-CT'
WHEN '6' THEN 'HO-CONB'
WHEN '7' THEN 'HO-CONC'
ELSE: ''
END),
A.ReferenceNo,
A.uarCoverageEffDate,
A.uarCoverageExpDate,
A.RatingTerritory,
ProtectionClass,
A.constructionType,
A.yearBuilt,
A.roofType,
A.cityLimit,
A.dwelling,
A.otherStructures,
PersonalProperty,
PersonalPropertyOffPremises,
A.liability,
A.medicalPayments,
A.ded1,
A.ded1Amount,
A.ded2,
A.ded2Amount,
--A.additionalResidencePremium,
A.basePremium,
A.basicPremium,
A.standardPremium,
ProtectionConstructionFactor,
A.amountOfInsuranceFactor,
FlexFactor,
A.ded1Factor,
A.ded2Factor,
A.HO101Factor,
A.HO140Factor,
A.HO145Rate,
A.HO330Rate,
A.dwellingECPremium,
A.dwellingECMultiplier,
PersonalPropertyECPremium,
PersonalPropertyECMultiplier,
LiabilityBasePremium = A.LiabilityBasePremiumMainDwelling,
A.otherStructuresPremiumRate,
A.lossOfUse,
FireSmokeAlarmType,
A.alarmRate,
A.AutomaticSprinklerSystem,
A.AutomaticSprinklerSystemRate,
A.AutomaticSprinklerSystemCT,
A.AutomaticSprinklerSystemCTRate,
A.HomeSecurityDevice,
A.HomeSecurityDeviceRate,
A.HomeSecurityDeviceS6A1,
A.HomeSecurityDeviceS6A1Rate,
A.HomeSecurityDeviceS6A2,
A.HomeSecurityDeviceS6A2Rate,
FireExtinguisher,
FireExtinguisherRate,
A.AgeOfRisk,
A.AgeOfRiskRate,
A.NonCombustibleRoof,
A.NonCombustibleRoofRate,
A.CareAndConditionOfPremises,
A.CareAndConditionOfPremisesRate,
A.LossExperienceCredit,
A.LossExperienceCreditRate,
A.CompanionPolicy,
A.CompanionPolicyRate,
FullReplacementCost,
FullReplacementCostRate,
A.SeniorCitizen,
A.SeniorCitizenRate,
A.DryHydrant,
A.DryHydrantRate,
PropertyPermanentMarking,
PropertyPermanentMarkingRate,
A.StoveTopFireSupression,
A.StoveTopFireSupressionRate,
A.TotalPremium,
A.additionalInformation,
A.Endorsements,
AgentName = B.name,
AgentCode = B.code,
AgentAddress = REPLACE(B.MailingAddress,'##',' '),
AgentCity = B.mailingCity,
AgentState = B.mailingState,
AgentZipCode = B.mailingZipCode,
AgentPhone = B.daytimePhone,
E.TransactionEffDate,
E.TransactionName,
Z.HO101,
Z.HO101Premium,
Z.HO105,
Z.HO105Premium,
Z.HO110,
Z.HO110Premium,
Z.HO112,
Z.HO112Premium,
Z.HO113,
Z.HO113Premium,
Z.HO120,
Z.HO120Premium,
Z.HO126,
Z.HO126Premium,
Z.HO140,
Z.HO140Premium,
Z.HO145,
Z.HO145Premium,
Z.HO160,
Z.HO160Premium,
Z.HO170,
Z.HO170Premium,
Z.HO201,
Z.HO201Premium,
Z.HO215,
Z.HO215Premium,
Z.HO225,
Z.HO225Premium,
Z.HO301,
Z.HO301Premium,
Z.HO330,
Z.HO330Premium
-- ----------------------------------------------------------------------------
FROM
-- ----------------------------------------------------------------------------
UIHO A,
Agent B,
Event E,
ObjectManager O,
#Temp_Endorsements Z
-- ----------------------------------------------------------------------------
WHERE
-- ----------------------------------------------------------------------------
A.Id = @Policy AND
A.VersionNo = @PolicyVersion AND
A.Agent = B.Id AND
B.Id = O.Id AND
B.VersionNo = O.CurrentVersionNo AND
A.Id = E.Id AND
A.EventNo = E.No
-- ----------------------------------------------------------------------------
RETURN @@ROWCOUNT
-- ----------------------------------------------------------------------------
END

 

[MisterMo]

I don't see the link to the temp table #Temp_Endorsements in your WHERE clause

this might cause the SP to return resultset which could conflict with CR

Just a Guess

Mo

 

[MSGCR]

I'm just selecting everything from the #temp table. it will only result in one row.

 

[vidru]

I don't see anything wrong syntactically with the procedure. At first, I thought the RETURN @@ROWCOUNT statement might be choking it, but that tested out ok for me.

Is there anything in the Record Selection Formula for this report? If so, get rid of it. Also, you might want to see if you're up to date with service packs for CR 9, as I believe there were some db connectivity issues with it early on.

-dave

 

[MSGCR]

Have you done reporting off sp's that use results from embedded sp's? anyone know if database connectivity is enhanced in CR v.10 or v.11? This is very agitating.

 

[vidru]

Have you done reporting off sp's that use results from embedded sp's?

Yep, do it all the time with CR 8 - 10. I wonder if the issue might be with the procedure you're calling. Can you post that one as well?

-dave

 

[MSGCR]

-------------------------------------------------------------------------------
-- sp_UIHOEndorsements
-------------------------------------------------------------------------------
IF EXISTS (SELECT *
FROM sysobjects
WHERE id = object_id(N'[dbo].[sp_UIHOEndorsements]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP
PROCEDURE [dbo].[sp_UIHOEndorsements]
-------------------------------------------------------------------------------
GO
-------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
-------------------------------------------------------------------------------
GO
-------------------------------------------------------------------------------
CREATE PROCEDURE
-------------------------------------------------------------------------------
sp_UIHOEndorsements
-------------------------------------------------------------------------------
@TableName varchar(30),
@Endorsements varchar(600)
-------------------------------------------------------------------------------
AS
-------------------------------------------------------------------------------
BEGIN
-------------------------------------------------------------------------------
DECLARE @SW tinyint
DECLARE @Position integer
DECLARE @Length integer
DECLARE @Counter smallint
DECLARE @HasForm varchar(10)
DECLARE @FormNo varchar(10)
DECLARE @Premium decimal(11, 2)
-------------------------------------------------------------------------------
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-------------------------------------------------------------------------------
SET @Endorsements = RTRIM(LTRIM(@Endorsements))
-------------------------------------------------------------------------------
SET @SW = 1
SET @Counter = 0
-------------------------------------------------------------------------------
WHILE (@SW > 0)
------------------------------------------------------------------------------- +1
BEGIN
-------------------------------------------------------------------------------
SET @Counter = @Counter + 1
SET @Position = CHARINDEX('^', @Endorsements)
-------------------------------------------------------------------------------
IF @Position <= 0
------------------------------------------------------------------------------- +2
BEGIN
-------------------------------------------------------------------------------
SET @SW = 0
SET @Position = 1
SET @Length = LEN(@Endorsements) - 2
--------------------------------------------------------------------------------- 2
END
-------------------------------------------------------------------------------
ELSE
------------------------------------------------------------------------------- +2
BEGIN
-------------------------------------------------------------------------------
SET @Length = @Position - 3
SET @Position = 1
---------------------------------------------------------------------------------2
END
-------------------------------------------------------------------------------
IF SUBSTRING(@Endorsements, @Position, 1) = 'Y'
------------------------------------------------------------------------------- +2
BEGIN
-------------------------------------------------------------------------------
SET @FormNo = (
CASE
WHEN @Counter = 1 THEN 'HO101'
WHEN @Counter = 2 THEN 'HO102'
WHEN @Counter = 3 THEN 'HO105'
WHEN @Counter = 4 THEN 'HO110'
WHEN @Counter = 5 THEN 'HO111'
WHEN @Counter = 6 THEN 'HO112'
WHEN @Counter = 7 THEN 'HO113'
WHEN @Counter = 8 THEN 'HO120'
WHEN @Counter = 9 THEN 'HO121'
WHEN @Counter = 10 THEN 'HO122'
WHEN @Counter = 11 THEN 'HO125'
WHEN @Counter = 12 THEN 'HO126'
WHEN @Counter = 13 THEN 'HO130'
WHEN @Counter = 14 THEN 'HO135'
WHEN @Counter = 15 THEN 'HO14X'
WHEN @Counter = 16 THEN 'HO142'
WHEN @Counter = 17 THEN 'HO145'
WHEN @Counter = 18 THEN 'HO160'
WHEN @Counter = 19 THEN 'HO16X'
WHEN @Counter = 20 THEN 'HO170'
WHEN @Counter = 21 THEN 'HO180'
WHEN @Counter = 22 THEN 'HO190'
WHEN @Counter = 23 THEN 'HO201'
WHEN @Counter = 24 THEN 'HO205'
WHEN @Counter = 25 THEN 'HO210'
WHEN @Counter = 26 THEN 'HO215'
WHEN @Counter = 27 THEN 'HO220'
WHEN @Counter = 28 THEN 'HO225'
WHEN @Counter = 29 THEN 'HO230'
WHEN @Counter = 30 THEN 'HO301'
WHEN @Counter = 31 THEN 'HO305'
WHEN @Counter = 32 THEN 'HO310'
WHEN @Counter = 33 THEN 'HO315'
WHEN @Counter = 34 THEN 'HO320'
WHEN @Counter = 35 THEN 'HO330'
WHEN @Counter = 36 THEN 'HO380'
WHEN @Counter = 37 THEN 'HO381'
WHEN @Counter = 38 THEN 'HO382'
WHEN @Counter = 39 THEN 'HO700'
WHEN @Counter = 40 THEN 'HO702'
WHEN @Counter = 41 THEN 'HO703'
WHEN @Counter = 42 THEN 'HO708'
WHEN @Counter = 43 THEN 'HO709'
WHEN @Counter = 44 THEN 'HO710'
WHEN @Counter = 45 THEN 'HO711'
ELSE 'HOXXX'
END)
-------------------------------------------------------------------------------
SET @Premium = CONVERT(decimal(11, 2), SUBSTRING(@Endorsements, @Position + 2, @Length))
-- ----------------------------------------------------------------------------
EXECUTE
-- ----------------------------------------------------------------------------
(
'INSERT ' + @TableName + ' VALUES (' + '''' + @FormNo + '''' + ',' + @Premium + ')'
)
------------------------------------------------------------------------------- -2
END
-------------------------------------------------------------------------------
IF @SW > 0
------------------------------------------------------------------------------- +2
BEGIN
-------------------------------------------------------------------------------
SET @Position = CHARINDEX('^', @Endorsements)
SET @Endorsements = RIGHT(@Endorsements, LEN(@Endorsements) - @Position)
-------------------------------------------------------------------------------- -2
END
-------------------------------------------------------------------------------- -1
END
-------------------------------------------------------------------------------
END
-------------------------------------------------------------------------------

 

[MisterMo]

Hi

This could be a complete waste of time and seen the number of fields you are pulling out I'll understand if you won't want to do it.

try by creating a temp table and put your resultset onto it

and then run a select at the end of your stored proc.

for testing purposes you could use just a small number of fields.

I have learned that with crystal you have to be very precise with what you return from a SP

Mo