Report Running Continuously (forever)

Apr 9, 2007

I have a query that uses a lot of joins, subqueries and temp tables (I inherited it and am leary about rewriting it). I tried to rewrite it using table variables at one point, but the run time just got ridiculous for any query spanning more than a few months.



It currently runs as-is in about 3 minutes or less in Management Studio; however, when I try to run it via SRS or the Visual Studios 2005 Designer it runs indefinitely. It also runs indefinitely if I try to refresh the fileds or run it from the data tab (basically any time it has to call the stored procedure).



Does anyone have an idea of how I can get this report to run or know why it runs differently in SRS than in Management Studio?



Here is the very long procedure:



CREATE PROCEDURE [dbo].[ISP_RPT_RENEWAL_RETENTION_TEST]

(

@FROM DATETIME,

@TO DATETIME,

@ASOF DATETIME,

@REPORT VARCHAR(10) = 'ADHOC')

AS

SET NOCOUNT ON

--DECLARE @FROM DATETIME

--DECLARE @TO DATETIME

--DECLARE @ASOF DATETIME

--DECLARE @REPORT VARCHAR(10)

--

--SET @REPORT = 'YTD'

--SET @FROM = '1/1/2006'

--SET @TO = '10/31/2006'

--SET @ASOF = '10/31/2006'

IF (@REPORT = 'ADHOC' OR @REPORT IS NULL OR @REPORT = '')

BEGIN

--Check to see if user has input data

--First of the previous month

IF (@FROM IS NULL OR @FROM = '') SET @FROM = dbo.PreviousMonthEndDate(dbo.PreviousMonthEndDate(GetDate()))+1

IF (@TO IS NULL OR @TO = '') SET @TO = dbo.PreviousMonthEndDate(GetDate())

IF (@ASOF IS NULL OR @ASOF = '') SET @ASOF = dbo.PreviousMonthEndDate(GetDate())

END

ELSE IF @REPORT = 'MTD'

BEGIN

--Disregard user input and run for MTD

--First of the previous month

SET @FROM = dbo.PreviousMonthEndDate(dbo.PreviousMonthEndDate(GetDate()))+1

SET @TO = dbo.PreviousMonthEndDate(GetDate())

SET @ASOF = dbo.PreviousMonthEndDate(GetDate())

END

ELSE IF @REPORT = 'YTD'

BEGIN

--Disregard user input and run for YTD

SET @FROM = dbo.FirstOfTheYear(GetDate())

SET @TO = dbo.PreviousMonthEndDate(GetDate())

SET @ASOF = dbo.PreviousMonthEndDate(GetDate())

END

SET @FROM = dbo.MidnightDate(@FROM)

SET @TO = dbo.MidnightDate(@TO)

SET @ASOF = dbo.MidnightDate(@ASOF)

/*** if user runs report before month end is closed, then use data from premium table

if however user runs report after month end close, then pull from reinsurance table ***/

DECLARE @CHECKDATE DATETIME

SELECT @CHECKDATE = MAX(MONTHENDDATE) FROM MPATREINSURANCE

WHERE MONTHENDDATE = @TO

/*********************************************** IDENTIFY ALL RENEWALS ***************************************************/

SELECT DISTINCT

RC.POLICYNO POLICYNO , MCITCUST.CURRENTNAMENO,RC.POLICYID, RC.PMRSEQUENCE, RC.SUBCOMPANYID, RC.POLICYEFFDATE,

MCITCUST.FULLNAME ,MAX(ISNULL(RC1.POLICYNO,RC.PREVIOUSPOLICYNO)) PREVIOUSPOLICYNO ,RC.INSUREDNAMENO,RC.PROCESSINGSTATUS, RC.PMRSTATUS PMRSTATUS,V.COVERAGE

INTO #MPAV_RENEWALS

FROM MPATPMRC RC

INNER JOIN MCITCUST ON

RC.COMPANYID = MCITCUST.COMPANYID AND

RC.INSUREDNAMENO = MCITCUST.NAMENO

LEFT OUTER JOIN MPATCGLUWVERSIONS V ON

V.POLICYID = RC.POLICYID AND

V.PMRSEQUENCE = RC.PMRSEQUENCE AND

V.SUBCOMPANYID = RC.SUBCOMPANYID

INNER JOIN MCITCUST M1 ON

MCITCUST.COMPANYID =M1.COMPANYID AND

MCITCUST.CURRENTNAMENO = M1.CURRENTNAMENO

INNER JOIN MPATPMRC RC1 ON

RC1.COMPANYID = M1.COMPANYID AND

RC1.INSUREDNAMENO = M1.NAMENO

LEFT OUTER JOIN MPATCGLUWVERSIONS V1 ON

V1.POLICYID = RC1.POLICYID AND

V1.PMRSEQUENCE = RC1.PMRSEQUENCE AND

V1.SUBCOMPANYID = RC1.SUBCOMPANYID

WHERE

RC.POLICYTYPE ='CGL' AND

RC.POLICYEFFDATE >= '1/1/1998' AND

--FIND THE VERY FIRST VALID TRANSACTION BY LOOKING AT WHICH ONE OF RN/NB/RW IS THE LATEST

RC.TXNISSUED <= @ASOF AND

RC1.TXNISSUED <= @ASOF AND

RC.PMRSEQUENCE = (SELECT MAX(PMRSEQUENCE) FROM MPATPMRC RC2

WHERE RC2.POLICYID = RC.POLICYID AND

RC2.SUBCOMPANYID = RC.SUBCOMPANYID AND

RC2.POLICYYEAR = RC.POLICYYEAR AND

RC2.TXNISSUED <= @ASOF AND

RC2.TXNTYPE IN ('RN','NB','RW','RE') AND

( (RC2.POLICYNO = RC.POLICYNO AND RC.POLICYNO IS NOT NULL)

OR (RC.POLICYNO IS NULL)

) AND

RC2.PMRSTATUS IN ('HS', 'EX', 'IN','PC','PA')AND

RC2.PROCESSINGSTATUS IN ('*','P','A','I','Q')

)

AND ((RC1.TXNTYPE ='CN' AND RC1.CANCELMETHOD <> 'F') OR (RC1.CANCELMETHOD IS NULL OR RC1.TXNTYPE <> 'CN'))

AND RC1.PMRSEQUENCE = (

SELECT MAX(PMRSEQUENCE) FROM MPATPMRC

WHERE MPATPMRC.POLICYID = RC1.POLICYID AND

MPATPMRC.POLICYNO = RC1.POLICYNO AND

MPATPMRC.TXNISSUED <= @ASOF AND

MPATPMRC.PMRSTATUS IN ('HS', 'EX', 'IN','CN') AND

MPATPMRC.PROCESSINGSTATUS IN ('*','C')AND

MPATPMRC.TXNTYPE NOT IN ('EA','ER' )) AND

(

(RC1.TXNTYPE <>'CN' AND RC1.POLICYEXPDATE = RC.POLICYEFFDATE ) OR

(RC1.TXNTYPE ='CN' AND RC1.TXNEFFECTIVE = RC.POLICYEFFDATE )

)

AND (V.COVERAGE = 'B' AND V1.COVERAGE = 'P' OR V.COVERAGE = 'P' AND V1.COVERAGE = 'B' OR

V.COVERAGE = V1.COVERAGE)

GROUP BY RC.POLICYNO , MCITCUST.CURRENTNAMENO,RC.POLICYID, RC.PMRSEQUENCE, RC.SUBCOMPANYID, RC.POLICYEFFDATE,

MCITCUST.FULLNAME , RC.INSUREDNAMENO,RC.PROCESSINGSTATUS, RC.PMRSTATUS ,V.COVERAGE



/**************************************FIND LAST VERSION*********************************/

SELECT

RC.POLICYNO,

RC.POLICYYEAR,

RC.POLICYID,

RC.SUBCOMPANYID,

RC.PMRSEQUENCE,

RC.TXNTYPE,

POLICYEXPDATE = CASE WHEN RC.TXNTYPE = 'CN' THEN RC.TXNEFFECTIVE ELSE RC.POLICYEXPDATE END,

REPORTDATES =CASE

WHEN RC.TXNTYPE ='CN' AND RC.TXNEFFECTIVE <= RC.TXNISSUED THEN RC.TXNISSUED

WHEN RC.TXNTYPE ='CN' AND RC.TXNEFFECTIVE > RC.TXNISSUED THEN RC.TXNEFFECTIVE

ELSE RC.POLICYEXPDATE END,

CN_REASON = CASE WHEN RC.TXNTYPE = 'CN' THEN

(CASE WHEN RC.TXNSUBTYPE IN ('PF','NP') THEN 'Non-pay'

WHEN RC.TXNSUBTYPE IN ('IN') THEN 'Ins Request'

WHEN RC.TXNSUBTYPE IN ('UW') THEN 'UW Reasons' ELSE 'Other' END)

Else '' END,

CN_METHOD = CASE WHEN RC.TXNTYPE = 'CN' THEN CANCELMETHOD ELSE '' END,

INS.FULLNAME INSURED,

RC.INSUREDNAMENO,

INS.CURRENTNAMENO,

RC.STATE,

RC.ORIGINCEPTIONDATE

INTO #LAST_VERSION

FROM MPATPMRC RC

INNER JOIN --LOOKING UP THE LATEST VERSION OF POLICIES

(SELECT DISTINCT POLICYNO, POLICYID, SUBCOMPANYID, MAX( PMRSEQUENCE) LASTPMR

FROM MPATPMRC

WHERE

POLICYTYPE ='CGL' AND

PMRSTATUS IN ('HS', 'EX', 'IN','CN') AND

PROCESSINGSTATUS IN ('*','C') AND

TXNTYPE NOT IN ('EA','ER')

AND MPATPMRC.TXNISSUED <= @ASOF

GROUP BY POLICYNO, POLICYID, SUBCOMPANYID) LV ON

RC.POLICYNO = LV.POLICYNO AND

RC.SUBCOMPANYID = LV.SUBCOMPANYID AND

RC.POLICYID = LV.POLICYID AND

RC.PMRSEQUENCE = LV.LASTPMR

LEFT OUTER JOIN MCITCUST INS ON

RC.INSUREDNAMENO = INS.NAMENO

/*=============================================IDENTIFY RENEWALS FOR THE PERIOD==============================*/

SELECT R.POLICYNO,

R.CURRENTNAMENO,

R.POLICYID,

R.PMRSEQUENCE,

R.SUBCOMPANYID,

R.POLICYEFFDATE,

R.FULLNAME,

R.PREVIOUSPOLICYNO,

R.INSUREDNAMENO,

R.PROCESSINGSTATUS,

R.PMRSTATUS,

R.COVERAGE

INTO #RENEWALS

FROM #MPAV_RENEWALS R

LEFT OUTER JOIN #LAST_VERSION LV ON

R.POLICYNO = LV.POLICYNO

--take off pendings

WHERE LV.CN_METHOD <> 'F' AND

POLICYEFFDATE BETWEEN @FROM AND @TO

/* LETS GET RENEWAL BROKER */

SELECT

R.POLICYNO,

P.NAMENO,

C.FULLNAME,

A.CITY

INTO #RN_BRK

FROM #RENEWALS R

INNER JOIN MPATPRODUCERS P ON

R.POLICYID = P.POLICYID AND

R.PMRSEQUENCE = P.PMRSEQUENCE AND

R.SUBCOMPANYID = P.SUBCOMPANYID AND

PAYOR = 1

INNER JOIN MCITCUST C ON

P.NAMENO = C.NAMENO

INNER JOIN MCITADDR A ON

C.MAILINGADDRESS = A.ADDRESSID

/*==============================================GET RENEWING PREMIUMS=========================================*/

--this gives us written premium as of report date

SELECT DISTINCT

R.POLICYNO,

R.POLICYID,

MAX(R.SUBCOMPANYID) SUB,

MAX(MPATPMRC.POLICYEXPDATE) EXPDATE,

MAX(R.PREVIOUSPOLICYNO) EXPIRED_POLICY,

MAX(MPATPMRC.POLICYYEAR) POLICYYEAR,

MAX(MPATPMRC.UNDERWRITER)UW,

RN_DIRECT = SUM(WRITTENPREM1 + WRITTENPREM2 + WRITTENPREM3),

RN_CEDED_REIN = SUM(WRITTENPREM3),

CEDED_RETAINED = 0

INTO #RN_PREM

FROM #RENEWALS R

LEFT OUTER JOIN MPATPMRC MPATPMRC ON

MPATPMRC.POLICYNO = R.POLICYNO

LEFT OUTER JOIN MPATPREMIUMS MPATPREMIUMS ON

MPATPMRC.COMPANYID = MPATPREMIUMS.COMPANYID AND

MPATPMRC.PMRSEQUENCE = MPATPREMIUMS.PMRSEQUENCE AND

MPATPMRC.POLICYID = MPATPREMIUMS.POLICYID AND

MPATPMRC.SUBCOMPANYID = MPATPREMIUMS.SUBCOMPANYID

WHERE @CHECKDATE IS NULL AND

MPATPMRC.PMRSTATUS NOT LIKE '%P%' AND

((MPATPMRC.TXNISSUED >= MPATPMRC.TXNEFFECTIVE AND MPATPMRC.TXNISSUED BETWEEN @FROM AND @TO) OR

(MPATPMRC.TXNEFFECTIVE >= MPATPMRC.TXNISSUED AND MPATPMRC.TXNEFFECTIVE BETWEEN @FROM AND @TO))

GROUP BY R.POLICYNO , R .POLICYID

UNION ALL

--this gives us written premium as of report date

SELECT DISTINCT

R.POLICYNO,

R.POLICYID,

MAX(R.SUBCOMPANYID) SUB,

MAX(MPATPMRC.POLICYEXPDATE) EXPDATE,

MAX(R.PREVIOUSPOLICYNO) EXPIRED_POLICY,

MAX(MPATPMRC.POLICYYEAR) POLICYYEAR,

MAX(MPATPMRC.UNDERWRITER) UW,

RN_DIRECT = SUM(MPATREINSURANCE.WRITTENPREM1 + MPATREINSURANCE.WRITTENPREM2 + MPATREINSURANCE.WRITTENPREM3) ,

RN_CEDED_REIN = SUM(MPATREINSURANCE.CEDEDAMOUNT1 + MPATREINSURANCE.CEDEDAMOUNT2 + MPATREINSURANCE.CEDEDAMOUNT3) ,

CEDED_RETAINED= SUM(

(CASE WHEN CEDEDPERCENT1 <> 0 THEN

((1-CEDEDPERCENT1) * WRITTENPREM1) ELSE 0 END)

+

(CASE WHEN CEDEDPERCENT2 <> 0 THEN

((1-CEDEDPERCENT2) * WRITTENPREM2) ELSE 0 END)

+

(CASE WHEN CEDEDPERCENT3 <> 0 THEN

((1-CEDEDPERCENT3) * WRITTENPREM3) ELSE 0 END)

)

FROM #RENEWALS R

LEFT OUTER JOIN MPATPMRC MPATPMRC ON

MPATPMRC.POLICYNO = R.POLICYNO

LEFT OUTER JOIN MPATREINSURANCE MPATREINSURANCE ON

MPATPMRC.COMPANYID = MPATREINSURANCE.COMPANYID AND

MPATPMRC.PMRSEQUENCE = MPATREINSURANCE.PMRSEQUENCE AND

MPATPMRC.POLICYID = MPATREINSURANCE.POLICYID AND

MPATPMRC.SUBCOMPANYID = MPATREINSURANCE.SUBCOMPANYID AND

MPATPMRC.TREATYID = MPATREINSURANCE.TREATYID

WHERE @CHECKDATE IS NOT NULL AND

MPATPMRC.PMRSTATUS NOT LIKE '%P%' AND

((MPATREINSURANCE.TXNISSUED >= MPATREINSURANCE.TXNEFFECTIVE AND MPATREINSURANCE.TXNISSUED BETWEEN @FROM AND @TO) OR

(MPATREINSURANCE.TXNEFFECTIVE >= MPATREINSURANCE.TXNISSUED AND MPATREINSURANCE.TXNEFFECTIVE BETWEEN @FROM AND @TO))

GROUP BY R.POLICYNO , R .POLICYID



/*======================= looks for all policies EXPIRED during report time==========================*/

SELECT

LV.POLICYYEAR,

LV.POLICYNO,

LV.POLICYID,

LV.SUBCOMPANYID,

LV.PMRSEQUENCE,

LV.TXNTYPE,

LV.POLICYEXPDATE,

REASONDROPPED =

CASE WHEN LV.TXNTYPE = 'CN' AND CN_REASON = 'Non-pay' AND R.DESCRIPTION IS NULL

THEN 'Cancelled for non pay'

ELSE

ISNULL(ISNULL(R.DESCRIPTION, CLOSEREASONDESC),'No reason entered') END,

CN_METHOD,

V.COVERAGE,

CURRENTNAMENO,

INSUREDNAMENO,

INSURED,

LV.STATE,

ORIGINCEPTIONDATE,

CLOSECATEGORYDESC,

CLOSEREASONDESC,

COMPETITORDESC,

MKTFOLLOWUPDESC

INTO #EXPIRED

FROM #LAST_VERSION LV

LEFT OUTER JOIN MCITINSURED ON

LV.INSUREDNAMENO = MCITINSURED.NAMENO

LEFT OUTER JOIN MSOTDROPREAS R ON

MCITINSURED.REASONDROPPED = R.REASONID

LEFT OUTER JOIN MPATCGLUWVERSIONS V ON

V.POLICYID = LV.POLICYID AND

V.PMRSEQUENCE = LV.PMRSEQUENCE AND

V.SUBCOMPANYID = LV.SUBCOMPANYID

LEFT OUTER JOIN MSOTCLOSECATEGORIES CC ON

MCITINSURED.CLOSECATEGORYID = CC.CLOSECATEGORYID

LEFT OUTER JOIN MSOTCLOSEREASONS CR ON

MCITINSURED.CLOSEREASONID = CR.CLOSEREASONID

LEFT OUTER JOIN MSOTCOMPETITORS CO ON

MCITINSURED.COMPETITORID = CO.COMPETITORID

LEFT OUTER JOIN MSOTMKTFOLLOWUP MK ON

MCITINSURED.MKTFOLLOWUPID = MK.MKTFOLLOWUPID

WHERE REPORTDATES BETWEEN CAST(CAST(@FROM AS VARCHAR(11)) AS DATETIME) --DF CHANGE

AND CAST(CAST(@TO AS VARCHAR(11)) AS DATETIME)

/*==============================================GET EXPIRED POLICY INFO=========================================*/

--GET INFO FOR ALL TXNS SO U ALSO GET THE PREMIUMS

SELECT DISTINCT

MAX(MCITCUST.NAMENO) NAMENO,

R.RISKSEQUENCE,

MAX(MCITCUST.CURRENTNAMENO) CURRENTNAMENO,

MAX(MCITCUST.FULLNAME) FULLNAME,

RC.POLICYNO,

RC.POLICYID,

MAX(RC.SUBCOMPANYID) SUB,

MAX(RC.POLICYYEAR) POLICYYEAR,

EXPDATE = MAX(EX.POLICYEXPDATE),

SALES = SUM(CASE WHEN PR.COVERAGETYPE ='P' AND PR.PMRSEQUENCE = EX.PMRSEQUENCE THEN ISNULL(PR.SALES,0) ELSE 0 END),

MAX(RC.UNDERWRITER) UW,

P.HAZARDCLASS,

P.CLASSCODE PRODUCTCODE,

MAX(P.DESCRIPTION) [DESCRIPTION],

DISTRIBUTOR = (CASE WHEN P.DISTRIBUTOR = 1 THEN 'YES' ELSE 'NO' END),--R.PRODUCTCODE

MEMBER = MAX(CASE WHEN MEMBER = 1 THEN 'DEVICE' ELSE 'EXP' END),

SUM(WRITTENPREM1 + WRITTENPREM2) LAYER1_2,

EX_LIMIT = MAX(CASE WHEN V.COV_PROD_AGGREGATE = '1000000' OR V.COV_CGL_AGGREGATE = '1000000' THEN 1

WHEN V.COV_PROD_AGGREGATE = '2000000' OR V.COV_CGL_AGGREGATE = '2000000' THEN 2

WHEN V.COV_PROD_AGGREGATE = '3000000' OR V.COV_CGL_AGGREGATE = '3000000' THEN 3

WHEN V.COV_PROD_AGGREGATE = '4000000' OR V.COV_CGL_AGGREGATE = '4000000' THEN 4

WHEN V.COV_PROD_AGGREGATE = '5000000' OR V.COV_CGL_AGGREGATE = '5000000' THEN 5

WHEN V.COV_PROD_AGGREGATE = '6000000' OR V.COV_CGL_AGGREGATE = '6000000' THEN 6

WHEN V.COV_PROD_AGGREGATE = '7000000' OR V.COV_CGL_AGGREGATE = '7000000' THEN 7

WHEN V.COV_PROD_AGGREGATE = '8000000' OR V.COV_CGL_AGGREGATE = '8000000' THEN 8

WHEN V.COV_PROD_AGGREGATE = '9000000' OR V.COV_CGL_AGGREGATE = '9000000' THEN 9

WHEN V.COV_PROD_AGGREGATE = '10000000' OR V.COV_CGL_AGGREGATE = '10000000' THEN 10 END),

EX_BROKER_GROUP =

(SELECT [DESCRIPTION] FROM MSOTCOMMPLAN

WHERE

SUBCOMPANYID = MPATPRODUCERS.SUBCOMPANYID AND

COMMISSIONPLAN =

(SELECT TOP 1 COMMISSIONPLAN FROM MAGTCOMMPLAN

WHERE NAMENO = MPATPRODUCERS.NAMENO AND SUBCOMPANYID =MPATPRODUCERS.SUBCOMPANYID AND

COMMRATEEFFDATE <= EX.POLICYEXPDATE

ORDER BY COMMRATEEFFDATE DESC )),

MAX(BROKER.FULLNAME) BRK_NAME,

MAX(MPATPRODUCERS.COMMISSIONRATE * 100) RATE,

MAX(AA.CITY) BRK_CITY

INTO #EX

FROM MPATPMRC RC

INNER JOIN MPATPREMIUMS PR ON

RC.POLICYID = PR.POLICYID AND

RC.SUBCOMPANYID = PR.SUBCOMPANYID AND

RC.PMRSEQUENCE = PR.PMRSEQUENCE

INNER JOIN #EXPIRED EX ON

EX.POLICYNO = RC.POLICYNO

INNER JOIN MCITCUST ON

RC.COMPANYID = MCITCUST.COMPANYID AND

RC.INSUREDNAMENO = MCITCUST.NAMENO

LEFT OUTER JOIN MPATCGLUWRISKS R ON

PR.RISKSEQUENCE = R.RISKSEQUENCE AND

EX.POLICYID = R.POLICYID AND

EX.PMRSEQUENCE = R.PMRSEQUENCE AND

EX.SUBCOMPANYID = R.SUBCOMPANYID

LEFT OUTER JOIN MSOTPRODUCTS P ON

R.PRODUCTCODE = P.PRODUCTCODE

LEFT OUTER JOIN MPATCGLUWVERSIONS V ON

V .POLICYID = EX.POLICYID AND

V .PMRSEQUENCE =EX.PMRSEQUENCE AND

V .SUBCOMPANYID = EX.SUBCOMPANYID

INNER JOIN MPATPRODUCERS ON

EX.SUBCOMPANYID = MPATPRODUCERS.SUBCOMPANYID AND

EX.POLICYID = MPATPRODUCERS.POLICYID AND

EX.PMRSEQUENCE = MPATPRODUCERS.PMRSEQUENCE AND

MPATPRODUCERS.PAYOR = 1

INNER JOIN MCITCUST BROKER ON

MPATPRODUCERS.COMPANYID = BROKER.COMPANYID AND

MPATPRODUCERS.NAMENO = BROKER.NAMENO

LEFT OUTER JOIN MCITADDR AA ON

BROKER.MAILINGADDRESS = AA.ADDRESSID

WHERE RC.PMRSTATUS IN ('IN','EX','HS','CN') AND

RC.PROCESSINGSTATUS IN ('*','C')

GROUP BY R.RISKSEQUENCE ,RC.POLICYNO, RC.POLICYID,P.HAZARDCLASS, P.CLASSCODE,

MPATPRODUCERS.SUBCOMPANYID, MPATPRODUCERS.NAMENO, EX.POLICYEXPDATE,P.DISTRIBUTOR

ORDER BY RC.POLICYNO





/*****************************************************************************************************************/

SELECT

R.POLICYNO,

EX_DIRECT = SUM( WRITTENPREM1 + WRITTENPREM2 + WRITTENPREM3),

EX_CEDED_REIN = SUM(WRITTENPREM3),

EX_CEDED_RETAINED= 0

INTO #EX_PREM

FROM MPATPMRC R

INNER JOIN #EXPIRED E ON

R.POLICYNO = E.POLICYNO

LEFT OUTER JOIN MPATPREMIUMS ON

R.PMRSEQUENCE = MPATPREMIUMS.PMRSEQUENCE AND

R.POLICYID = MPATPREMIUMS.POLICYID AND

R.SUBCOMPANYID = MPATPREMIUMS.SUBCOMPANYID

WHERE @CHECKDATE IS NULL AND

((R.TXNISSUED >= R.TXNEFFECTIVE AND R.TXNISSUED <= @TO) OR

(R.TXNEFFECTIVE >= R.TXNISSUED AND R.TXNEFFECTIVE <= @TO))

GROUP BY R.POLICYNO

UNION ALL

SELECT

R.POLICYNO,

EX_DIRECT = SUM(MPATREINSURANCE.WRITTENPREM1 + MPATREINSURANCE.WRITTENPREM2 + MPATREINSURANCE.WRITTENPREM3) ,

EX_CEDED_REIN = SUM(MPATREINSURANCE.CEDEDAMOUNT1 + MPATREINSURANCE.CEDEDAMOUNT2 + MPATREINSURANCE.CEDEDAMOUNT3) ,

EX_CEDED_RETAINED=

SUM(

(CASE WHEN CEDEDPERCENT1 <> 0 THEN

((1-CEDEDPERCENT1) * WRITTENPREM1) ELSE 0 END)

+

(CASE WHEN CEDEDPERCENT2 <> 0 THEN

((1-CEDEDPERCENT2) * WRITTENPREM2) ELSE 0 END)

+

(CASE WHEN CEDEDPERCENT3 <> 0 THEN

((1-CEDEDPERCENT3) * WRITTENPREM3) ELSE 0 END)

)

FROM MPATPMRC R

INNER JOIN #EXPIRED E ON

R.POLICYNO = E.POLICYNO

LEFT OUTER JOIN MPATREINSURANCE ON

R.PMRSEQUENCE = MPATREINSURANCE.PMRSEQUENCE AND

R.POLICYID = MPATREINSURANCE.POLICYID AND

R.SUBCOMPANYID = MPATREINSURANCE.SUBCOMPANYID

WHERE @CHECKDATE IS NOT NULL AND

((MPATREINSURANCE.TXNISSUED >= MPATREINSURANCE.TXNEFFECTIVE AND MPATREINSURANCE.TXNISSUED <= @TO) OR

(MPATREINSURANCE.TXNEFFECTIVE >= MPATREINSURANCE.TXNISSUED AND MPATREINSURANCE.TXNEFFECTIVE <= @TO))

GROUP BY R.POLICYNO

/******************************************************************************************************************/

EXECUTE ISP_RPT_MKTSEGMENT

SET NOCOUNT OFF

SELECT

E.POLICYNO EXP_POLICY,

E.POLICYEXPDATE,

A.UW,

E.INSURED INS_NAME,

STATE INS_STATE,

BROKER,

BROKER_GROUP = CASE WHEN BROKER_GRP LIKE '%Others%' THEN 'Others'

WHEN BROKER_GRP LIKE '%Preferred%' THEN 'Preferred'

WHEN BROKER_GRP LIKE '%Marsh%' THEN 'Marsh'

WHEN BROKER_GRP LIKE '%Champion%' THEN 'Champion'

ELSE 'Others' END, COMM_RATE,

DOMINANT_PROD DOM_PROD,

DOMINANT_PROD_DESC DESCR,

MK.DISTRIBUTOR,

DOMINANT_HAZ DOM_HAZD,

EX_LIMIT,

MEMBER,

A.SALES,

MKT_SEGMENT,

ISNULL(EX_DIRECT,0) TOTAL_WRTTN,

(ISNULL(EX_CEDED_REIN,0) + ISNULL(EX_CEDED_RETAINED,0)) TOTAL_CEDED,

CEDED_REINSURE = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_CEDED_REIN,0) END ,

CEDED_RETAINED = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_CEDED_RETAINED,0) END ,

NWP_2M = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_DIRECT,0) - ((ISNULL(EX_CEDED_REIN,0) + ISNULL(EX_CEDED_RETAINED,0))) END,

XOL_NWP = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_DIRECT,0) - ISNULL(EX_CEDED_REIN,0) END,

REASON_LOST =

CASE WHEN R.POLICYNO IS NULL THEN

(SELECT 'LOST -'+REASONDROPPED FROM #EXPIRED WHERE POLICYNO = E.POLICYNO)

ELSE '' END,

ISNULL(R.POLICYNO, '') RN_POLICY,

ISNULL(RN_DIRECT,0) RN_TOTAL_WRTTN,

(ISNULL(RN_CEDED_REIN,0) + ISNULL(CEDED_RETAINED,0)) RN_TOTAL_CEDED,

RN_CEDED_REINSURE = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_CEDED_REIN,0) END ,

RN_CEDED_RETAINED = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(CEDED_RETAINED,0) END ,

RN_NWP_2M = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_DIRECT,0) - ((ISNULL(RN_CEDED_REIN,0) + ISNULL(CEDED_RETAINED,0))) END,

RN_XOL_NWP = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_DIRECT,0) - ISNULL(RN_CEDED_REIN,0) END,

[INSURED INFORCE TODAY?] = CASE WHEN (SELECT COUNT(*)

FROM MPATPMRC

WHERE PMRSTATUS ='IN' AND

INSUREDNAMENO IN

(SELECT NAMENO FROM

MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)

)>0 THEN 'YES' ELSE 'NO' END,

[FIRST_JOINED_DATE] =

ISNULL(

(SELECT MIN(DATEJOINED) FROM MCITINSURED

WHERE NAMENO IN

(SELECT DISTINCT NAMENO FROM

MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)), ORIGINCEPTIONDATE),

[# OF POLICY YEARS] = (

SELECT COUNT(DISTINCT POLICYYEAR)

FROM MPATPMRC

WHERE PMRSTATUS IN ('EX','HS','IN') AND TXNTYPE IN ('CV','NB','RN','RW') AND PROCESSINGSTATUS ='*'

AND POLICYNO IS NOT NULL AND

INSUREDNAMENO IN (SELECT DISTINCT NAMENO FROM

MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)),

MONTHS = (CASE WHEN MONTH(E.POLICYEXPDATE) = 1 THEN 'January'

WHEN MONTH(E.POLICYEXPDATE) = 2 THEN 'February'

WHEN MONTH(E.POLICYEXPDATE) = 3 THEN 'March'

WHEN MONTH(E.POLICYEXPDATE) = 4 THEN 'April'

WHEN MONTH(E.POLICYEXPDATE) = 5 THEN 'May'

WHEN MONTH(E.POLICYEXPDATE) = 6 THEN 'June'

WHEN MONTH(E.POLICYEXPDATE) = 7 THEN 'July'

WHEN MONTH(E.POLICYEXPDATE) = 8 THEN 'August'

WHEN MONTH(E.POLICYEXPDATE) = 9 THEN 'September'

WHEN MONTH(E.POLICYEXPDATE) = 10 THEN 'October'

WHEN MONTH(E.POLICYEXPDATE) = 11 THEN 'November'

WHEN MONTH(E.POLICYEXPDATE) = 12 THEN 'December' END),

BRK_CITY,

RB.FULLNAME RN_BROKER,

RB.CITY RN_BRK_CITY,

ORIG_EXP =

(SELECT MIN(POLICYEXPDATE) POLICYEXPDATE

FROM

MPATPMRC

WHERE POLICYNO = E.POLICYNO AND TXNTYPE IN ('NB','RN','RW')) ,

DROP_CATEGORY = CASE WHEN R.POLICYNO IS NULL THEN CLOSECATEGORYDESC ELSE '' END ,

DROP_REASON = CASE WHEN R.POLICYNO IS NULL THEN CLOSEREASONDESC ELSE '' END ,

COMPETITOR = CASE WHEN R.POLICYNO IS NULL THEN COMPETITORDESC ELSE '' END ,

MKTFOLLOWUP = CASE WHEN R.POLICYNO IS NULL THEN MKTFOLLOWUPDESC ELSE '' END,

MKT_SEGMENTSORT = MS.SORTORDER

FROM #EXPIRED E

LEFT OUTER JOIN #RENEWALS R ON

E.POLICYNO = R.PREVIOUSPOLICYNO

LEFT OUTER JOIN

(SELECT POLICYNO, SUM(SALES)SALES, MAX(UW) UW, SUM(LAYER1_2) PREM_2MIL, MAX(EX_LIMIT) EX_LIMIT,

MAX(BRK_NAME) BROKER, MAX(EX_BROKER_GROUP) BROKER_GRP, MAX(RATE) COMM_RATE, MAX(BRK_CITY) BRK_CITY

FROM #EX

GROUP BY POLICYNO ) A ON

E.POLICYNO = A.POLICYNO

LEFT OUTER JOIN #EX_PREM EP ON

E.POLICYNO = EP.POLICYNO

LEFT OUTER JOIN #RN_PREM RP ON

R.POLICYNO = RP.POLICYNO

LEFT OUTER JOIN MPAT_MKTSEGMENTS MK ON

A.POLICYNO = MK.POLICYNO

LEFT OUTER JOIN MSOTMARKETSEGMENT MS ON

MK.MKT_SEGMENT = MS.MARKETSEGMENT

LEFT OUTER JOIN #RN_BRK RB ON

R.POLICYNO = RB.POLICYNO

WHERE ISNULL(E.CN_METHOD,'X') <> 'F'

ORDER BY R.POLICYNO

View 7 Replies


ADVERTISEMENT

Continuously Running Package

Nov 8, 2005

I need to create a package that will monitor a table in a source system and when a flag is set, load data from other tables in this source system to my destination system.  Today this is accomplished with a SQL Agent job that executes every 15 minutes.  If there is no work to do the job simply exits.  I would like to create a SQLIS package that checks this control table every 30 seconds.  Can I create a package that runs continuously?

View 16 Replies View Related

SQL Server Job Running Forever

Dec 9, 2004

I have a job that is running for 2 days straight and the status reads: Performing Completion action.

I have tried to disable/Stop the job, but can't.

I have also tried to start the job which it won't because it is still running.

Is there a table that I can manual delete the schedule # or something along those lines to start all over again.

Thanks

Oh the normal time for the job to run is only 7 seconds.

Lystra

View 2 Replies View Related

Report Taking Forever To Come Up

Dec 14, 2007

I am using RS 2005 and SQL Server 2005. I am having a table with about 6 million rows. I am extracting about 2 milliion rows for a report. When i run the report as a single user the report comes up in 6-7 minutes but when i run the report with 2 users the report takes forever to come up.

The statistics are different each time sometimes 19 minutes sometimes 30 minutes. The report connects to the db with the same dbuser id for both the people running the report. The stored procedure being invoked uses temp tables and also indexes are created on the fly for these temp tables.

The moment 2 people are running the report and when i run an SP_WHO2 i see that one process id that is being started by reportserver blocks another process being run by reportserver.

Timeouts are not happening the report justs goes on forever to come up. Any help? Also if you need any more information please do let me know I will be glad to give them.

The report is a matrix report and there are 4 levels of grouping on the report.

Thanks in advance

View 1 Replies View Related

Report Taking Forever To Render

Jan 15, 2008



Hi All,

I have 2 reports that report on baiscally the same thing, just group differently.

Report 1 groups summary phone call stats by Department, Day, and Hour - which are all drop down options.
This means that the department summary stats are shown when the reports are rendered and can be expanded to see daily stats ... the daily stats can then be expanded to see the hourly stats.

Kinda Like:
-------------------------
-Department 1
-10/1/2007
12:00 AM
1:00 AM
+10/2/2007

+Department 2
-------------------------

Report 2 shows the same summary stats by department and extension - which is also a drop down option.
This means that the department summary stats are shown when the reports are rendered and can be expanded to see summary stats for each extension.

Kinda Like:
-------------------------
-Department 1
Ext 2005
Ext 2008

+Department 2
-------------------------


The queries for these reports run from the Management Studio in about 10 seconds each with the Report 1 query returning about 800 rows for the month of October 2007 and the Report 2 query returning about 30 rows for the same date range.

When the reports are rendered, Report 1 (with 800 rows) is rendered in about 20 seconds, while Report 2 (with only 30 rows) takes about 5 minutes to render.

The reports themselves are very similar, with the only difference being the grouping. It is weird that the report that returns the samller Dataset is actually taking longer to render.

One thing I did try was running the queries from the Data tab of the .rdl files (in visual studio) and the query for Report 2 took about 4 minutes to return data, while (as I mentioned above) it ran in about 10 seconds in Management Studio.

Has anyone else run into this?
Any suggestions?


Thanks in advance!
-Matt

View 1 Replies View Related

Sql Server 7.0 Crashes Continuously

Dec 15, 1999

I am using the DTS wizard to import tables from an AS/400. Some of the tables in the job are quite large and Sql Server either hangs up or completely crashes. My current configuration is WIN NT, SP4.0 SQL Server 7.0 no Service pack. Any suggestions?

View 1 Replies View Related

Recovery :: How To Get Continuously Backup

Aug 25, 2015

I have small db and load is very less. I am set full backup at  9:00 pm once a day,and set  transaction log backup to every 15 min.then i am taking the transaction log backup in 9:15,9:30,9:45....... My question is  I lost my data between 9:15 to 9:30 . in those time i will do some transactions.Then how to recover my data even with out lasing single  transactions.

View 4 Replies View Related

Continuously Updating A Website

Apr 15, 2007

Hi,



Bit of a newbee question:


I'm after hosting a website with basically a table in it that is linked to a MS SQL Server 2005 database, which I want to update on a pretty much continual basis from my own server PC which I'm running a data mining tool that updates the MS SQL Server 2005 database.

Any idea on how I would achieve this, or any pointers would be much appreciated.

Cheers,

Tom

View 1 Replies View Related

Reporting Services :: Subscription Of A Report RDL Still Running Even When Report Does Not Exist

Oct 1, 2015

I'm still receiving the emails of some subscriptions of reports that were already deleted, how these subscription could be deleted or turned off.

View 2 Replies View Related

The Report Server Windows Service 'ReportServer' Is Not Running. The Service Must Be Running To Use Report Server

May 16, 2007

Hello Reporting Services 2005 users or Reporting Services Team,
I have done everything to get pass this error but no luck.
My setup is :- WIndows 2003 Standared Edition SP1
Sql Server :-
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Reporting Services starting SKU: Standard.
From the Reporting Services Configuration Manager:
I can see all other things configured except Encryption Keys(blue sign)
Initialization(Grey sign) and Execution Acct(Yellow sign)
My windows Service Identity is using :
NT AuthorityNetworkService
Builtin Acct :NetworkService

Web Service Identity :-
ASP.NET Service Acct :- NT AuthorityNetworkService

DataBase Setup :- Credentials Type :- Service Credentials

I have also done everything from here thanks to Göran
http://stevenharman.net/blog/archive/2007/03/21/Avoiding-the-401-Unauthorized-Error-when-Using-the-ReportViewer-in.aspx

and

http://support.microsoft.com/default.aspx?scid=kb;en-us;896861
and

http://forum.k2workflow.com/viewtopic.php?t=619&


If you guys any solution for this please let me know.
I was wondering could this be a scale-out deployment issue ?

I also get the error when setting up the DataBase Setup :
Although saving the database connection info succeeded the The report server cannot access internal info about this deployment to determine whetherthe current con fig is valid for this editionThis could be a scale-out deployment and that the feature is not supported by this editionTo continue use a diff report server database or remove the encription keys



My Error log file is below:-
w3wp!webserver!5!16/05/2007-14:52:46:: i INFO: Reporting Web Server started
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing ConnectionType to '0' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing IsSchedulingService to 'True' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing IsNotificationService to 'True' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing IsEventService to 'True' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MemoryLimit to '60' percent as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MaximumMemoryLimit to '80' percent as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing IsWebServiceEnabled to 'True' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing ProcessRecycleOptions to '0' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing WatsonFlags to '1064' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing SecureConnectionLevel to '0' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file.
w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing WebServiceUseFileShareStorage to 'False' as specified in Configuration file.
w3wp!resourceutilities!5!16/05/2007-14:52:46:: i INFO: Reporting Services starting SKU: Standard
w3wp!resourceutilities!5!16/05/2007-14:52:46:: i INFO: Evaluation copy: 0 days left
w3wp!resourceutilities!5!16/05/2007-14:52:46:: i INFO: Running on 1 physical processors, 2 logical processors
w3wp!runningjobs!5!16/05/2007-14:52:46:: i INFO: Database Cleanup (Web Service) timer enabled: Next Event: 600 seconds. Cycle: 600 seconds
w3wp!runningjobs!5!16/05/2007-14:52:46:: i INFO: Running Requests Scavenger timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
w3wp!runningjobs!5!16/05/2007-14:52:46:: i INFO: Running Requests DB timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
w3wp!runningjobs!5!16/05/2007-14:52:46:: i INFO: Memory stats update timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
w3wp!library!5!05/16/2007-14:52:48:: i INFO: Call to GetPermissions:/
w3wp!library!5!05/16/2007-14:52:48:: i INFO: Catalog SQL Server Edition = Standard
w3wp!library!5!05/16/2007-14:52:48:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server.
w3wp!library!5!05/16/2007-14:52:48:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server.
w3wp!library!5!05/16/2007-14:52:49:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server.
w3wp!library!5!05/16/2007-14:52:49:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server.

View 8 Replies View Related

Running A SSRS Report In A Report Viewer Control In .net

Jun 29, 2007

Do I need the report server running in order view a SSRS report in a report viewer control? I've created my report in .net and it works great when I'm viewing it in visual studio, but can I run it my web app without the report server.

View 1 Replies View Related

Continuously Querying A Datastream Using SQL Server 2005

Nov 28, 2006

I was wondering if it is possible to continuously query a real-timedatastream using SQL Server. Does anyone have any experience of this? Ihave found LINUX based systems such as Borealis and STREAM. I wouldprefer to use a Windows based system as the program using the queryresults is Windows based.

View 4 Replies View Related

Replication :: Blocking And Deadlocks Are Occurring Continuously

Jul 2, 2015

We have transactional replication. After creating replication, we found that many blocking and deadlocks are occurring continuously. Will it cause any blocking on publisher db.

View 4 Replies View Related

Report Builder Error When Running Report

Feb 13, 2006

Hello everyone,

I've come across an unusual error on some of our machines. I'm trying to track down why only certain ones will give an error. I thought it might have something to do with .NET 2.0 not being installed, but I assume that they wouldn't even be able to launch Report Builder if this is the case. I'm at the point now where I've been told these machines have .NET 2.0. I plan on trying to verify soon, but I thought I'd get everyone's opinion on what it could be.

With even the simplist report (just 1 field dragged onto the report) the following error appears: "Report execution error The report might not be valid or the server could not process the data." When I clicked on the details, the following appears: "The numeric portion of 0pt cannot be parsed as a unit of type Point. ----- The type initializer for 'Microsoft.ReportingServices.Design.Constants' threw an exception."

This model was created with Business Intelligence Development Studio and we're using SQL 2005 Enterprise.

Anyone have any thoughts on what this could be or how I can better troubleshoot it?


Thanks,

Dan

View 5 Replies View Related

Transactional Replication - Pull Subscription Snapshot Being Applied Continuously

Jan 25, 2007

Hi

We have setup transactional replication between 2 databases on SQL Server 2000 SP3a  (~70GB), using a concurrent snapshot (to prevent locking out of the live database) to initilaise the data and a pull subscription from the second database.

From analysing the msdistribution_history table in the distribution database on the subscriber it appears that the snapshot is being applied in a continuous loop to the subscriber database. Viewing the comments column in the msdistribution_history table we can see the following sequence of events occuring

Initialising
Applied script 'snapshot.pre'
Then it applies all the schema files .sch
Then it applies all the index files .idx
The it bulk copies the data in (bcp)
Then it creates the Primary Keys
Then it applies all the trigger files .trg
Then it applies all the referential integrity files .dri

These all complete successfully but then the process kicks off again immediately after reapplying the snapshot.  We are unaware of any settings that may be causing this.

Any help on what maybe causing this would be much appreciated.

 

 

View 5 Replies View Related

Same Problem As The Report Server Is Not Responding. Verify That The Report Server Is Running And Can Be Accessed From This Comp

Dec 12, 2007

do you mean first install SDK, then install IIS, then install report sever?
I have same problem.
any can help?

View 1 Replies View Related

The Report Server Is Not Responding. Verify That The Report Server Is Running And Can Be Accessed From This Computer.

Oct 9, 2007



When I trying to configure Reposrting services in IIS & XP. It hsows me error
"The report server is not responding. Verify that the report server is running and can be accessed from this computer."


I also configure with reposrting configuration tools which show all confuguration is perfect.

but when I try to open through IE http://localhost/Reports/Pages/Folder.aspx

or http://localhost/Reportserver

It shows error. I try all ways still not works ..Help me out

Thanks.



View 9 Replies View Related

How To Know Which Report Is Running

Oct 9, 2006

Hi All,

Is there any way or tool to monitor Report Server so we could see which report is currently running?

And also can we alert/warn user about the processing time of the report?

Thanks and Regards,

Uzzi

View 11 Replies View Related

Running Report For Certain Days

May 28, 2008

I need to run a report where if it's Thursday then it will look for appointment dates for the following Monday, Friday will look for Tuesday, and then Monday will look for Wednesday. I found the Datepart syntax, however, I'm not sure if it should go in the Select statement area or where. The below query works but it literally looks for appointments 2 days out whether it be a weekday or weekend.

Select distinct c.ConfigECode as OfficeName,
p.PatsFirstName,
p.PatsLastName,
CONVERT(varchar(10),p.PatsBirthDate,101) as PatsBirthDate,
ins.CarrierName,
ins.CarrierPhone,
pol.PolEmployeeFirstName as EmpFirstName,
pol.PolEmployeeLastName as EmpLastName,
pol.PolEmployeeSSN,
CONVERT(varchar(10),pol.PolEmployeeBirthDate,101) as EmployeeBirthDate,
cov.GroupName,
appt.dt_appt as FutureAppt
From tPats p
Inner Join tConfig c
on c.ConfigOfficeNumber = p.OfficeNumber
Inner Join tInsurancePol pol
on pol.PolPatientID = p.PatsNumber
and pol.OfficeNumber = p.OfficeNumber
Inner Join tInsuranceCarrier ins
on ins.CarrierID = pol.PolCarrierID
Inner Join tInsCoverage cov
on cov.CoverageID = pol.PolCoverageID
Inner Join tbl_appt appt
on appt.PatsNumber = p.PatsNumber
and appt.OfficeNumber = p.OfficeNumber
Where c.ConfigOfficeNumber in ('241150','240728','240808')
and pol.PolStatus in (0,3)
and c.ConfigCenterType = 2
and CONVERT(varchar(10),appt.dt_appt,101) =
DATEADD(dd,+2,CONVERT(varchar(10),GETDATE(),101))
and appt.stat_cd = 0
Order by c.ConfigECode, p.PatsLastName, p.PatsFirstName

View 2 Replies View Related

Long-running Report

Jul 6, 2007

Hi. I'm fairly new to SSRS, and very new to this forum. I have a report based on a stored procedure. I've optimized the procedure so that it runs from 2-4 minutes (previously over half an hour). However, when I run the report that calls the sp, it runs forever (well over 45 minutes in some cases), and the users basically give up on it. Any ideas of why this happens and what steps I can take to improve performance?

Thanks,

Marianne

View 4 Replies View Related

Re-running Subscriptions For Any Report

Mar 28, 2008



Hi,

I have created a subscription for a report and this subscription is scheduled to run at specific time everyday. My subscription failed because of some stored proc issue. Is there a way to re-run the subscription. I did not know how to do this so I created a temporary subscription just to run it again. Please help!

View 7 Replies View Related

How To Find Out The Job/report That Is Currently Running?

Jun 7, 2007

How do I find out the report that is currently being run? I have a scenario where some users run a report without knowing the amount of data that will be pulled by it... In such a case, it bogs down the server and doesn't let me access the Report Manager application.

How can I find out what is the report that is currently being run? I know I can get that information from the Show Jobs link fromm Site Settings in Report Manager. But in this case, I am not even able to login to the Report Manager. The memory (8 gigs) in the app server is fully maxed out and it doesn't log me in to the Report Manger.

I ran a query against the ExecutionLog table in the ReportServer database that logs all the executions. But it looks like the data to this table gets logged only after the report execution is completed. There is no way to tell what is being executed right now. I also checked the table RunningJobs in the ReportServer database, but there are no records in that table.

Can someone help?

Thanks.

View 4 Replies View Related

Report Running Really Really Slowly!!

Jun 11, 2007

Hi, I have a report with 18 cascading report parameters. Each report parameter has a unique dataset which passes the value of the previous parameter into the sql string. As I am selecting the report parameters it is taking longer to query the further down I go. I think this is because of the number of where conditions that are being passed through the sql query. The last report parameter is passing 17 where conditions.

In access when I have done this the parameters near the bottom were being refreshed quicker than the top ones - why is it the opposite way round in reporting services??? Any ideas of how to speed this process up?

View 4 Replies View Related

Getting RsOperationNotSupported When Running Report

Aug 10, 2007

I had started a project with SQL Server 2005 Express and quickly realized that it would not let me access DB's on other servers so I removed Express and Installed Workgroup Edition. When I try to run reports I still get "rsOperationNotSupported" when connecting to the remote database.

What gives? Do I need to move up to Std Edition for this?

View 5 Replies View Related

Specify Database When Running A Report

Mar 4, 2008

Hi,

Currently I have a Report Model that uses a Data Source where the "Credentials are supplied by the user running the report". This works quite well.
The only further requirement is that I would also like to be able to specify which database to use (at runtime), would this be possible?



Kind Regards,
Robert

View 3 Replies View Related

Jump To Report Without Running It Automatically

May 18, 2007

Hi



I need to be able to click a hyperlink in one report to open a linked report, but without automatically running the linked report.



Scenario.

I have a report showing customer details and on this page there is a link to a price enquiry report. This price enquiry report has two parameters: customer code and product code.



When I click the price enquiry hyperlink in the customer details report I can only attach a value to the customer code parameter. The product code parameter is not known at this stage and would have to be provided by the user after the price enquiry report is opened (but not run). The customer code parameter should already be filled (carried in the link).



When I click the link at the moment I get the error "The 'product' parameter is missing a value".



Any advice much appreciated.



Thanks,

SQLServant



View 1 Replies View Related

Prevent The Report From Running Automatically.

Feb 4, 2008


I have a report that gets two parameters of type date.
These parameters have default values.
When I press preview the report runs automatically.
I want to prevent the report from running automatically, and let it run only when I press the button "view report".

How should I do it?
Thanks in advance!

View 7 Replies View Related

Report Subscription Not Running Reports

Mar 1, 2008

I have a new setup of report server. I have deployed three reports that I have also scheduled to run and save as an Excel spreadsheet to a network locations. The reports do not run and I get the error message below. Does anyone have any ideas? Anyone run into this issue before




ReportingServicesService!dbpolling!10!02/29/2008-17:29:07:: i INFO: NotificationPolling finished processing item 33d62282-2a03-483c-bd47-f41ec78c01d5
ReportingServicesService!library!4!02/29/2008-17:29:07:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., AuthzInitializeContextFromSid: Win32 error: 110;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information.
ReportingServicesService!library!4!02/29/2008-17:29:07:: i INFO: Initializing EnableExecutionLogging to 'True' as specified in Server system properties.
ReportingServicesService!subscription!4!02/29/2008-17:29:07:: i INFO: Microsoft.ReportingServices.Diagnostics.Utilities.RSException: The report server has encountered a configuration error. See the report server log files for more information. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information.
at Microsoft.ReportingServices.Authorization.Native.GetAuthzContextForUser(IntPtr userSid)
at Microsoft.ReportingServices.Authorization.Native.IsAdmin(String userName)
at Microsoft.ReportingServices.Authorization.WindowsAuthorization.IsAdmin(String userName, IntPtr userToken)
at Microsoft.ReportingServices.Authorization.WindowsAuthorization.CheckAccess(String userName, IntPtr userToken, Byte[] secDesc, ReportOperation requiredOperation)
at Microsoft.ReportingServices.Library.Security.CheckAccess(ItemType catItemType, Byte[] secDesc, ReportOperation rptOper, String reportPath)
at Microsoft.ReportingServices.Library.RSService._GetReportParameterDefinitionFromCatalog(CatalogItemContext reportContext, String historyID, Boolean forRendering, Guid& reportID, Int32& executionOption, String& savedParametersXml, ReportSnapshot& compiledDefinition, ReportSnapshot& snapshotData, Guid& linkID, DateTime& historyOrSnapshotDate, Byte[]& secDesc)
at Microsoft.ReportingServices.Library.RSService._GetReportParameters(ClientRequest session, String report, String historyID, Boolean forRendering, NameValueCollection values, DatasourceCredentialsCollection credentials)
at Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters)
at Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
--- End of inner exception stack trace ---

View 1 Replies View Related

Issue Running MDX In Report Designer

May 1, 2008



Below I have query I run in Management Studio. The sample results are below. When I put the same query in SSRS Report Designer it strips the "All" result out and replaces it with a Null. Is there a reason for this? Is there a way to force a string result instead of a Null.

Any assistance would be appreciated.


SELECT NON EMPTY { [Measures].[FACT NETWORK TT 2 Count] } ON COLUMNS, NON EMPTY

([FACT NETWORK TT 2].[Year].[Year].ALLMEMBERS

* [FACT NETWORK TT 2].[Quarter].[Quarter].ALLMEMBERS

* [FACT NETWORK TT 2].[Month].[Month].ALLMEMBERS * [DIM NETWORK TT 2].[EVENT DETECTOR].Allmembers)

ON ROWS FROM [Network TT2]


Sample results:
2006 2006-Q1 2006-02 All 1
2006 2006-Q1 2006-02 SMARTSNET 1
2006 2006-Q1 2006-03 All 613
2006 2006-Q1 2006-03 217
2006 2006-Q1 2006-03 Netview 25
2006 2006-Q1 2006-03 SMARTSNET 371
2006 2006-Q2 2006-04 All 62
2006 2006-Q2 2006-04 17
2006 2006-Q2 2006-04 SMARTSNET 45
2006 2006-Q2 2006-05 All 2
2006 2006-Q2 2006-05 SMARTSNET 2


View 3 Replies View Related

Running A SSRS Report In SSIS?

Mar 11, 2008

Has anyone figured out a way to create an ssis package that runs a report in ssrs 2005?

View 4 Replies View Related

Display In Report Which Database You Are Running From

Apr 11, 2008

Hi,

I searched in the previous posting but can't find any or miss them. We have a few databases that users can run reports from, i.e. production, training & development. Is there any way, say put it in either in the header or footer, to display from which database that the report are running from? Sometimes they got confused whether the data is current or like few days old.

Any input is very much appreciated. Thanks in advance ..

View 5 Replies View Related

Running SSRS Report From SSIS Package

Feb 27, 2009

I have an SSIS package. It updates a table in SQL Server 2005 database. There is another SSRS report that presents my final table and I run it and export it into Excel.Is it any way after SQL Query task and Store procedures are being finished in my SSIS package, I add another object to run my SSRS report and export it to Excel format?

View 4 Replies View Related

Problem With Writting A Report (SQL Slow Running)

Jul 20, 2005

HiI am trying to write a report that calculates the average number of salesover 7, 14, 31 and 365 days for each hourly period of the day. the problemis it takes over 4 minutes to run.e.g.Average Xactions per Hour7 Days 14 Days 31 Days 365 Days00:00 - 01:00 1,141.6 579.2 261.6 28.801:00 - 02:00 1,298.0 649.6 293.4 30.0The report was use to be purely ASP running SQL Statements.I then changed it to ASP Running a SP 24 times - this reduced running timeby about 1 minute.I then changed it so that the stored proc looped internally 24 times andreturns the data.I have ran the Index Tuning Wizard on the SQL and Implemented the indexessuggested - this actually increase execution time by 20 seconds.Below is the stored procedure I am currently using that loops internally 24times and returns the data.Can anyone suggest a better way / any improvements I could make ?Many ThanksSteve-----------------------------------------------------------------------------------------------------CREATE procedure ams_RPT_Gen_Stats@strResult varchar(8) = 'Failure' output,@strErrorDesc varchar(512) = 'SP Not Executed' output,@strTest varchar(1),@strCurrency varchar(3),@strVFEID varchar(16)asdeclare @strStep varchar(32)set @strStep = 'Start of Stored Proc'/* start insert sp code here */create table ##Averages (TheHour varchar(2),Day7Avge float ,Day14Avge float ,Day31Avge float ,Day365Avge float)declare @numHour varchar(2)declare @strSQL varchar(2000)declare @Wholesalers varchar(64)declare MyHours cursor FORWARD_ONLY READ_ONLY forselect convert(char(2), timestamp,14) as TheHourfrom xactionsgroup by convert(char(2), timestamp,14)order by convert(char(2), timestamp,14)if @strTest = 'Y'select @Wholesalers = VALUE FROM BUSINESSRULES WHERE NAME ='TEST_Wholesalers'open MyHoursfetch next from MyHours into @numHourwhile @@fetch_status = 0beginset @strSQL = 'insert into ##Averages (TheHour, Day7Avge) ( select ''' +@numHour + ''', ' +'count(*) / 7.00 ' +'FROM ' +'XACTIONS INNER JOIN ' +'RETAILER ON XACTIONS.RETAILERID = RETAILER.RETAILERID ' +'WHERE ' +'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 8) and ' +'xactions.xactiontotal <> 0 and ' +' convert(char(2) , timestamp, 14) = ''' + @numHour + ''' 'if @strTest = 'Y'set @strSQL = @strSQL + ' and retailer.BillOrgID not in (' +@Wholesalers + ') 'if @strCurrency <> '*'set @strSQL = @strSQL + ' and xactions.XACTIONCURRENCY = ''' +@strCurrency + ''' 'if @strVFEID <> '*'set @strSQL = @strSQL + ' and xactions.VFEID = ''' + @strVFEID + ''''set @strSQL = @strSQL + ')'exec ( @strSQL )set @strSQL = 'update ##Averages set Day14Avge = ( select ' +'count(*) / 14.00 ' +'FROM ' +'XACTIONS INNER JOIN ' +'RETAILER ON XACTIONS.RETAILERID = RETAILER.RETAILERID ' +'WHERE ' +'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 15) and ' +'xactions.xactiontotal <> 0 and ' +' convert(char(2) , timestamp, 14) = ''' + @numHour + ''' 'if @strTest = 'Y'set @strSQL = @strSQL + ' and retailer.BillOrgID not in (' +@Wholesalers + ') 'if @strCurrency <> '*'set @strSQL = @strSQL + ' and xactions.XACTIONCURRENCY = ''' +@strCurrency + ''' 'if @strVFEID <> '*'set @strSQL = @strSQL + ' and xactions.VFEID = ''' + @strVFEID + ''' 'set @strSQL = @strSQL + ') where TheHour = ''' + @numHour + ''' 'exec ( @strSQL )set @strSQL = 'update ##Averages set Day31Avge = ( select ' +'count(*) / 31.00 ' +'FROM ' +'XACTIONS INNER JOIN ' +'RETAILER ON XACTIONS.RETAILERID = RETAILER.RETAILERID ' +'WHERE ' +'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 32) and ' +'xactions.xactiontotal <> 0 and ' +' convert(char(2) , timestamp, 14) = ''' + @numHour + ''' 'if @strTest = 'Y'set @strSQL = @strSQL + ' and retailer.BillOrgID not in (' +@Wholesalers + ') 'if @strCurrency <> '*'set @strSQL = @strSQL + ' and xactions.XACTIONCURRENCY = ''' +@strCurrency + ''' 'if @strVFEID <> '*'set @strSQL = @strSQL + ' and xactions.VFEID = ''' + @strVFEID + ''' 'set @strSQL = @strSQL + ' ) where TheHour = ''' + @numHour + ''' 'exec ( @strSQL )set @strSQL = 'update ##Averages set Day365Avge = ( select ' +'count(*) / 365.00 ' +'FROM ' +'XACTIONS INNER JOIN ' +'RETAILER ON XACTIONS.RETAILERID = RETAILER.RETAILERID ' +'WHERE ' +'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 366) and ' +'xactions.xactiontotal <> 0 and ' +' convert(char(2) , timestamp, 14) = ''' + @numHour + ''' 'if @strTest = 'Y'set @strSQL = @strSQL + ' and retailer.BillOrgID not in (' +@Wholesalers + ') 'if @strCurrency <> '*'set @strSQL = @strSQL + ' and xactions.XACTIONCURRENCY = ''' +@strCurrency + ''' 'if @strVFEID <> '*'set @strSQL = @strSQL + ' and xactions.VFEID = ''' + @strVFEID + ''' 'set @strSQL = @strSQL + ' ) where TheHour = ''' + @numHour + ''' 'exec ( @strSQL )fetch next from MyHours into @numHourend -- while fetchclose MyHoursdeallocate MyHoursselect * from ##Averages order by TheHourdrop table ##Averages/* end insert sp code here */if (@@error <> 0)beginset @strResult = 'Failure'set @strErrorDesc = 'Fail @ Step :' + @strStep + ' Error : ' +CONVERT(VARCHAR,@@Error)return -1969endelsebeginset @strResult = 'Success'set @strErrorDesc = ''endreturn 0GO

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved