Error catch in SQL

[Mark]

Hi everyone, I am using an SQL extended stored procedure to send emails in a
DTS package using a cursor that goes through each row in a table.

Email sending code below
=============== =======
exec master.dbo.xp_s mtp_sendmail
@FROM = @sFrom,
@FROM_NAME = @sFrom,
@TO = @sRecepients,
@subject = @sSubject,
@message = @sBody,
@type = N'text/html',
@codepage = 0,
@server =N'MYMAILSERVER '
=============== =======
Fetch Next From EmailCursor ...

Now the problem I have is that if an individual email address in invalid
then an error occurs and the whole DTS package falls over. What I would like
to be able to do is "catch the error", something like this (C# code used as
example)

try
{
exec master.dbo.xp_s mtp_sendmail
@FROM = @sFrom,
@FROM_NAME = @sFrom,
@TO = @sRecepients,
@subject = @sSubject,
@message = @sBody,
@type = N'text/html',
@codepage = 0,
@server =N'MYMAILSERVER '
} catch {
exec master.dbo.xp_s mtp_sendmail
@FROM = "ar**********@m ybusiness.com",
@FROM_NAME = @sFrom,
@TO = @sRecepients,
@subject = @sSubject,
@message = @sBody,
@type = N'text/html',
@codepage = 0,
@server =N'MYMAILSERVER '
}

Is this possible??? Normally I would do all the email validation before the
email is entered into the database but unfortunately, I do not have access
to the application code so I am stuck doing it this way.

Thanks in advance
Mark

[Erland Sommarskog]

Mark (ma*******@n0Sp 8mTAIRAWHITIdot AC.NZ) writes:

Now the problem I have is that if an individual email address in invalid
then an error occurs and the whole DTS package falls over. What I would
like to be able to do is "catch the error", something like this (C# code
used as example)

In T-SQL there is no way to suppress the error. (In SQL2000, that is. The
coming version SQL2005 has the TRY CATCH you are looking for.

Presumably you should be able to have the DTS package to swallow the
error, but I don't know DTS so I can't give any details. The nice and
friendly people in microsoft.publi c.sqlserver.dts may have some ideas.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Mark]

Thanks Erland
Regards
Mark
"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...

Mark (ma*******@n0Sp 8mTAIRAWHITIdot AC.NZ) writes:

Now the problem I have is that if an individual email address in invalid
then an error occurs and the whole DTS package falls over. What I would
like to be able to do is "catch the error", something like this (C# code
used as example)

In T-SQL there is no way to suppress the error. (In SQL2000, that is. The
coming version SQL2005 has the TRY CATCH you are looking for.

Presumably you should be able to have the DTS package to swallow the
error, but I don't know DTS so I can't give any details. The nice and
friendly people in microsoft.publi c.sqlserver.dts may have some ideas.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp