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 2005 Forums
 Transact-SQL (2005)
 While loop for DBMail

Author  Topic 

spareus
Yak Posting Veteran

52 Posts

Posted - 2013-05-20 : 07:45:33
I am trying to send mail to select users with following script.
declare @batch nvarchar(255)
declare @body nvarchar(255)

select @batch = min(Batch) from type

while @batch is not null
begin
set @body=@batch
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Mail',
@body = @body,
@body_format ='HTML',
@recipients = 'mumse@elianmail.com',
@subject = 'E-mail to customers' ;
select @batch = min(Batch) from [type] where Batch > @batch
end

It works fine but sends mail with one col only.

If I change the select statement as below,
SET @BODY = SELECT *
FROM dbo.TYPE
WHERE (Batch =
(SELECT MIN(Batch)
FROM dbo.TYPE))

I am getting error "Incorrect syntax near the keyword 'SELECT'."

I need to send the order details of customers on their mails from SQL.
Using SQL2012.

Pl help.

Regards,
spareus.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-20 : 07:50:07
Refer the following link (See Section D & E)
http://msdn.microsoft.com/en-us/library/ms189505(v=sql.105).aspx

EDIT:
Check "Send E-Mail from Database Mail" in http://www.idevelopment.info/data/SQLServer/DBA_tips/Database_Administration/DBA_20.shtml

--
Chandu
Go to Top of Page
   

- Advertisement -