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


ADVERTISEMENT

Revisiting Deadlocks And Blocking

Jul 12, 2004

ok, about a few months ago I brought up the issues we were having with deadlocks. My company is running a .net data entry application hitting against a sql 2000 box. Forget hardware since we have more then enough hardware to handle the load.

The issue we are seeing is we get a process that blocks a table, and the bulding up of block process that cause timeouts/deadlocks etc. A couple of things we see when this happens is that .net on the web server seems to open up more and more connections(it seems to double or triple the connections as the blocking begins), we start to see deadlocks and we see alot of timeouts in the app due to the blocking.

The blocking aways happen on an insert of an order or a customer. One thing I keep wondering about is how we handle the unique id for our tables. Our DBA had us put in the following:

Begin Trans
select max(id) + 1
insert....
commit trans

I've heard from a few here and in other forums this is the wrong way to go. I'm still get a gut feeling that this could start causing issues like what we are seeing. My problem is I'm not knowledgable enough to make a strong enough case with it.

What I need help is getting enough information to either point to how we manage ID's or at the very least rule them out. I've done searchs in google and some other search engines but there doesnt seem to be any good articles on how best to handle unique ids and show a good case for it. I do know I can use sql identity, or even guid with the newid() function but I need solid proof before my boss will move to one of these methods. Any links you guys can give or even your own write up will really help my case and hopefully help my headache.

Thanks Ahead of time

View 3 Replies View Related

Replication - Several Transactions Occurring

Feb 8, 2012

I have configured a transactional replication between 2 MSSQL 2005 instances. It is set to replicate every 15 minutes. Most replications take less than a minute with about 10 - 50 transactions being replicated.

However, 3 times during the day, the replication takes about 25 minutes to complete with 500-700 transactions being replicated. This also causes the application that is accessing the DB being replicated to become unresponsive when it attempts to retrieve rows from tables in the DB.

Might there be a setting that causes the transactional replication to accumulate transactions during the day?

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

Replication Deadlocks

Sep 16, 2005

Hi All,

View 15 Replies View Related

Snapshot Replication Deadlocks

Feb 4, 2004

Is there anyway to prevent deadlocks during the snapshot replication?

I understand that you can minimize by maybe creating a couple different snapshots (mixing tables to minimize locking while snapshot is being created), but is there any other way?

Thank you

View 1 Replies View Related

SQL2000 Replication Deadlocks

Jun 1, 2007

We have an SQL2000 database server that uses merge, pull replication with about 70 clients around the country, each with their own publication. Each article in each publication is row filtered by a single condition. There are busy times of the day when all of these clients attempt to replicate within a 15-20 minute period. We have noticed the following error that is creating conflicts that are resulting in data not being transferred to the server and very long replication run times (error message is paraphrased):



The record was inserted at the client, but could not be inserted at the server. Transaction (Process ID XXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.



Is there a way to lessen the effects of this error? How can I reduce the number of deadlocks that are occurring? We will need to be adding a great deal more clients to the system in the future; is there a way to grow the system without making the deadlock problem worse?



Any help will be appreciated!



Thanks,

Craig



View 1 Replies View Related

SQL 2012 :: Deadlocks Between Application Queries And Replication?

Jul 10, 2014

We recently upgraded to sql server 2012. We have xxx-D-011 as OLTP server and yyy-D-011 as distributor server.

The log is showing deadlocks every day between application queries/updates and replication jobs.

A fragment of the log about the deadlock is included below.

2014-07-10 15:31:05.94 spid13s deadlock-list
2014-07-10 15:31:05.94 spid13s deadlock victim=process37ced3498
2014-07-10 15:31:05.94 spid13s process-list
2014-07-10 15:31:05.94 spid13s process id=process37ced3498 taskpriority=0 logused=0 waitresource=OBJECT: 8:532249001:0 waittime=357 ownerId=860304057 transactionname=SELECT lasttranstarted=2014-07-10T15:31:05.090

[code]....

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

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

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

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

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

Updates Not Occurring When Using Execute

Apr 12, 2005

Create Procedure UpdateGameIsOverlappingFlagByIds (@gameDateIds as varchar(200) = '') as
begin
    if (@gameDateIds = '')
        raiserror('UpdateGameIsOverlappingFlagByIds: Missing parameters', 16,1)
    else
        begin
            Execute
('update games Set IsOverlapping = 1 where gameDateId IN (' +
@gameDateIds + ')')

        end
end   
return
--------------------------------------------
That is the sproc that doesn't seem to update the date when called from
asp.net but does work when done through the query analyzer.  Am I
missing something?

Thanks for any help you can give me.

View 4 Replies View Related

Exception Repeately Occurring

Jul 23, 2005

HiI have a strange problem with my SQL server 2k instllation - every 10mintutes when I have the Profiler trace with the entire "Errors" Eventcategory selected, the following 5 exceptions show up in the trace andthat too for the same SPID.Error: 16955, Severity: 16, State: 2Error: 16945, Severity: 16, State: 1Error: 16955, Severity: 16, State: 2Error: 16945, Severity: 16, State: 1Error: 16955, Severity: 16, State: 2Error: 16945, Severity: 16, State: 1I have no clue why this is occurring - I tried running a trace with theSP:StmtCompleted event on, but no other stored procedures show up withthe same spid close to the time where this exception is logged.Does anyone have a clue as to why this error is occurring ?Rahul

View 1 Replies View Related

Transact SQL :: Get Max Occurring Value Using GROUP BY

May 14, 2015

I have 3 columns of data CustomerNum, Newsletter, and NumSent.

I need to return the only the CustomerNumber and Newsletter combinations having the Max(NumSent) So it should be a unique customernumber with the newletter having the most numsent.

For the data below, my result set will be:

0000000000000000101 Healthcare
0000000000000000102 Construction-Environ Svcs

How can I do this easily with GROUP BY , Max or subselect?

Sample Data:
0000000000000000101 Healthcare                       19
0000000000000000101 Construction-Environ Svcs 11
0000000000000000101 Manufacturing                   8               

0000000000000000101 Homecare                        5
0000000000000000101 Daycare                          4
0000000000000000102 Healthcare                       9
0000000000000000102 Construction-Environ Svcs 21
0000000000000000102 Manufacturing                   5              

0000000000000000102 Homecare                        11
0000000000000000102 Daycare                          1

View 6 Replies View Related

FTP Task Error Occurring

Apr 16, 2007

This is strange... just started getting this today, while it's worked for weeks. But alas, that is programming.

The error I'm getting is

[Connection manager "FTP DQ Connection"] Error: An error occurred in the requested FTP operation. Detailed error description: 200 Type set to I. 200 Command okay. 550 /usfsr/DFE_INPUT/FATest_DQ.txt: No such file or folder. .

I'm uploading a file (FATest_DQ.txt) to the folder /usfsr/DFE_INPUT/. I eventually just uploaded the file manually using IE6 to make sure the connection properties were still the same.

Any ideas?

View 1 Replies View Related

Number Events Occurring Within A Period

Mar 17, 2008

I have TableA with the following structure and Data.


CaseID ActionID StartDate
------ ------------ --------------
A232/21/2007
A212/8/2007
B2212/4/2007
B1310/12/2007
B1710/7/2007
C617/6/2007
D3311/2/2007
D5610/22/2007
D267/29/2007
E226/21/2007
E585/25/2007
E874/8/2007
E293/23/2007

Expected Results:
I want to create TableB with the following structure and data .

CaseIDX YZ
----- - - -
A001
B011
C000
D001
E111


X = 1 when a case has 4 or more actions within 180 days , 0 otherwise
Y= 1 when a case has 3 or more actions within 90 days , 0 otherwise
Z= 1 when a case has 2 or more actions within 30 days , 0 otherwise

Any help will be welcomed

View 12 Replies View Related

Command.ExecuteNonQuery() Error Occurring NULL Value

Apr 2, 2008

Here is the table that I am wanting to insert the information into (BTW I am wanting the UserID, EntryDate, Note) On my page i do have a text box for the UserID as well as a Note textbox. When I hite the submit button on my page I am already sending the UserID textbox information to be sent to another table (called RequestTable). However, I am wanting to take that same UserID and insert it into the RequestNote table as well. Let me know if you have any questions for me to further explain anything.
**RequestNote**RequestNoteKey (PK)  (has identity set to yes)RequestKey (allows nulls)NoteEntryDateEntryUserID  (allows Nulls)****This is my stored procedure that I am calling called "NoteInsert"***@Note nvarchar(1000),@EntryUserID nvarchar(50)AS INSERT INTO RequestNote (Note, EntryDate,EntryUserID)
VALUES (@Note,GetDate(), @EntryUserID)
RETURNGO
****THIS IS THE PAGE THAT CONNECTS THE USER INTERFACE AND STORED PROCEDURE***public static void AddRequestNote(string requestNote, string userID){using (SqlConnection connection = new SqlConnection(connRequestNote)){using (SqlCommand command = new SqlCommand("NoteInsert", connection)){command.CommandType = CommandType.StoredProcedure;command.Parameters.Add(new SqlParameter("@Note", requestNote));command.Parameters.Add(new SqlParameter("@EntryUserID", userID));connection.Open();command.ExecuteNonQuery(); <--THIS IS WHERE I GET AN ERROR THAT SAYS Cannot insert the value NULL into column 'RequestNoteKey', table 'RequestNote'; column does not allow nulls. INSERT fails}

View 5 Replies View Related

Rows Affected- But Update Not Physically Occurring

Mar 15, 2004

I have a mixed mode account setup with exec permissions granted on my stored procedures. I am running an sp that is editing a member, and if i run it using my query analyzer with the same login, my sp writes to the db. however, if i'm calling it through my web app (asp.net) it doesn't. here is my sp code:

USE DATABASE
--DROP PROC sp_EditMember
GO

--Create the stored procedure
CREATE PROCEDURE sp_EditMember
@member_id smallint,
@last_name nvarchar(50), --not nullable
@first_name nvarchar(50),
@spouse_name nvarchar(50),
@street_address nvarchar(50),
@city nvarchar(35),
@state nvarchar(5),
@zip_code nvarchar(15),
@zip_4 nvarchar(4),
@area_code nvarchar(10),
@phone_number nvarchar(20),
@email nvarchar(50),
@child_1 nvarchar(30),
@child_2 nvarchar(30),
@child_3 nvarchar(30),
@child_4 nvarchar(30),
@child_5 nvarchar(30),
@member_status nvarchar(20),
@member_exp nvarchar(10),
@plaques_st nvarchar(10)

AS
BEGIN TRAN
UPDATE Members
SET last_name = @last_name, first_name = @first_name,
spouse_name = @spouse_name, street_address = @street_address,
city = @city, state = @state, zip_code = @zip_code, zip_4 = @zip_4,
area_code = @area_code, phone_number = @phone_number, email = @email,
child_1 = @child_1, child_2 = @child_2, child_3 = @child_3,
child_4 = @child_4, child_5 = @child_5,
member_status = @member_status, member_exp = @member_exp,
plaques_st = @plaques_st

WHERE member_id = @member_id

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
END
ELSE COMMIT TRAN
GO
--------------------------------------

on my app side- i've already ensured the datatypes match up and the sizes are all ok (except for member_id, the variable is an int and i pass it as a small_int- the number is ranging from 1000-10000).

any troubleshooting on this?

thanks in advance,

sudeep.

View 1 Replies View Related

Auto Start Of SQL Server 7.0 Agent Not Occurring

Nov 16, 2000

I am not successful in getting SQL Server Agent to startup when SQL server starts up.

I believe the problem has to do with a lack of permissions or rights. I will list the steps
below which seem to me to be all that are needed to install SQL Server Agent. I used
Enterprise Manager to get this information.

If anyone can shoot any holes in the process, please do, that may be where my
problem lies.

a. Created NT accounts called MSSQLSERVER for the server and MSSQLSERVERAGENT
for SQL Agent.

b. Each of these accounts has the "right" to logon as a service.

c. Each of these accounts has "admin" rights.

d. Each of these accounts has been added to SQL SERVER under Security and Logins.

e. The properties assigned to each of the accounts are:
(1) on the general tab -- Windows NT authentication is in effect, database is master
and language is English (2) on the server roles tab -- System Administrators is checked
(3) on the database access tab -- the master database is checked

f. Under SQL Server Agent, properties, selecting the General Tab, in the frame
titled "service startup account":
(1) the circle labelled "this account" is clicked (2) the account is filled in using
domain namemssqlserveragent (3) the correct password is filled in taking case
into consideration

g. Under SQL Server Agent, properties, selecting the Connection Tab, in the frame
titled "sql server connection":
(1) the circle labelled "use windows NT authentication" is clicked


h. Right clicking the server and selecting properties show a multi-tab window
(1) the general tab shows that all three autostart boxes are checked
(2) the security tabs shows SQL Server and Windows authentication are in effect
(3) the startup service account shows "this account" selected and the account
is filled in with domain namemssqlserver and the password is filled in (taking
case into consideration)

In all cases above, the domain name is not the domain in which the server resides,
but is in a "trusted" domain.

Any help would be very much appreciated!!!!!

View 2 Replies View Related

Transact SQL :: Check No Inserts Occurring In Tables

Sep 14, 2015

I want to check that no inserts are occurring in 5 tables that are depending on each other and then drop and create those 5 tables. I have scripts to drop and recreate the tables. How do I check that no inserts are happening in these 5 tables?

Table A
Table B dependant on
Table A
Table C dependant on
Table B
Table D dependant on
Table C
Table E dependant on
Table D

View 9 Replies View Related

Error Occurring In ExecuteSQL Task In OnPreExecute (and In OnPostExecute) But Package Not Failing

Jun 5, 2006

When any of
my tasks or script tasks in my control flow, or data flow, have an
error, the entire package, and then the calling package fail, as far as
I've seen, through some CTP versions, beta versions, and the release
version of SQL Server 2005.

But, I've just made a change, in an
sproc called from an ExecuteSQL Task in a PreExecute event attached to
a DataFlow, and that ExecuteSQL Task is reporting an error in the
output window, but the package execution is not being stopped. Why not?

I see something like so

Error: 0xC002F309 at MyExecuteSqlTaskName, Execute SQL Task: ....
Task failed: MyExecuteSqlTaskName
Warning:
0x80019002 at OnPreExecute: The Execution method succeeded, but the
number of errors raised (1) reached the maximum allowed (1); resulting
in failure. This occurs when the number of errors reaches the number
specified in MaximumErrorCount. Change the MaximumErrorCount or fix the
errors.

repeated many times in the output stream, and the same thing for the PostExecute event attached to the same object.

I'm disappointed, because I want errors propagated upwards, as I'm used to.

I
looked, and as far as I can tell, all my ExecuteSQL tasks (in events
and in the regular control flow) have the default settings of

FailPackageOnFailure: False
FailParentOnFailure: False
MaxErrorCount: 1


Do
I have to go revise these settings on every ExecuteSQL Task in every
event handler in every SSIS package? (That will be exceedingly
tedious.) Do I set these on the ExecuteSQL task inside the event? Do I
set these on the event handler itself?

View 3 Replies View Related

Transact SQL :: Query To Give Results Of All Configured Alerts Occurring History

May 14, 2015

We created sql alerts on all our sql servers environments. Now, i want to see each sql server which sql alerts so far got fired and which one never occurs. is there any way, we can get this information from any system database?

View 9 Replies View Related

Severe Error Occurring When Creating Assembly With External Access Permission

Jul 11, 2006

I had created a CLR function in my db and was able to execute it successfully a couple of months ago. But when I tried to execute it today it was throwing errors saying there was something wrong with the permissions on the assembly. So I decided to drop everything and recreate it except I can not longer create the assembly with EXTERNAL ACCESS permissions. Whenever I try to create the assembly I get the followng error:

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

I also tried to create the assembly with Unsafe permissions and got the same error. Does anyone know why this error would be occurring now? I tried creating the same assembly on a different SQL 2005 server and it creates successfully and can be executed successfully. Any help would be greatly appreciated!!



Thanks!

GN

View 3 Replies View Related

Deadlocks

Mar 17, 2005

Our system is reasonably complex with a lot of non-trivial stored procedures. As the load on our DB increased we're now getting more and more deadlocks (10 per day or so from about a million stored proc executions).

We try to avoid transactions where we can, and we do attempt to optimse stored procs to steer clear of deadlock conditions, but with the sheer number of stored procedures we can't possibly avoid all deadlock conditions.

One solution I'm considering is to re-run stored procs that failed because of a deadlock. In the .net code we'll run the stored proc, check for a deadlock error and if one happened, wait 100ms and try again.

What do you guys think?

View 8 Replies View Related

Deadlocks

Aug 13, 2002

Hi,

we have a production inviremont that is running for about 10 months. Since a couple of weeks we are having problems with "Deadlocks".

This cant be due to an increase in data size on the tables that are having the issues because these are cleaned in the same transaction that populates them.

These tables are used to store temporary data that the production system needs to calculate the correct price for any given order. This transaction takes between 0.5 to 1 second to commit.

We are running on a dual processor machine with 1 Gb of RAM with SQL Server 7 - sp 3, Windows NT 4 sp 6, Microsoft Transaction Server.

In all our queries and stored procedures we use the optimizer hints (nolock) for select statements and (rowlock) for updates or deletes.

Any help and/or suggestions would be appriciated.

View 2 Replies View Related

Deadlocks

Dec 17, 1998

Is there any way to totally avoid deadlocks. In some critical applications
we have removed transactions entirely, counting on other means to maintain
database consistency. We still get deadlocks in this area. These are mainly
inserts, and the only thing I can think is that updates to the indexes are
causing multiple page locks which result in deadlocks. Is this true?

Will deadlocks be eliminated in 7.0 with row level locking for this situation?
Or will index page splits still cause a possibility of deadlock contention?

Thanks!
ben

View 2 Replies View Related

DeadLocks

Mar 5, 2001

Hi ,

I have a problem with a SP in 6.5. When i try to run a Stored Proc which is a simple select statement dumped into a temp table in a particular database, I lock other users who are tring to log into other databases some in tempdb database. When i try to kill the process the rollback takes almost 45 mins or so..till then no one can log on to the server.

The SP works fine when no one is logged into the Great Plains server. One more thing i observed is that, the SP when run results on a deadlock only when the owner is a user. If the owner is DBO it works fine.

Can anybody throw some light on this.

Thanks in Advance
Siv

View 1 Replies View Related

DeadLocks

Jul 10, 2002

I am getting the following dead lock error message writtent to the Error Log.

How do i interpret this...?


2002-07-10 11:49:52.88 spid3 Node:1
2002-07-10 11:49:52.88 spid3 KEY: 6:1531868524:1 (1e0040209980) CleanCnt:1 Mode: X Flags: 0x0
2002-07-10 11:49:52.88 spid3 Grant List::
2002-07-10 11:49:52.88 spid3 Owner:0x26429de0 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:62 ECID:0
2002-07-10 11:49:52.88 spid3 SPID: 62 ECID: 0 Statement Type: INSERT Line #: 67
2002-07-10 11:49:52.88 spid3 Input Buf: RPC Event: sp_Save;1
2002-07-10 11:49:52.88 spid3 Requested By:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-S-S SPID:58 ECID:0 Ec:(0x29f534f8) Value:0x2649f0c0 Cost:(0/0)
2002-07-10 11:49:52.88 spid3
2002-07-10 11:49:52.88 spid3 Node:2
2002-07-10 11:49:52.88 spid3 KEY: 6:1695345104:1 (ffffffffffff) CleanCnt:1 Mode: Range-S-U Flags: 0x0
2002-07-10 11:49:52.88 spid3 Grant List::
2002-07-10 11:49:52.88 spid3 Owner:0x26450f20 Mode: Range-S-U Flg:0x0 Ref:1 Life:02000000 SPID:58 ECID:0
2002-07-10 11:49:52.88 spid3 SPID: 58 ECID: 0 Statement Type: INSERT Line #: 250
2002-07-10 11:49:52.88 spid3 Input Buf: RPC Event: sp_IPAQManagerFetchFilterDetail;1
2002-07-10 11:49:52.88 spid3 Requested By:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-Insert-Null SPID:62 ECID:0 Ec:(0x3bb5f4f8) Value:0x2649e040 Cost:(0/2340)
2002-07-10 11:49:52.88 spid3 Victim Resource Owner:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-S-S SPID:58 ECID:0 Ec:(0x29f534f8) Value:0x2649f0c0 Cost:(0/0)

View 1 Replies View Related

Too Many Deadlocks

Oct 27, 2004

Hi,

I've got a deadlock problem. The log below has been generated. The problem is that during one day, I have more than 300 deadlocks like it. Before, the were not so many deadlocks.
During past year, the number of users has grow (from 100 before to 500 or 700 now)


*** Deadlock Detected ***
- Requested by: SPID 360 ECID 0 Mode "S"
- Held by: SPID 113 ECID 0 Mode "S"
Index: aaaaa_PK
Table: TABLE_1
Database: MYDB
== Lock: KEY: 22:325576198:1 (ff009ae5078d)
- Requested by: SPID 113 ECID 0 Mode "S"
- Held by: SPID 374 ECID 0 Mode "X"
Index: aaaaa_PK
Table: TABLE_1
Database: MYDB
== Lock: KEY: 22:325576198:1 (ff009ae5078d)
- Requested by: SPID 374 ECID 0 Mode "IX"
- Held by: SPID 360 ECID 0 Mode "S"
Table: TABLE_2
Database: MYDB
== Lock: PAG: 22:1:2428
== Deadlock Lock participant information:
Input Buf: S E L E C T the_rest_of_the_query
SPID: 360 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 1
Input Buf: s p _ e x e c u t e 8
Input Buf: s p _ c u r s o r 8À B 8 8f ç @ Table I
Input Buf: S E L E C T the_rest_of_the_query
SPID: 360 ECID: 0 Statement Type: SELECT Line #: 1
== Session participant information:
== Deadlock Detected at:
==> Process 360 chosen as deadlock victim


I have done :
- rebuild indexes on all tables (fillfactor 90)
- analysed memory activity

Could a lack of memory be at the origin of the problem ? Which counters in perfmon are significant for memory lack ?

Could the index fill factor could be at the origin of the problem ? At time, it is at 90 percent.


Config : Winnt4 Server, MS-SQL 7 SP4 , 2 GB of RAM , 2 x Xeon 700


Thanks for any help.

View 4 Replies View Related

Deadlocks (I Think)

Feb 16, 2004

Hi folks,

I have an application built on top of a questionable DB design which requires overcomplicated selects. The application is experiencing deadlocks regularly, in some cases with only one concurrent user.

I set the trace flag 1204 but am not seeing anything in the Error.log and I initiated a trace in profiler which does not seem to show any deadlock.
Despite having recreated the problem which show my browser hanging indefinitely. When I run the following queries:

SELECT spid, waittime, lastwaittype, waitresource
FROM master..sysprocesses
WHERE waittime > 10000
AND spid > 50

SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)

I get:

55860978LCK_M_XPAG: 13:1:2573

54AWAITING COMMANDsleeping sa 11499
55UPDATE sleeping sa 21499


respectively. Any help would be welcome.

Thanks in advance,
Don

View 9 Replies View Related







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