SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 While loop for DBMail
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

spareus
Yak Posting Veteran

India
52 Posts

Posted - 05/20/2013 :  07:45:33  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 05/20/2013 :  07:50:07  Show Profile  Reply with Quote
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

Edited by - bandi on 05/20/2013 07:54:08
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000