Can someone proofread my remove duplicates script?

DELETE
FROM tblContacts
WHERE tblContacts.ID IN(
SELECT F.ID
FROM tblContacts AS F
WHERE Exists (
SELECT email, Count(ID)
FROM tblContacts
WHERE tblContacts.email = F.email
GROUP BY tblContacts.email
HAVING Count(tblContacts.ID) > 1
)
)
AND tblContacts.ID NOT IN(
SELECT Min(ID)
FROM tblContacts AS F
WHERE Exists (
SELECT email, Count(ID)
FROM tblContacts
WHERE tblContacts.email = F.email
GROUP BY tblContacts.email
HAVING Count(tblContacts.ID) > 1
)
GROUP BY email
)

I readily admit that I've shamelessly copied 'n pasted this from a tutorial and then taken a stab at tweaking it for my own ends. But I really don't understand what it's doing.

Really, all I want to know is that it will remove records with duplicate email fields. But I could also do with confirming - looking at the "SELECT Min(ID)" bit - does that mean that if it finds a duplicate, it'll delete the latest-added one? And if so, that changing it to remove the earliest-added one is simply a case of changing MIN to MAX?

Thanks :)A good tip for keeping your sanity is to always run scripts like this on a testing version of your database and then confirm that it has worked before even contemplating running it on prod. As such - the below is based on my best reading of the script.

Yes it will work. Yes it will delete the most recently inserted record(s) (assuming that the ID field is a monotonically increasing value such as an identity and a higher number always indicates a more recently inserted record). And yes - you can change MIN to MAX to retain the most recently inserted record.

Have a read through the script a few times though - even if you don't consider it necesary it would be nice to know what it is doing and why.

HTH|||Yeah, a test run would be advisable. Good point. I've tried reading my way through it and I just get bogged down in "so we get one list that's... and those exist in... and that doesn't exist... and..." and the will to live rapidly leaves me. I think I get it now, though. I guess I just needed someone to tell me it did do what I thought before I tried to figure out exactly how.

Thanks for the help.|||While it lookes to me like the code you posted should work, I'd suggest a simpler approach. It is a lot easier to read (at least for me anyway), and probably easier to understand.

DELETE FROM tblContacts
WHERE ID <> (SELECT Max(z.ID)
FROM tblContacts AS z
WHERE z.email = tblContacts.email))-PatP|||Ooh, that's easier :D Nice one :beer:|||Oh yeah... One thing I ought to mention before you go trundling off, this code snippet will trip over any rows that have an ID column that is NULL. This may make you need to add an ID IS NOT NULL to the outer clause if there is any chance of encountering a legitimate NULL value in the ID column. This is unlikely, but some schemas will permit it, and the results can be catastrophic!

-PatP|||A handy trick I have used in such cases is rewrite the statement as a SELECT, rather than an update or delete. See what records you are about to modify/maim, and if you have no objections, then you can run the actual data modification.|||DELETE FROM tblContacts
WHERE ID <> (SELECT Max(z.ID)
FROM tblContacts AS z
WHERE z.email = tblContacts.email))
Actually - reading this more carefully, I'm a bit confused. It looks like it'll only do one at a time? Is that right? If so, that's not a bad thing - in fact, it'd be good to know that I've managed to guess what a statement will do before I run it :rolleyes: :D

edit:

Well... having read MCrowley's excellent advice: clearly it doesn't. It gives me a big list of duplicates. But I don't understand how? It selects MAX - which is only going to return one record, right? And then it selects (or deletes) WHERE ID <> - not "is not in [a range]", but "does not equal [a value]".

I'm confused again :(|||The key is in the corrolation ;)

DELETE FROM tblContacts
WHERE ID <> (SELECT Max(z.ID)
FROM tblContacts AS z
WHERE z.email = tblContacts.email))
If you remove the bit in bold then yes - you would get one ID returned. However the bit in bold corrolates the inner and outer query so there is one MAX(ID) returned per email address.

It can be rewritten as a select query as a join of two tables that might make it more obvious:


SELECT tblContacts.*
FROM tblContacts INNER JOIN
(SELECT Max(z.ID) AS TheMaxID,
email
FROM tblContacts
GROUP BY email) AS z ON
z.email = tblContacts.email
WHERE tblContacts.ID <> TheMaxID

HTH|||The more I think about this, the harder it gets :D I swear there's a SQL gene.

Anyway - thanks for the help and patience, everyone. I think I need to just go and play with these statements and get my head round them.|||Just practice - SQL is very easy to learn but rather tricky to master.

Run the inner query on its own:
SELECT Max(z.ID) AS TheMaxID,
email
FROM tblContacts
GROUP BY emailThat might illuminate...|||When you have a subquery (one query that is logically "nested inside" of another query), the subquery gets re-evaluated for each row returned by the outer query. If the DELETE query materializes a million rows from the tblContacts table, then the SELECT query would be evaluated a million times, once for each of the rows materialized by the DELETE query.

For each row in tblContacts, that DELETE checks to see if that row has the Max(ID) value for a given email address. If the row does not have the Max(ID) for that email, then the row is deleted.

While this will sometimes confuse people, it does not confuse SQL! ;) The only point that can confuse SQL is NULL values. A NULL email is simply ignored, considered junque and deleted (the explanation of that gets a bit tricky, just take it on faith for now). A NULL ID is also deleted, for a different (but similar) reason.

Once you understand the way this works for non-NULL values, we'll worry about the NULL values. They are almost assuredly garbage anyway, so don't burn much time on them yet.

-PatP

Can service broker process a email message

How do you set up the service broker to process an email message, and how do you format that message and send it to the que.

Can the service broker alos process an html form from a que.

Thanks

Service Broker can only be used for exchanging messages between two SQL Instances. You cannot send nor receive email messages directly to/from Service Broker.

Why do you need to send email messages to a queue?

HTH,
~ Remus

can send email using dbmail

Hi

I can't send email using sp_send_dbmail stored proc

when I execute this stored proc in msdb I face no error but executing it in other databases raises an error saying this stored proc does not exist.

when I executed this query

select * from sys.transmission_queue

I recived no result set.

please tell me what to do.

thanks

pooyan.

yes,

sp_send_dbmail should be run in msdb

Can report parameters be sent in subscription emails?

Hi all,
Is there any way to have the report parameters appear in the subscription
email, for both custom and default reports? Currently, the goal is to just
get the team project name to appear along with the sent report. The other
idea I can think of is to just put a field in the actual report that will
show the project name. I would rather not do this so that we don't have to
edit the existing rdl's (unless in the end, we absolutely have to). It
would also appear redundant when viewing the reports through Report Manager.
In addition to these, it is possible that people would request for other
parameters to be sent with the email, thus cluttering the actual report with
redundant information.
Can anyone help or offer alternatives, please? Thanks.Hello Winkles,
I would like to know this issue a little bit clearly.
Do you mean that you want to add the Report Parameter in the Subject of the
subscription?
Well, you could not refer the Report Parameter in the Subject.
I would like to suggest you to use the data driven subscription so that you
could store the subject in a data table and you could maintain it.
Data-Driven Subscriptions
http://msdn2.microsoft.com/en-us/library/ms159150.aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei,
We are trying to include, at the very least, the team project name in the
email somewhere. This is because some people may subscribe to the same
report across multiple team projects, and we need a way to indicate which
project the report belongs to.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:dllA96K$GHA.4432@.TK2MSFTNGXA01.phx.gbl...
> Hello Winkles,
> I would like to know this issue a little bit clearly.
> Do you mean that you want to add the Report Parameter in the Subject of
> the
> subscription?
> Well, you could not refer the Report Parameter in the Subject.
> I would like to suggest you to use the data driven subscription so that
> you
> could store the subject in a data table and you could maintain it.
> Data-Driven Subscriptions
> http://msdn2.microsoft.com/en-us/library/ms159150.aspx
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Winkles,
Thanks for the update.
When you try to create a subscription, you will need to provide which
parameter you will need to render the report.
According to your scenario, you could add a TextBox in the beginning of the
report and add the value =Parameters!Team.Value in the textbox. Then, when
you send the email, and provide the parameter value, it will show the value
in the report.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei,
Thanks for your response. So is that the only way to do it then? Adding a
textbox to the report? There is no way to either set some flag or add some
lines of code to get something like the Parameters selection box to be sent
with the subscription email, along with the options used to generate that
report?
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:a8xSHiY$GHA.4184@.TK2MSFTNGXA01.phx.gbl...
> Hello Winkles,
> Thanks for the update.
> When you try to create a subscription, you will need to provide which
> parameter you will need to render the report.
> According to your scenario, you could add a TextBox in the beginning of
> the
> report and add the value =Parameters!Team.Value in the textbox. Then, when
> you send the email, and provide the parameter value, it will show the
> value
> in the report.
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Just to clarify: will this change also be applied to people who subscribe to
their own reports, i.e. independent of a data-driven subscription?
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:a8xSHiY$GHA.4184@.TK2MSFTNGXA01.phx.gbl...
> Hello Winkles,
> Thanks for the update.
> When you try to create a subscription, you will need to provide which
> parameter you will need to render the report.
> According to your scenario, you could add a TextBox in the beginning of
> the
> report and add the value =Parameters!Team.Value in the textbox. Then, when
> you send the email, and provide the parameter value, it will show the
> value
> in the report.
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Winkles,
If you wants the user to be able to select the parameter in the email
subscription, that can not been done in the Reporting Services.
All the report render type need a parameter value so you could not just
provide the parameter select option in the output file.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

can not send an email using databasemail from asp.net page in a trigger

Hello,

I want to send an email from a trigger. I have configured all, and if I insert something into the table directly in the db the email is sent.

I give access to the asp.net account to msdb and put it into DatabaseMailUserRole group, but it doesnt work when I launch the trigger.

Any ideas?

What is the error you are getting?

Did you try inserting the row from Query window to check db mail working?

Check mail log for more info...

|||that is what i do not understand. It do not give me any error. If I insert from query window it works, but from asp.net fails. I think it is something related to the asp.net account, but i am using atlas and i can not see the error message. Nothing to see in the log, except a transacction exception, but I do not see that always, sometimes it writes it sometimes not.