SQL - ASPMailing - 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

Transact-SQL (2000)

SQL - ASPMailing

Author

Topic

Dito
Starting Member

1 Post

Posted - 2003-06-12 : 12:56:42

I'm trying to send email via ASPMail based on the result of this query:

USE Northwind
GO
DECLARE abc CURSOR FOR
SELECT * FROM Shippers
OPEN abc
WHILE (@@FETCH_STATUS = 0)

FETCH NEXT FROM abc
exec sp_SMTPMail @SenderName='Monitor', @SenderAddress='sender@abc.com',
@RecipientName = 'Someone', @RecipientAddress = 'recip@abc.com',
@Subject=CompanyName, @body=Phone
FETCH NEXT FROM abc
CLOSE abc
DEALLOCATE abc
GO

It sends only the first line that matches the query results. If the call to sp_SMTPMail is commented out, the query returns the correct results, 3 shippers.

mtomeo
Starting Member

30 Posts

Posted - 2003-06-12 : 15:14:40

Are you sure it returns the three shippers? You have two FETCH's inside your WHILE.
Move that first one outside the WHILE. Also, I'm not sure if it's necessary,
but you might want to add a BEGIN and END.


USE Northwind 
GO 
DECLARE abc CURSOR FOR 
SELECT * FROM Shippers 
OPEN abc 
FETCH NEXT FROM abc 
WHILE (@@FETCH_STATUS = 0) 

BEGIN
exec sp_SMTPMail @SenderName='Monitor', @SenderAddress='sender@abc.com', 
@RecipientName = 'Someone', @RecipientAddress = 'recip@abc.com', 
@Subject=CompanyName, @body=Phone 
FETCH NEXT FROM abc 
END

CLOSE abc 
DEALLOCATE abc 
GO

Of course, that probably doesn't solve your problem. I'm not familiar w/that stored proc,
but I thought I'd at least mention the problem(s) I saw.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources