Query to extract number of duplicates

This query is driving me insane.
I need to extract the number of rows in tblUsers where the first 3
characters of "fldSurname" are the same and they where born "fldDOB" on the
same year and month.
Can anyone help ?Poppy,
Try this link from MS!
I had to use it the other w to remove 150,000 duplicate rows from a live
database and it worked a treat.
You need to modify the SQL slightly to accomodate your data, but the logic
is good and should help you out.
Thanks Microsoft! :)
Immy
"poppy" <poppy@.discussions.microsoft.com> wrote in message
news:F7BE4539-D96B-4FDF-AD48-188649EC4CF9@.microsoft.com...
> This query is driving me insane.
> I need to extract the number of rows in tblUsers where the first 3
> characters of "fldSurname" are the same and they where born "fldDOB" on
> the
> same year and month.
> Can anyone help ?|||if all you need is the count:
select left(fldSurname,3) as surname_start, fldDOB, count(*) as rows
from tblUsers
group by left(fldSurname,3), fldDOB
poppy wrote:
> This query is driving me insane.
> I need to extract the number of rows in tblUsers where the first 3
> characters of "fldSurname" are the same and they where born "fldDOB" on th
e
> same year and month.
> Can anyone help ?|||What link from MS '
"Immy" wrote:

> Poppy,
> Try this link from MS!
> I had to use it the other w to remove 150,000 duplicate rows from a liv
e
> database and it worked a treat.
> You need to modify the SQL slightly to accomodate your data, but the logi
c
> is good and should help you out.
> Thanks Microsoft! :)
> Immy
> "poppy" <poppy@.discussions.microsoft.com> wrote in message
> news:F7BE4539-D96B-4FDF-AD48-188649EC4CF9@.microsoft.com...
>
>|||DOH!!!! God damn CTRL+C! :)
Here it is...
Just note that this will help to remove the records also...
If you want to just see how many you have then a simple count as suggested
by Trey will show the results.
http://support.microsoft.com/defaul...kb;en-us;139444
Regards
Immy
"poppy" <poppy@.discussions.microsoft.com> wrote in message
news:AFCD89C6-2862-4304-B554-0581E085E78C@.microsoft.com...
> What link from MS '
> "Immy" wrote:
>

Query taking ages for no apparent reason

Hope someone can help me with this because its driving me potty!

I have a .NET script that sends really simple queries to SQL server that works perfectly 50% of the time but for the other 50% it takes ages (2-3 minutes) and then fails, I'm assuming because it times out. I then check the SQL by excecuting it via query analyzer and it again takes ages but will work eventually (I'm assuming because this bypasses the timeout settings, but changing these isn't on).

This happens randomly, the scripts will be working fine and then fail a few times before magically working again!

Any ideas? Perhaps some database features that commonly cause this problem? The problem only occurs with one database, all our others are fine but we can't spot any differences!

Any help or tips would really be appreciated.

Thanks.sound like a locking issue.

When you are running the query via .net, in query analyzer in a seperate session run sp_who2. This will show you if there are any locked processes.

Even better use enterprise manager (if you have access)|||Originally posted by dbabren
sound like a locking issue.

When you are running the query via .net, in query analyzer in a seperate session run sp_who2. This will show you if there are any locked processes.

Even better use enterprise manager (if you have access)

Thanks for the advice.

There's no sign of locking when my problem is occuring using sp_who2 (I refreshed sp_who2 a few times whilst I was waiting for the query to give-up).

On the other hand, I had a look using enterprise manager->Locks/Object and there's a huge list of Table Locks (908!) owned by 'xact' (a transaction? )for the database i'm using. Other db's being used have database locks owned by the SESS (session I assume). I've never explicitly asked for a lock, but this db is someone else's so could there be somehting in there that aquires a lock?

Thanks for you help,

suddy.|||Suddy

Everytime you access the db, you will take a lock - the type and severity of that lock depends on what you are doing - have a look at locking in BOL (it can explain it better ..)

I find it easier to use locks/process id in Ent Manager as it is often easier to track the spid to a particular PC/trnsaction. It also tell you which process is blocking which other processes.

Another option may be to use profiler to track the SQL that is being ran, and capture blocking lock information - but this will have a performance impact itself (so be weary of it)|||Thanks for your help Dbabren.

Darned problem has mysteriously vanished this morning but I'm going to go away and have a look at BOL because this is bound to come back if I don't work out what's going on.

Thanks again.|||Originally posted by suddy
Hope someone can help me with this because its driving me potty!

I have a .NET script that sends really simple queries to SQL server that works perfectly 50% of the time but for the other 50% it takes ages (2-
Thanks.

Can you post the queries? Are you using the "NOLOCK" directive with your select statements?