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 Repliesok, 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
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?
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.
Hi All,
View 15 Replies View RelatedIs 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
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
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]....
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 RelatedI 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 RelatedI 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 RelatedHi,
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
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
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 RelatedCreate 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.
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 RelatedI 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
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?
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
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}
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.
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!!!!!
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
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?
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 RelatedI 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
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?
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.
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
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
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)
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.
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