Faster Remove Duplicate SQL

I have a table containing over 100,000 email addresses. This email table gets duplicates in it, and our customers don't want a second (or third or fourth) copy of our news letter. To prevent this, we run the following SQL to kill the duplicates:

Code Snippet

DELETE FROM _email WHERE _email.eid IN
(
SELECT tbl1.eid FROM _email AS tbl1 WHERE Exists
(
SELECT emailaddress, Count(eid) FROM _email WHERE _email.emailaddress = tbl1.emailaddress GROUP BY _email.emailaddress HAVING Count(_email.eid) > 1
)
)
AND _email.eid NOT IN
(
SELECT Min(eid) FROM _email AS tbl1 WHERE Exists
(
SELECT emailaddress, Count(eid) FROM _email WHERE _email.emailaddress = tbl1.emailaddress GROUP BY _email.emailaddress HAVING Count(_email.eid) > 1
)
GROUP BY emailaddress
);

This query takes about 2hrs to run which is really hurting our server preformance. Is there any way to do this faster?

I am running SQL Server 2000

Thanks in advance

Create a unique non clustered index and turn on IGNORE_DUP_KEY after you run your code to delete all the existing duplicates. Then SQL Server will not insert duplicates to the column, try the link below for index options.

http://msdn2.microsoft.com/en-us/library/ms186869.aspx

|||

You can try the following queries,

Code Snippet

delete from _email

from _email A

join (select min(eid) eid,email from _email group by email) B

on A.email = B.email

Where

b.eid <> a.eid

Code Snippet

delete from _email

from _email E

Where exists

(

select 1 from _email A

join (select min(eid) eid,email from _email group by email) B

on A.email = B.email

Where

b.eid <> a.eid

and E.eid=A.eid

)

|||

If you are using SS 2005, then try:

;with cte

as

(

select *, row_number() over(partition by emailaddress order by eid ASC) as rn

from _email

)

delete cte

where rn > 1;

AMB

FAO Hilary Cotter

Hilary,
please can you send me an alternative email address - I seem to be marked as
a dodgy spammer on your current one:
"<smtp-out1.blueyonder.co.uk #5.5.0 smtp;550-195.188.213.4 blocked by
blacklist.mail.ops.worldnet.att.net.>"
Thanks,
Paul
hilarycotter1@.hotmail.com
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23uDacMWiEHA.1280@.TK2MSFTNGP09.phx.gbl...
> Hilary,
> please can you send me an alternative email address - I seem to be marked
as
> a dodgy spammer on your current one:
> "<smtp-out1.blueyonder.co.uk #5.5.0 smtp;550-195.188.213.4 blocked by
> blacklist.mail.ops.worldnet.att.net.>"
> Thanks,
> Paul
>

Failure sending mail: The system cannot find the path specified.

I have had a development XP workstation with RS2000 and IIS sending reports via email for 6 months straight with no problems.

Today my subscriptions are failing with

Failure sending mail: The system cannot find the path specified.

Been searching and digging for hours, and if anyone has any ideas they are greatly appreciated.

I need some info to troubleshoot...

Replicate the issue, then go the Report Server log (ReportServer_xxx_xxx.log) and email the log to me for review. markcam80@.hotmail.com

What I will be looking for is explicit error messages versus the displayed message.

|||Thanks for the help but it seems we had a domain controller misbehaving.

Failure sending email in subscriptions

I am running SQL 2005, I have the latest service pack on SQL 2005 and also on
the Windows 2003 server. This is on a single server. I am using my Exchange
2003 server as my SMTP server which is on a different server with the same
subnet.
-When a domain user schedules a subscription it won't send an email.
-When a domain admin schdules a subscription the email will get sent.
-When a domain user schedules a subscription with ONLY the link option, it
will send an email and the user can access the link.
- I tried setting my RS config to use Local System and also Network Service,
and that did not make a difference.
In the RS logs I get the error...
Success: False, Status: Failure sending mail: The report server has
encountered a configuration error. See the report server log files for more
information., DeliveryExtension: Report Server Email, Report: Purchase
Requests Ordered But Not Received, Attempt 0
I also get an error in the Windows event log when a subscription is created
or editted, but I'm not sure if it is related. The error is..
Report Server (MSSQLSERVER) cannot load the Report Server DocumentLibrary
extension.
Here is my RS config file..
<RSEmailDPConfiguration>
<SMTPServer>192.168.100.200</SMTPServer>
<SMTPServerPort></SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing>2</SendUsing>
<SMTPAuthenticate></SMTPAuthenticate>
<From>hqjobs01Reports@.trinitystairs.com</From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
<RenderingExtension>RGDI</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>False</SendEmailToUserAlias>
<DefaultHostName>trinitystairs.com</DefaultHostName>
<PermittedHosts></PermittedHosts>
</RSEmailDPConfiguration>
Please Help!Having the same problem. Tried everything and nothing worked. Next week I
will try transferring all my services to run under domain admin account to
see if that helps.
These are some of the links where I have explained my problem. Read just to
make sure that we have same problem.
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1769187&SiteID=17
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.opsmgr.reporting&mid=c1fdce30-5db0-487d-931e-1e17cb0c387b
"Pablo" wrote:
> I am running SQL 2005, I have the latest service pack on SQL 2005 and also on
> the Windows 2003 server. This is on a single server. I am using my Exchange
> 2003 server as my SMTP server which is on a different server with the same
> subnet.
> -When a domain user schedules a subscription it won't send an email.
> -When a domain admin schdules a subscription the email will get sent.
> -When a domain user schedules a subscription with ONLY the link option, it
> will send an email and the user can access the link.
> - I tried setting my RS config to use Local System and also Network Service,
> and that did not make a difference.
> In the RS logs I get the error...
> Success: False, Status: Failure sending mail: The report server has
> encountered a configuration error. See the report server log files for more
> information., DeliveryExtension: Report Server Email, Report: Purchase
> Requests Ordered But Not Received, Attempt 0
>
> I also get an error in the Windows event log when a subscription is created
> or editted, but I'm not sure if it is related. The error is..
> Report Server (MSSQLSERVER) cannot load the Report Server DocumentLibrary
> extension.
> Here is my RS config file..
> <RSEmailDPConfiguration>
> <SMTPServer>192.168.100.200</SMTPServer>
> <SMTPServerPort></SMTPServerPort>
> <SMTPAccountName></SMTPAccountName>
> <SMTPConnectionTimeout></SMTPConnectionTimeout>
> <SMTPServerPickupDirectory></SMTPServerPickupDirectory>
> <SMTPUseSSL></SMTPUseSSL>
> <SendUsing>2</SendUsing>
> <SMTPAuthenticate></SMTPAuthenticate>
> <From>hqjobs01Reports@.trinitystairs.com</From>
> <EmbeddedRenderFormats>
> <RenderingExtension>MHTML</RenderingExtension>
> </EmbeddedRenderFormats>
> <PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
> <ExcludedRenderFormats>
> <RenderingExtension>HTMLOWC</RenderingExtension>
> <RenderingExtension>NULL</RenderingExtension>
> <RenderingExtension>RGDI</RenderingExtension>
> </ExcludedRenderFormats>
> <SendEmailToUserAlias>False</SendEmailToUserAlias>
> <DefaultHostName>trinitystairs.com</DefaultHostName>
> <PermittedHosts></PermittedHosts>
> </RSEmailDPConfiguration>
>
> Please Help!

Failure Precedence Constraints stopped working in my package

I had a Send email task linked to my Sequence Containers in my package and it was working fine. Everytime the container fails it would send an email to myself.

At some point all Failure constraints stopped working. Failure constraints work if I add brand new tasks, but with the existing tasks, they don't work. The Task which fails, turns red and execution stops. Next failure task is not executed.

I am not sure what triggered it to stop working. I cannot get anything on the log

Any help is appreciated.

I've resolved this issue.

I've added an Onerror Event Handler and changed the failure constaints to Logical Or.

It is working now...

|||

This is not consistent at all...

Next day failure precedences are not working again. For workaround I created onerror event handlers for each container to send an email. This time I got lock errors on the errDescription variable.