cursor or while loop?

hello,
i have 160,000 rows in a table. need to send out an email to somebody
for each row.
currently using a cursor to loop through the rows and calling COM email
component to send the emails
ran low on resources after about 100,000 records
would it be better to do something like this?
DECLARE @.Rowcount int
SELECT @.RowCount=COUNT(*) from table
WHILE @.RowCount > 0
BEGIN
SELECT TOP 1 @.somevar=somevar FROM table
SET @.RowCount = @.@.ROWCOUNT
IF @.Rowcount > 0
BEGIN
send the email
delete the record
END
END
this would only grab one record at a time. any recommendations?
TIA
NeilPass the result set to the front end and do not try to do this in the
database.|||Check out http://www.sqldev.net there is a xp_smtp_sendmail which you can
use to send your mail.
You will need to use a cursor still because there isn't anything that takes
a set.
I'd create a temporary table or table variable of the rows you want to email
first, then send the emails and join back removing the rows; you might want
recoverability in which case use a permenent table or something similar -
you don't want to lose the position if the server goes down.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<neilmcguigan@.gmail.com> wrote in message
news:1131321608.259481.84490@.g49g2000cwa.googlegroups.com...
> hello,
> i have 160,000 rows in a table. need to send out an email to somebody
> for each row.
> currently using a cursor to loop through the rows and calling COM email
> component to send the emails
> ran low on resources after about 100,000 records
> would it be better to do something like this?
> DECLARE @.Rowcount int
> SELECT @.RowCount=COUNT(*) from table
> WHILE @.RowCount > 0
> BEGIN
> SELECT TOP 1 @.somevar=somevar FROM table
> SET @.RowCount = @.@.ROWCOUNT
> IF @.Rowcount > 0
> BEGIN
> send the email
> delete the record
> END
> END
> this would only grab one record at a time. any recommendations?
> TIA
> Neil
>|||That just won't scale.
The number of round trips 160,000 would be even slower.
This is one of many examples where keeping it inside the SQL Server engine
scales better and is more maintable (you don't have to code using two
different langauges and worry about a front end app).
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1131322861.083235.253060@.g43g2000cwa.googlegroups.com...
> Pass the result set to the front end and do not try to do this in the
> database.
>|||I really had a lot of reliability problems with sql server mail.
I just don't trust mapi based mail anymore.
xp_smtp_sendmail is dead reliable. I pump huge amounts through it everyday.
What are we going to do with Yukon?
Tony, do you know if xp's written for 2000 will work with 2005 or is the
native mail OK in 2005?
Paul
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:ekRlvE34FHA.2548@.TK2MSFTNGP10.phx.gbl...
> Check out http://www.sqldev.net there is a xp_smtp_sendmail which you can
> use to send your mail.
> You will need to use a cursor still because there isn't anything that
> takes a set.
> I'd create a temporary table or table variable of the rows you want to
> email first, then send the emails and join back removing the rows; you
> might want recoverability in which case use a permenent table or something
> similar - you don't want to lose the position if the server goes down.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> <neilmcguigan@.gmail.com> wrote in message
> news:1131321608.259481.84490@.g49g2000cwa.googlegroups.com...
>|||Hi Paul,
Believe me - they got the message with MAPI mail!
Its been rewritten (again), but this time properly - MAPI is only there for
backward compatibility, there is a new SMTP mail built into SQL Server 2005
and its highly scalable too (at last!).
I will be glad to see the back of Outlook 2000 and SQL Server.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:OEEASy34FHA.1140@.tk2msftngp13.phx.gbl...
>I really had a lot of reliability problems with sql server mail.
> I just don't trust mapi based mail anymore.
> xp_smtp_sendmail is dead reliable. I pump huge amounts through it
> everyday.
> What are we going to do with Yukon?
> Tony, do you know if xp's written for 2000 will work with 2005 or is the
> native mail OK in 2005?
> Paul
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:ekRlvE34FHA.2548@.TK2MSFTNGP10.phx.gbl...
>|||Additional...
You've really got to get your head round what the SQL Server product is now,
its not just a RDBMS its a whole host of things that we can take advantage
of when building applications, be they simple or complex.
You don't have to go off to DB2 etc... in order to build a dimensional
model, you can use Analysis Services which is part of the SQL Server product
set and has been since 2000.
You don't have to go off and buy another text search package, you can use
Full-Text indexing which is built into the product as well.
Please (oh please), bring your knowledge up-to-date; you are so far behind
its embarrasing.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1131322861.083235.253060@.g43g2000cwa.googlegroups.com...
> Pass the result set to the front end and do not try to do this in the
> database.
>|||I'm going to have to kindof disagree with you on this one. Making a
round-trip on each result would be a horrendous solution; however,
returing a full batch 160,000 records to the client wouldn't be bad at
all (even if the client were the SQL Server machine.). I consider my
databases to be dynamic entities whose purpose is efficient storage,
retrieval and manipulation of data. I wouldn't dream of calling a COM
component from SQL Server; even the idea of embedding .NET assemblies
in 2005 scares me a bit.
The two solutions I would consider using would be embedded an ActiveX
script in a SQL Server Agent job step. I prefer Python, however,
VBScript and JScript are readily available for use. Or you can easily
throw together a quick .NET console app.
EXAMPLE (Compile using: csc /t:exe SendEMails.cs):
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Mail;
class SendEmails
{
static void Main(string[] args)
{
SqlDataAdapter da = new SqlDataAdapter(@."
SELECT EmailAddress
FROM TableName
", "server=.;database=MyDatabase;integrated security=sspi;");
DataTable dt = new DataTable();
da.Fill(dt);
SmtpMail.SmtpServer =
"smtp.domain.com";
foreach (DataRow dr in dt.Rows)
SmtpMail.Send("from@.domain.com", dr["EmailAddress"].ToString(),
"Subject", "MessageText");
}
}
-Alan|||Thanks Tony that's good news.
Now I've only got to remember how those DTS tasks I wrote last year work :)
Paul
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:O8r7xG44FHA.2888@.tk2msftngp13.phx.gbl...
> Hi Paul,
> Believe me - they got the message with MAPI mail!
> Its been rewritten (again), but this time properly - MAPI is only there
> for backward compatibility, there is a new SMTP mail built into SQL Server
> 2005 and its highly scalable too (at last!).
> I will be glad to see the back of Outlook 2000 and SQL Server.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:OEEASy34FHA.1140@.tk2msftngp13.phx.gbl...
>|||thanks for all the info guys.
i need something that can run continuously, reliably, minimal resouce
use
thinking i might go to an external program, write a .net service
perhaps.
maybe grab a couple hundred records at a time, process them, update.
i liked the all sql model as it didn't seem to use too many resources.
but i like the error handing in .net better.