"Gert E.R. Drapers" <GertD@Online.SQLDev.Net> wrote in message
news:eyKU$LTTEHA.1048@tk2msftngp13.phx.gbl...
> 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@SQLDev.Net',
> N'GertD@SQLDev.Net', NULL, NULL, N'high', N'Subject 1', N'Message text 1',
> N'text/html')
> insert into [dbo].[smtp_sendmail] values(N'GertD@SQLDev.Net',
> N'GertD@SQLDev.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@SQLDev.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@SQLDev.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@forme.com> wrote in message
> news:OD7z43LTEHA.2372@TK2MSFTNGP11.phx.gbl...
> >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@mydomain.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@mydomain.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
> >
> >
>
>

"Brian Nall" <nospam@forme.com> wrote in message
news:OD7z43LTEHA.2372@TK2MSFTNGP11.phx.gbl...
>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@mydomain.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@mydomain.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
>
>

""Mingqing Cheng [MSFT]"" <v-mingqc@online.microsoft.com> wrote in message
news:px45KkSTEHA.2160@cpmsftngxa10.phx.gbl...
> 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@microsoft.com')
> insert employee values ('bbb@microsoft.com')
> insert employee values ('ccc@microsoft.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 Nall wrote:

> 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@mydomain.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@mydomain.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
>