For all the rows in this recordset

Select emailAddress, customerNum from customer

I would like to call this stored procedure

EXEC sp_SMTPemail 'from (AT) address (DOT) com', emailAddress, 'Subjecttext','Messagetext - Your CustomerNumber is' + customerNu

Can I do this in DTS? Or can this been done in the stored procedure? I want to call the stored procedured for every row returned from the sql statement either in the stored procedure or in a DTS package. Thanks for your help

You could either

1. Use a cursor
2. Use a DataPump task that actually pumped to nothing and simply called a
lookup everytime. (no different really to solution #1)



--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"kdabda" <anonymous (AT) discussions (DOT) microsoft.com> wrote

statement either in the stored procedure or in a DTS package. Thanks for
your help.

Thanks for the note Alan. Below is a store procedure with a cursor to call my stored procedure to send email How can I add text plus the 'variable' of data I get from my cursor? Also I get an error about the bottom of this stored procedure, Incorrect syntax near @Mycursor. Thanks,

CREATE PROCEDURE sp_CursorToSendMail
as

DECLARE @Store nvarchar(10)
DECLARE @Email_Recipient nvarchar(30)
DECLARE @Submission datetime(8)
DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT DISTINCT tblItemOrder.Store, cp_RM00106.Email_Recipient, tblItemOrder.Submission
FROM tblItemOrder INNER JOIN
cp_RM00106 ON tblItemOrder.Store = cp_RM00106.CUSTNMBR

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @Store, @Email_Recipient, @Submission

WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
EXEC xp_cdosendmail
@Address = @Email_Recipient,
@message = 'Your order was received for ' + @Store + ' on ' + @Submission + '. Any questions please call at 608',
@Subject = 'Order received for for ' + @Store + ' on ' + @Submission,
@From = 'khofn (AT) hvees (DOT) com'
FETCH NEXT FROM @MyCursor
INTO @Store, @Email_Recipient, @Submission
END

CLOSE @MyCursor
DEALLOCATE @MyCursor
GO

OK I would use the DECLARE CURSOR variation and also I would build the
strings outside of the assignments so

DECLARE @strMessage varchar(255)
SET @strMessage = 'Your order was received for ' + @Store + ' on ' +
@Submission + '. Any questions please call at 608'

....
...
@message = @strMessage



--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"kdabda" <anonymous (AT) discussions (DOT) microsoft.com> wrote

'variable' of data I get from my cursor? Also I get an error about the
bottom of this stored procedure, Incorrect syntax near @Mycursor. Thanks,