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


ADVERTISEMENT

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 View Related

SQL Server Loads Over Network Take Forever

Feb 16, 1999

When I attempt to load a database from dump format across a network (100mb Ethernet) It takes forever. (15 hours for 16GB!) can anyone help me find a starting point to troubleshoot this?

Thanks!

-Chris

P.S. File Copies of the same size move at a rapid fashion, and I cannot find any bottlenecks in the network.

View 2 Replies View Related

Newbie Q: Contacting Server To Create Web Project Runs Forever

Aug 25, 2005

hi, I'm using vstudio 2002, trying to create an C# asp web project, when I select these for a new project it says contacting server to create web project and never quits even after 1.5 hours. I saw in a similar post elsewhere they said the solution is to disable the firewalls - and I did that - and I have norton only, so that is not the issue. IIS is installed as well as SQL server, I can see it running. I have tested successfully a sql connection not long ago using access. Any thoughts? Thanks.

View 3 Replies View Related

Remote Update Having A Linked Server Takes Forever To Execute

Oct 17, 2006

UPDATE CD SET col1=SR.col1,col2=SR.col2,col3=SR.col3,col4=SR.col4,col5=SR.col5,col6=SR.col6,col7=SR.col7,

col8=SR.col8,col9=SR.col9,col10=SR.col10

FROM LNKSQL1.db1.DBO.Table1 CD

join Table2 USRI on USRI.col00 = CD.col00

join table3 SR on USRI.col00 = SR.col00

Here, I'm trying to tun this from an instance and do a remote update. col00 is a primary key and there is a clustered index that exists on this column. When I run this query, it does a 'select * from tabl1' on the remote server and that table has about 60 million rows. I don't understand why it would do a select *... Also, we migrated to SQL 2005 a week or so back but before that everything was running smooth. I dont have the execution plan from before but this statement was fast. Right now, I can't run this statement at all. It takes about 37 secs to do one update. But if I did the update on a local server doing remote joins here, it would work fine. When I tried to show the execution plan, it took about 10 mins to show up an estimated plan and 99% of the time was spent on Remote scan. Please let me know what I can do to improve my situation. Thank you

View 4 Replies View Related

Can Profiler Run Forever?

Jan 9, 2008

I created a profiler to run on a remote server in local. Then I logout. After two hours, I login again. The profiler was closed. I don't know when and why. Did someone have same problem? Is this normal?

Thanks

ZYT

View 8 Replies View Related

SQL Query Taking Forever

Mar 1, 2006

I have the below query which returns thousands of records. can I optimize the returned result set faster without changing the structure of the database?
SELECT     dbo.tblComponent.ComponentID, dbo.tblComponent.ComponentName, dbo.tblErrorLog.ShortErrorMessage, dbo.tblErrorLog.LongErrorMessage,                       dbo.tblErrorLog.LogDate, dbo.tblErrorLevel.Description,dbo.tblErrorLog.ErrorLogIDFROM         dbo.tblErrorLevel INNER JOIN                      dbo.tblErrorLog ON dbo.tblErrorLevel.ErrorLevelID = dbo.tblErrorLog.ErrorLevelID INNER JOIN                      dbo.tblComponent ON dbo.tblErrorLog.ComponentID = dbo.tblComponent.ComponentID
Thanks.

View 2 Replies View Related

Sysprocesses Says: Wait Forever

Sep 30, 2004

I'm doing an update on a table with about 113m rows, the update-statement is fairly simple: update tab set col = null where col is not null.
The col column is mostly null.

Sysprocesses shows three rows for this statement: 1 CXPACKET (its a dual processor, 2000 box with sp3 installed), 2 PAGEIOLATCH_SH (waitresource is filled). My guess would be that the where-clause is executed in a seperate process blocking the update.

I changed the statement into update [...] set col = null; sysprocesses shows one row with PAGEIOLATCH_SH. Executing forever.

I checked other processes including those outside sqlserver but none are using the db, let alone accessing the table involved. Even restarted sqlserver to be sure there's no dead process blocking the update. Didn't help.

So I added a search condition to the where-clause, involving a clustered index in order to reduce the rowcount. The execution plan shows a 97% hit on the clustered index, but sysprocesses shows the three rows again...

So far the profiler didn't help me out either: there's a SP: CacheInsert on the update-statement... then nothing.

What should I do?

View 6 Replies View Related

Shrinkdatabase Taking Forever...

Mar 17, 2008

Hi all,

2 weeks ago I deleted about 200GB of data from a 300GB+ database. It's a custom DB we want to use to test few things. We wanted a smaller size DB for our testing and since we didn't have any we grabbed a production backup, removed sensitive data and ran a large archiving script on it... Anyway so far so good but our data file was still the same size as before.

So we started a shrinkdatabase... it has been running for 2 weeks now! After about 1 week I interrupted the shrinkdatabase process and ran a
dbcc shrinkdatabase('DB', truncateonly)
just to see if the data file will get reduced a bit or not. It did get reduced by about 20GB. I assume that
dbcc shrinkdatabase('DB', 0)
has free up enough pages at the end of the data file so a truncateonly was able to free up some space... Anyway after this we started the
dbcc shrinkdatabase('DB', truncateonly)
again... still running...

The database was never shrank before and every index is highly fragmented... Is that why it's taking so long? Am I actually going to have to wait for another few weeks before that thing finishes??

Anyone has experience running shrink on large DBs?


thanks!

View 14 Replies View Related

Cte Query Taking Forever

May 14, 2008

I have following common table expression query which is taking like 15 hours to run. would someone suggest what can I do to speed this thing up..

; with
a as (select proj_id, proj_start_dt,proj_end_dt, case when charindex('.', Proj_ID) > 0 then left(Proj_ID, len(Proj_ID) - charindex('.', reverse(Proj_ID))) end as Parent_Proj_ID from ods32.dbo.Proj a), --add Parent_Proj_ID column
b as (select proj_id, proj_start_dt,proj_end_dt,Parent_Proj_ID from a where PROJ_START_DT is not null and PROJ_END_DT is not null --get all valid rows
union all
select a.Proj_Id, b.PROJ_START_DT, b.PROJ_END_DT, a.Parent_Proj_ID from b inner join a on b.Proj_Id = a.Parent_Proj_ID where a.PROJ_START_DT is null or a.PROJ_END_DT is null) --get all invalid children of valid rows and give them the dates of their parents
update a set PROJ_START_DT = b.PROJ_START_DT, PROJ_END_DT = b.PROJ_END_DT
from WPData a left outer join b on a.Proj_ID = b.Proj_ID -- join up and update



thanks

View 8 Replies View Related

Help A Newbie And Live Forever... Maybe

Feb 22, 2006

Hi, sorry if this has been asked before but I'm pretty strapped for time...

I have two tables: [photos] and [photoFolders]

[photoFolders] contains information about photo albums on the site im creating. The information in [photos] lists information about all photos along with which [photoFolder] they belong. When the user logs in, I want to present a list of all 'folders' in their name along with the TOP image with the corresponding folderId...

[photos]
photoId
photoName
folderId
photoDescription
cUserId

[photoFolders]
folderId
folderName
eventDate
cUserId

Any help would be GREATLY appreciated

We're all going to hell... I guess I'll see you there!

View 15 Replies View Related

All Queries Are Taking Forever

Aug 23, 2007

what can I do?

all queries that used to work are taking forever now???

what can I do?

is there a max size for the db that I may have reached

please advise asap

View 20 Replies View Related

Why Would Adding Dbo. Cause My Function To Run Forever?

Jan 13, 2006

Lately I did a mass update on all our scripts and addeddbo. in front of all tables and other objects.There is a function that returns a table and the function isI think 300 lines. There are a lot of UNIONs, EXISTS,NOT EXISTS, etc...Anyhow there's a single place where if i add dbo. in frontof the table this function which is being called from an SP,when i run the SP from the QA or from the ASP applicationit runs forever. As soon as i remove the dbo. again it runsin 6-8 seconds. Here's the thing this same table reference existsa few lines below in the 2nd UNION for example andhaving a dbo. in front doesn't cause any problems.Even more confusing if i add Databasename.dbo.TableNameand run it again, i don't run into any problem.So it's almost like if i specify dbo. in front of the table, somehowSQL Server or our code is getting lost and searching for thistable in other databases?Has anyone run or seen such a problem?I am sure I can make changes to the code and end upwriting a different code but before I make changes I wouldlike to find out more about my mystery problem.I am running SQL 2000 SP4 and the same problem occuredon two different machines. Win 2000 Pro and Win 2000 Server.Any ideas, suggestions?Thank you

View 4 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

Take Forever To Take Database Offline

Mar 5, 2007

Hi,

I try to restore a database but it pop error said "the database is in use". So, I try to take the database offline so that I can store the database. But it takes me forever (1 hour till now). It is still showing "1 remaining". Do you have any idea why ? Thank you very much in advance!

View 4 Replies View Related

Keep Package Alive Forever

Oct 16, 2006

Hi there,

Does anyone know how i can keep an ssis package used for real time reporting alive no matter the amount of errors it gets? So for instance the server im streaming to is shutdown for maintenance, and the connection dies, its needs to just keep re-trying. In other words the maximum error count is infinite. i dont just want to set max err count high, i want it out of the picture all together.



Thanks

View 7 Replies View Related

Simple (?) SQL 7.0 Update Taking Forever...

May 3, 2000

I have a simple update/initialization query (set integer column = 0 on all rows) that's been running for over 28 hours. There are just over 27 million rows in the table. In current activity it shows that the transaction is open but it's sleeping, and in locks it shows 1 DB S mode lock, 766 page X mode locks, 1 page U mode lock, and one table X mode lock. Server is 7.0 with 1.7 gig ram. Anyone have any ideas as to why it's taking so long? Table is about 7 gig in size; can't get to it in Enterprise Manager without locking it up...

View 3 Replies View Related

Update Stmt Takes Forever

Nov 26, 1998

We have a MS SQL Server 6.5 database table with 643,000 records.
There are several indexes including some clustered indexes.

We do a statement: update wo set udf3 = '1234567890123456' where woid = '123'

this returns immediately.

Then we try the same statement where the string is 1 character longer and it
takes 45 minutes to return. There is no indication of what the server is doing
during this time.

There is no index on UDF3 and WOID is the primary key.

Any suggestions what is happening? What can we do to correct it?
DBCC CheckTable finds no errors.

name rows reserved data index_size unused
-------------------- ----------- ------------------ ------------------ ------------------ ------------------
WO 643124 493418 KB 321580 KB 169824 KB 2014 KB

View 1 Replies View Related

Stored Procedure Taking Forever To Run

Apr 9, 2008

Hi there i have a sql server 7 database that i copied across to another server this time running windows 2003 and sql server 8. I have a routine that runs every night on each machine. on the old machine it take about 2 hours to do. on this new machine it is taking up to 5.5 hours to do the exact same job. the results are the same but the time delay could become an issue later on so i would like to nip it in the bud now.

Does anyone ahe any suggestions as to why the code would run so much slower on a newer and better spec machine.

I have copied everything across so there is no difference in tables or stored procedures. is there an optimisation tool i can run ??

has anyone got any ideas ?

View 6 Replies View Related

NOLOCK Query Waits Forever

Apr 22, 2008

Back in the days of SQL 7.0 I used a lot of ODBC SELECT querying form VB applications, in which I implemented NOLOCK in order to prevent the primary business applications from being locked out of tables once the queries were run.

Now, quite a few years later, I'm busying myself converting a lot of old Access based forms and queries to TSQL on SQL-Server 2000, and wonder aimlessly why NOLOCK queries (simple select ones) are imensely slower than a standars select clause.


SELECT * FROM employees


This would be much much faster than the code below, but users would get "The current record could not be accessed, as it is being used by another user", evidently because I'm locking the record while producing the output.


SELECT * FROM employees (nolock)


So this could should - as I remember it - do a dirty read on table, not obstructing other users and give me a snapshot of date as they are, although they might be locked for edit.

Could anyone explain to me why the nOLOCK query fials to give me any output? It is as if the nolock request is waiting for the table/records to free? In which case I'll never be able to run a query.

Cheers in advance, Trin

View 14 Replies View Related

Locks And Forever Runnable Processes

Jul 20, 2005

Hello all,I'm using SS 2000 and NT4 (and Access97 as front-end on another server)Well, probably by lack of knowledge about table locks, I don't really knowwhere to start to present this problem. In Enterprise manager, section"Management->Current activity->Locks/Objects", we have a couple (5-7) of"forever runnable" processes, all related to two specific situations. Eachof them are for "SELECT" statements. It's been a long time since it's likethat. I've always been curious about them but the weren't causing anyproblem. Now, after a modification, a third situation happened ("SELECT"again)... and sometime a lock created by this new "forever runnable" processblocks other functions that use the same table. All my table are linked withan ODBC link.Any help or suggestion where to search would be appreciated.Thanks.Yannick

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

What Is Wrong With This Query..It's Taking Forever...

May 16, 2008



USE GLPDEMO
GO

select t.name as TriggerName, ta.name as TableName, o.parent_obj
into GLPDemo.dbo.Temp_TablesAndTriggers
from sysobjects o inner join sys.triggers t
on t.object_id = o.id inner join syscomments c
on c.id = t.object_id inner join sys.tables ta
on ta.object_id = o.parent_obj
where xtype = 'tr' and c.text like '%Audit%'


DECLARE @DBTrigger as varchar(100), @DBTable as varchar(100), @exestr as varchar(100)

DECLARE TCursor CURSOR for

SELECT TriggerName, TableName from Temp_TablesAndTriggers

OPEN TCursor

FETCH NEXT FROM TCursor
INTO @DBTrigger, @DBTable

WHILE @@FETCH_STATUS = 0


select @exestr = ' DISABLE TRIGGER GLPDemo.dbo.' + @DBTrigger + ' ON GLPDemo.dbo.' + @DBTable


EXECUTE sp_executesql @exestr;

FETCH NEXT FROM TCursor
INTO @DBTrigger, @DBTable

CLOSE TCursor
DEALLOCATE TCursor

--DROP TABLE #Temp_TablesAndTriggers

View 4 Replies View Related

SELECT In A Table Takes FOREVER

May 25, 2006

SQL Server 2000, QA Database: A table called Telephone_Directory with just 4.000 records.

SELECT * FROM Telephone_Directory is taking forever.

If I stop the select after 1 second I see 162 rows.

If I stop the select after 1 minute I see again 162 rows.

Why this could be happening?

The same querie on Production Database is taking 6 seconds to retrieve the 4.000 records.





View 13 Replies View Related

Fill DataSet Takes Forever, Query Db 7 Sec

Jan 16, 2007

Hi,
I got a weird problem. I've created a sp that takes in the query analyzer 7 seconds to run. When i put in my code dataAdapter.Fill(dataSet.Tables(0)) it takes forever to finish!!
What's going on?
Any thoughts highly appreciated.
t.i.a.,ratjetoes.

View 2 Replies View Related

Why Does 'alter Database NorthWind SET ENABLE_BROKER' Take Forever?

Feb 2, 2008

I am trying to execute the following query , in Management Studio. But it takes forever. Can someone tell me why is this happening? I am running the query in 'NorthWind' database.The windows account  under which I am logged into WinXP (windows authentication is enabled for the SQL Server database) is the database owner for NorthWind database.
alter database NorthWind SET ENABLE_BROKER

View 3 Replies View Related

Restore Database Problem - LOADING Forever

Sep 17, 2004

I had to restore a database late this afternoon. I have the database set to FULL recovery. Database backups are performed nightly and transaction log backups are performed every other hour. I decided to perform a point-in-time restore. When I restored this way everything seems to go ok and it finishes. Then the database is grayed and says "Loading". Although I tried 4 separate times, one time allowing over an hour, the grayed out database and "Loading" never goes away.

Freaking out I deleted the "Loading" database (didn't delete logs and backup files) and tried a manual restore from the previous night's backup file. It attached and restored properly and was ready to go in 2 minutes.

Of course I wanted to get the transaction log files restored too, since it had work from earlier today. So I tried another restore via point-in-time and got the same old messages. Currently, the database is running with the previous night's backup restored but the users aren't too keen on having to do 5 hours worth of work to catch up to the previous transaction log backup come Monday morning.

Any suggestions?

Thanks,
JB

View 2 Replies View Related

Update Operation Takes Forever! How Can I Speed It Up?

Jul 20, 2005

I'm having a problem with an update operation in a stored procedure. Itruns so slowly that it is unusable, unless I comment a part out in whichcase it is very fast. However, I need the whole thing :). I have atable of email addresses of people who want to get invited to parties.Each row contains information like email address, city, state, country,and preferences for what types of events are of interest.The primary key is an EMAILID, and has a unique constraint on the emailfield. The stored procedure receives the field data as arguments, andinserts the record if the email address passed is not in the database.This works perfectly. However, if the stored procedure is called for anemail address that already exists, it updates the existing row insteadof doing an insert. This way I can build a web page that lets peoplemodify their preferences, opt in and out of the list and so on.If I am doing an update, the stored procedure runs SUPER SLOW (and thepage times out) unless I comment out the part of the update statementfor city, state, country and zipcode. However, I really need to be ableto update this!My database has 29 million rows.Thank you for telling me anything about how I can speed up this update!Here is the SQL statement to run the stored procedure:declare @now datetime;set @now = GetUTCDate();EXEC usp_EMAIL_Subscribe @Email='dberman@sen.us', @OptOutDate=@now,@Opt_GenInterest=1, @Opt_DatePeople=0, @Opt_NewFriends=1,@Opt_OldFriends=0, @Opt_Business=1, @Opt_Couples=0, @OptOut=0,@Opt_Events=0, @City='Boston', @State='MA', @ZCode='02215',@Country='United States'Here is the stored procedure:SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOALTER PROCEDURE [usp_EMAIL_Subscribe](@Email [varchar](50),@Opt_GenInterest [tinyint],@Opt_DatePeople [tinyint],@Opt_NewFriends [tinyint],@Opt_OldFriends [tinyint],@Opt_Business [tinyint],@Opt_Couples [tinyint],@OptOut [tinyint],@OptOutDate datetime,@Opt_Events [tinyint],@City [varchar](30), @State [varchar](20), @ZCode [varchar](10),@Country [varchar](20))ASBEGINdeclare @EmailID intset @EmailID = NULL-- Get the EmailID matching the provided email addressset @EmailID = (select EmailID from v_SENWEB_EMAIL_SUBSCRIBERS whereEmailAddress = @Email)-- If the address is new, insert the address and settings. Otherwise,UPDATE existing email profileif @EmailID is null or @EmailID = -1BeginINSERT INTO v_SENWEB_Email_Subscribers(EmailAddress, OptInDate, OptedInBy, City, StateProvinceUS, Country,ZipCode,GeneralInterest, MeetDate, MeetFriends, KeepInTouch, MeetContacts,MeetOtherCouples, MeetAtEvents)VALUES(@Email, GetUTCDate(), 'Subscriber', @City, @State, @Country, @ZCode,@Opt_GenInterest, @Opt_DatePeople,@Opt_NewFriends, @Opt_OldFriends, @Opt_Business, @Opt_Couples,@Opt_Events)EndElseBEGINUPDATE v_SENWEB_EMAIL_SUBSCRIBERSSET--City = @City,--StateProvinceUS = @State,--Country = @Country,--ZipCode = @ZCode,GeneralInterest = @Opt_GenInterest,MeetDate = @Opt_DatePeople,MeetFriends = @Opt_NewFriends,KeepInTouch = @Opt_OldFriends,MeetContacts = @Opt_Business,MeetOtherCouples = @Opt_Couples,MeetAtEvents = @Opt_Events,OptedOut = @OptOut,OptOutDate = CASEWHEN(@OptOut = 1)THEN @OptOutDateWHEN(@OptOut = 0)THEN 0ENDWHERE EmailID = @EmailIDENDreturn @@ErrorENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOFinally, here is the database schema for the table courtesy ofenterprise manager:CREATE TABLE [dbo].[EMAIL_SUBSCRIBERS] ([EmailID] [int] IDENTITY (1, 1) NOT NULL ,[EmailAddress] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[OptinDate] [smalldatetime] NULL ,[OptedinBy] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[FirstName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[MiddleName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[LastName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[JobTitle] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[WorkPhone] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[HomePhone] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[AddressLine1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[AddressLine2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[AddressLine3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[City] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[StateProvinceUS] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[StateProvinceOther] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[Country] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[ZipCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SubZipCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GeneralInterest] [tinyint] NULL ,[MeetDate] [tinyint] NULL ,[MeetFriends] [tinyint] NULL ,[KeepInTouch] [tinyint] NULL ,[MeetContacts] [tinyint] NULL ,[MeetOtherCouples] [tinyint] NULL ,[MeetAtEvents] [tinyint] NULL ,[OptOutDate] [datetime] NULL ,[OptedOut] [tinyint] NOT NULL ,[WhenLastMailed] [datetime] NULL) ON [PRIMARY]GOCREATE UNIQUE CLUSTERED INDEX [IX_EMAIL_SUBSCRIBERS_ADDR] ON[dbo].[EMAIL_SUBSCRIBERS]([EmailAddress]) WITH FILLFACTOR = 90 ON[PRIMARY]GOALTER TABLE [dbo].[EMAIL_SUBSCRIBERS] WITH NOCHECK ADDCONSTRAINT [DF_EMAIL_SUBSCRIBERS_OptedOut] DEFAULT (0) FOR [OptedOut],CONSTRAINT [DF_EMAIL_SUBSCRIBERS_WhenLastMailed] DEFAULT (null) FOR[WhenLastMailed],CONSTRAINT [PK_EMAIL_SUBSCRIBERS] PRIMARY KEY NONCLUSTERED([EmailID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_WhenLastMailed] ON[dbo].[EMAIL_SUBSCRIBERS]([WhenLastMailed] DESC ) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_OptOutDate] ON[dbo].[EMAIL_SUBSCRIBERS]([OptOutDate] DESC ) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_OptInDate] ON[dbo].[EMAIL_SUBSCRIBERS]([OptinDate] DESC ) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_ZipCode] ON[dbo].[EMAIL_SUBSCRIBERS]([ZipCode]) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_STATEPROVINCEUS] ON[dbo].[EMAIL_SUBSCRIBERS]([StateProvinceUS]) ON [PRIMARY]GOMeet people for friendship, contacts,or romance using free instant messaging software! See a picture youlike? Click once for a private conversation with that person!<a href="http://www.sen.us"><imgsrc="http://www.sen.us/mirror/SENLogo_62_31.jpg"></a>*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 9 Replies View Related

SQL 2005 To SQL 2000 Update Takes Forever

May 7, 2007

I have a SQL 2005 & SQL 2000 server. I am attempting to execute a simple update statement, something that looks like:



update AD

set AD.SomeDate = getdate()

from [ServerX].DB.dbo.Table

where ColumnX = 'X'



ServerX is the SQL 2000 box.

ServerY is the SQL 2005 box. Server Y is where this statement is invoked from. (Not shown in statement).



I have a linked server set up.



When executed from the 2000 box, it runs in < 1 second.



When both environments are 2005 to 2005, it takes less than < 1 second.



View 1 Replies View Related

Simple Select Query Runs Forever

Dec 14, 2007



Hi all,

I know this sounds rather dumb but my select statement is running forever!
I am trying to execute the statement through my C# code.

If I try to run the query through sql server management studio, sometimes it runs fine, but sometimes it keeps running and never returns a value.

I am doing something like this:

###########################################################################################
String query = "SELECT studentID from StudentTable WHERE studentDeptID = '100' AND deptName = 'CS'";

SqlCommand command = new SqlCommand(query, connection);
Object myObject = command.ExecuteScalar();

############################################################################################

StudentTable contains roughly 1000 rows.


Somebody please help me out of this. Thanks in advance.

Surya

View 11 Replies View Related

DB Engine :: Query With WHERE Condition That Lasts Forever

May 27, 2015

I've a complex view on a SQL 2014 Enterprise Edition. If I query the view with:

SELECT * FROM myComplexView
it takes 14 seconds to completes
if I want a subset of the result and I run the query with a WHERE clause:
SELECT * FROM myComplexView WHERE [Season]='A16'

The query never completes (I've waited 10 minutes and then cancelled the task).

View 3 Replies View Related

Sql2005 Replication, Droping It Takes Forever

Apr 20, 2007


I have a database that is about 300 gig. I am setting up replication to a reporting server. We are doing a series or mock loads and I will need drop the tables and reload the main database a few times before we go live. To do that I plan to stop replication and drop all the articles, drop the subscription, then load the new data, then reinitialize and restart replication.




The first time I tried to do this, when I drop the articles, it seems to be trying to "clean up" the distribution database on the reporting server and that is taking a couple of hours to do. The disruption database is about 40 gig.



Is this correct behavior in SQL2005 replication? Is there a way to avoid this? I have all the replication pieces scripted out and would like to just drop replication, reload, and then run my scripts to recreate replication. But this "clean up" is going to cause me a lot of headache if I don't figure out what is going on.



Am I going down the wrong road here? Is there an easier way to do this? Any comments would be great!!!!



Thanks in advance for any help.



Jim Youmans

St. Louis Missouri

View 1 Replies View Related

Cursor In Sproc To Kill Spids Loops Forever

Mar 20, 2000

I need a stored proc to kill spids, but the following sproc loops infinitely with the same [correct] spid being printed out. What am I doing wrong?

The select statement, when I execute it via the query grid, returns the correct and finite number of spids.

Any help greatly appreciated.
Judith


CREATE PROCEDURE rasp_KillDBProcess
@dbname varchar(128)
AS
declare @KillSpid smallint
declare @SQL varchar(1000)
--
declare DBCursor cursor Forward_only for SELECT distinct l.spid
FROM master.dbo.syslocks l INNER JOIN
master.dbo.sysdatabases d ON l.dbid = d.dbid
WHERE (d.name = N'coj_pcisdata')
open DBCursor
--
Fetch next from DBCursor into @Killspid
--
While (@@Fetch_status <> -1)
Begin
If (@@Fetch_status <> -2)
begin
print 'spid = ' + cast(@killspid as varchar(12))
--exec ('kill ' + @killspid)

end
--
end
Fetch next from DBCursor into @Killspid
--
close dbcursor
deallocate dbcursor
print 'end'

return

View 1 Replies View Related







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