While loop for DBMail - 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 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources