skipping errors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

skipping errors

Author

Topic

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-12-31 : 11:54:14

i have a job that sends out emails. if even one email gets passed my email validation script then the job fails. how can i modify my script so that if one email sent out fails it will just move to the next one without failing? below is my script:

declare @vcEmailBody varchar(8000),@ResponseCode int,@vcEmailTitle varchar(100),@vcEmailName varchar(50),@vcEmailFileName varchar(250)

declare @intDaysOut int
set @intDaysOut = 5

DECLARE @vcEmail varchar(100)
DECLARE cursorDeferral CURSOR FOR

select vcemail from table

OPEN cursorDeferral
FETCH cursorDeferral INTO @vcEmail

WHILE @@FETCH_STATUS = 0
BEGIN

--send email
EXEC @ResponseCode = master..xp_smtp_sendmail
@FROM = 'email@email.com',
@FROM_NAME = 'Customer Service',
@TO = @vcEmail,
@subject = 'test',
@type = 'text/html',
@message = 'test',
@server = 'serveraddress'

FETCH cursorDeferral INTO @vcEmail
END

CLOSE cursorDeferral
DEALLOCATE cursorDeferral

jhermiz

3564 Posts

Posted - 2004-12-31 : 12:40:36

Why are you using cursors for such a thing ?

Wouldnt it be easier to simply SELECT your records perform any modifications or updates you need to to format the email and use xp_sendmail to send the emails? Whether or not the email makes it or not wont matter, you can wrap this around a WHILE loop and go until you've reached the last record.

You can get the last record ID if you need to do some loop based on a count by using DESC in the order by clause.

Jon

A new beat on the web -- http://www.web-impulse.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources