I have a DTS package that I have written to has a SQL task that runs some
code simular to the following code. The problem is that I have been running
into is that when the cursor is going though the email addresses, sometimes
it will just error out. I put an audit insert statement on the script to
insert the email addresses into a table that it is currently sending to as
it goes through the cursor and the email address it errors out on is a valid
email. For some reason, it just truncates part of the email address out.
If the email address is j.smith (AT) mydomain (DOT) com it would just truncate the
j.smith@my part of and try to send an email to domain.com instead. Another
thing that concerns me is why would it stop the cursor? Shouldn't a cursor
continue even though there was an error and go to the next email? I am
wondering if this is a problem with the way DTS handles the script. I can
take the same exact script and run it through a query analyzer window and it
will run just fine every single time. Has anyone ran into this problem
before with DTS and the xp_smtp_sendmail procedure? Thanks for any help.
The script is below. This is running on a Windows 2000/SQL Server 2000
Enterprise Sp 3a with MS03-031 Cumulative patch installed.

declare @emailcmd varchar(8000)
declare @emailaddy varchar(100)
declare @batchcounter int
declare @sub varchar(150)
declare @msg varchar(5000)

set @sub = 'This is my subject'
set @msg = 'This is my message'

declare mycur cursor for select distinct email from employee where email is
not null

open mycur

fetch next from mycur into @emailaddy

while @@fetch_status = 0
begin

set @emailcmd = 'execute master..xp_smtp_sendmail @server =
''smtp.mydomain.com'', '
set @emailcmd = @emailcmd + '@from = ''sql (AT) mydomain (DOT) com'''
set @emailcmd = @emailcmd + ', @to = ''' + @emailaddy + ''', @subject='''
+ @sub + ''', '
set @emailcmd = @emailcmd + '@message=''' + @msg + ''',
@type=''text/html'''
exec (@emailcmd)

fetch next from mycur into @emailaddy

end

close mycur
deallocate mycur

GO

Hi Brian,

From your descriptions, I understood that you meet the error with
xp_smtp_sendmail in DTS. Email domain will be truncated and cursor could be
only used once. Have I understood you? If there is anything I
misunderstood, please feel free to let me know

Unfortunately, xp_smtp_sendmail is not an offical stored procedures so that
it is not supported. Additional information about xp_smtp_sendmail I could
provide personally is it's home site:

XPSMTP.DLL - SQL Server SMTP Mail XP
http://www.sqldev.net/xp/xpsmtp.htm

Secondly, I make a small sample to see whether it will be truncated or used
only once using Cursor by following T-SQL in Query Analyzer
----Create Database
create table employee
(email varchar(80))
----Insert Sample Values
insert employee values ('aaa (AT) microsoft (DOT) com')
insert employee values ('bbb (AT) microsoft (DOT) com')
insert employee values ('ccc (AT) microsoft (DOT) com')
----use the Cursor
declare @emailaddy varchar(100)
declare mycur cursor for select distinct email from employee where email is
not null
open mycur
fetch next from mycur into @emailaddy
while @@fetch_status = 0
begin
select @emailaddy
fetch next from mycur into @emailaddy
end
close mycur
deallocate mycur
-----End of T-SQL

It works well on my machine that all email will be listed seperatly and no
truncated.

Anyway, would you please use xp_sendmail and then try to see whether it
will works fine? would you plesae show me what's the detailed error message
information SQL Server gives?

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!

Sincerely yours,

Mingqing Cheng
Microsoft Online Support
------------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Brian,
You do not have to create a dynamic string look at the following example:

if (object_id('smtp_sendmail') is not null and
objectproperty(object_id('smtp_sendmail'), 'IsUserTable') = 1)
drop table [dbo].[smtp_sendmail]
go

create table [dbo].[smtp_sendmail]
(
[to] nvarchar(4000) not null,
[replyto] nvarchar(4000) null,
[cc] nvarchar(4000) null,
[bcc] nvarchar(4000) null,
[priority] nvarchar(10) not null default N'normal',
[subject] nvarchar(4000) null,
[message] nvarchar(4000) null,
[type] nvarchar(20) not null default N'text/plain'
)
go

insert into [dbo].[smtp_sendmail] values(N'GertD (AT) SQLDev (DOT) Net',
N'GertD (AT) SQLDev (DOT) Net', NULL, NULL, N'high', N'Subject 1', N'Message text 1',
N'text/html')
insert into [dbo].[smtp_sendmail] values(N'GertD (AT) SQLDev (DOT) Net',
N'GertD (AT) SQLDev (DOT) Net', NULL, NULL, N'high', N'Subject 2', N'Message text 2',
N'text/html')
go

declare @to nvarchar(4000),
@replyto nvarchar(4000),
@cc nvarchar(4000),
@bcc nvarchar(4000),
@priority nvarchar(10),
@subject nvarchar(4000),
@message nvarchar(4000),
@type nvarchar(20),
@rc int

declare c1 cursor read_only for
select [to],
[replyto],
[cc],
[bcc],
[priority],
[subject],
[message],
[type]
from [dbo].[smtp_sendmail]

open c1

fetch next from c1 into @to, @replyto, @cc, @bcc, @priority, @subject,
@message, @type
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'XPSMTP (AT) SQLDev (DOT) Net',
@FROM_NAME = N'XPSMTP',
@TO = @to,
@replyto = @replyto,
@CC = @cc,
@BCC = @bcc,
@priority = @priority,
@subject = @subject,
@message = @message,
@type = @type,
@server = N'mail.sqldev.net',
@port = 25

if (@rc <> 0)
begin
raiserror('Error sending mail', 16, 1)
end

end
fetch next from c1 into @to, @replyto, @cc, @bcc, @priority, @subject,
@message, @type
end

close c1
deallocate c1
go



GertD (AT) SQLDev (DOT) Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.

"Brian Nall" <nospam (AT) forme (DOT) com> wrote

Dear Mingqing Cheng,

I do not expect Microsoft to support my XP's, but you make it sound like
this is an evil thing to use. Your statement should be that you can not
support a third party product, but it is totally allowed to do so and that
they should contact the third party instead.

If SQL Mail was that good there would not be a need for XPSMTP mail.

Thanks,
GertD (AT) SQLDev (DOT) Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.

""Mingqing Cheng [MSFT]"" <v-mingqc (AT) online (DOT) microsoft.com> wrote

Dear GertD,

I truly apologized for this if there is anything in my words leading to
that kind of misunderstanding. What I mean is just that we will not support
xp_smtp_sendmail as you have said.

Anyway, thanks for your updates and sorry for that communication failure
again


Sincerely yours,

Mingqing Cheng
Microsoft Online Support
------------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Thanks much,

I did however find out what was erroring out. There was an invalid email
address but it was just kind of hard to see among the hundreds that are in
the table. The email that knocked it for a loop was formated like this:

joe.smith. (AT) mydomain (DOT) com

It looks like a normal email but notice the period next to the @ sign. When
I came across that email. This is the error it threw out:

Error: sending message
Server response: 501 5.5.4 Invalid Address
n.com>

Anyways. What still concerns me is the way the cursor acts in the Execute
SQL Task in DTS. If I ran the script in a normal query analyzer window, it
would continue without bombing the cursor out. But the same script would
totally fail the Execute SQL Task in DTS.

Anyways, Thanks for the help Gert


"Gert E.R. Drapers" <GertD (AT) Online (DOT) SQLDev.Net> wrote

Here's an unsolicited but potentially valuable bit of advice regarding
xp_sendmail, and variants:
Consider putting a "wrapper" around such procedures, because email
connectivity may change/break without warning in this volatile security
environment. Corporate security standards can change with little or no
warning. With a "wrapper" procedure around all of your email calls,
you'll have alternatives, like redirecting the email output to tables,
for later processing, perhaps by an entirely different system. Or if the
messages are not critical to your system, you may include an option to
make "success" entirely optional, and merely log any failures.

Be sure when using xp_sendmail and variants, that you provide error
handling so that errors in xp_sendmail do not interrupt critical
processes. Consider adding a variable sp_my_sendmail proc to let it know
that it should skip the message altogether, or redirect all messages to
tables. This will give you a "single point" of emergency repair/kludge
that will alloy you to get your system working, without email, should
server be out of service or unreachable.

If replying to my email, clean up trash.


Brian Nall wrote: