database mail to send mail to multiple recipient from table

I am using database mail to send emails to our Lotus Notes SMTP server using sp_send_dbmail. I want to accomplish the following.

I have maintained department-wise users email address in one table . Now I want to send mail to one particular department and there can be 1-15 users as recipient for that mail. How can I do that using sp_send_dbmail?

Well, I have found answer to it. The following way, we can accomplish. Hope that will help those, who are searching for something similar.

DECLARE @.email VARCHAR(4000)
SET @.email = ''
SELECT @.email = RTRIM(@.email) + RTRIM(email) + ';'
FROM Users
WHERE email <> '' AND DepCode = 'A'
PRINT @.email

EXECUTE msdb.dbo.sysmail_add_account_sp
@.account_name = 'custoerders',
@.description = 'Customer Address Account',
@.email_address = @.email
@.mailserver_name = 'mail.anywhere.com'

database mail to send mail to multiple recipient from table

I am using database mail to send emails to our Lotus Notes SMTP server using sp_send_dbmail. I want to accomplish the following.

I have maintained department-wise users email address in one table . Now I want to send mail to one particular department and there can be 1-15 users as recipient for that mail. How can I do that using sp_send_dbmail?

Well, I have found answer to it. The following way, we can accomplish. Hope that will help those, who are searching for something similar.

DECLARE @.email VARCHAR(4000)
SET @.email = ''
SELECT @.email = RTRIM(@.email) + RTRIM(email) + ';'
FROM Users
WHERE email <> '' AND DepCode = 'A'
PRINT @.email

EXECUTE msdb.dbo.sysmail_add_account_sp
@.account_name = 'custoerders',
@.description = 'Customer Address Account',
@.email_address = @.email
@.mailserver_name = 'mail.anywhere.com'

Database mail not sending

I am using the sp_send_dbmail sp and all appears to go well with that. But SS does not seem to be attempting to send the mail.

However when I look at the entries in sysmail_mailitems the sent status is zero. There are no entries in sysmail_log.

I configured Database Mail via the wizard. I am using the Sept CTP.

What could be the problem?I do have the following information in my Sql Alert log:
Unable to start mail session [reason: No Mail Profile defined]

However there are two mail methods defined in 2005, Sql Mail and Database Mail. I have defined a default mail profile in Database Mail. I have define and test one in Sql Mail and it says it works.

Database Mail and Linked Servers

We have a stored procedure that executes sp_send_dbmail with an @.query parameter. The @. query executes a stored procedures that has a linked server query in it. The linked server is SQL 2000. The login/user used by the link is valid and running the sproc by itself will return the expected results but it fails with the following error if run by the sp_send_dbmail sproc:

Warning: Null value is eliminated by an aggregate or other SET operation.

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476

Query execution failed: OLE DB provider "SQLNCLI" for linked server "Link_Server" returned message "Communication link failure".

HResult 0x2746, Level 16, State 1

TCP Provider: An existing connection was forcibly closed by the remote host.

HResult 0x4818, Level 14, State 1

Login failed for user 'EPC'.

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

Any help will be greatly appreciated.

What selection are you using under the security option for the Linked Server setup in SQL Server Management?|||use logins' current security conttext. The user id and password are the same on both servers.|||

I started to get the same message as you, the first part that is: (I'm not using linked servers). So I don't know if this will help you or not.

Warning: Null value is eliminated by an aggregate or other SET operation.

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476

This site was helpful to me.

http://forums.databasejournal.com/archive/index.php/t-42981.html

Apparently, the problem is with the SET QUOTED_IDENTIFIER. I applied his logic to my script and now it seems to be working. I also needed to stop and start dbmail to release the queue. I don't know why but it gets clogged. I have begun to put the stop and start near the beginning of all my scripts.

--These two lines will release any emails "clogged" in SS queue

EXEC msdb.dbo.sysmail_stop_sp;

EXEC msdb.dbo.sysmail_start_sp;

--new logic begin

SETQUOTED_IDENTIFIEROFF

set @.myqry ='SET QUOTED_IDENTIFIER ON '

set @.myqry = @.myqry +char(13)

--new logic end

set @.myqry = @.myqry

+'

SELECT * FROM PPI_Reporting.DBRS.HpSyncCompareInvDate'

EXECUTE msdb.dbo.sp_send_dbmail @.profile_name=@.profile, @.recipients=@.recipients, @.importance='Normal'

,@.subject=@.subject, @.body=@.body

,@.query=@.myqry

GO

|||I don't know how it affects the login failure but it is a linked server afterall The sproc currently set nocount and ansi_warnings so we'll give quoted_identifiers a try. Thanks for the suggestion.|||

I'm sorry, but my solution only worked for about 10 times yesterday and then no longer works. I was excited to see it work after adding the SET QUOTED_IDENTIFIER statement so I posted here.

So I am back to square one. Let us know if you find a solution.

-Doug

|||See this http://sqlserver-qa.net/blogs/tools/archive/2007/04/20/tcp-provider-an-existing-connection-was-forcibly-closed-by-the-remote-host.aspx is any help.|||I appreciate the responses but none of them quite fit the issue. We found a workaround and have the sprocs running now. Rather than returning the output of the called sproc to the calling sproc, the data is inserted in a table and the calling sproc now queries the table.

Database Mail

I am using the sp_send_dbmail for my mailing activity for my client
applications. I have created a profile for the SP and assigned my name
and e-mail address. but when any of my users send a mail from the
client application the display name of the mail is my name and not the
person who actually sends the mail.
Is it possible to set the display name dynamically? or anyother
workaround ?
Any help would be appreciated
ThanxsThe only way I could think of is to setup several mail profiles and have eac
h user use a different
mail profile. Or, use xp_smtp_sendmail. Also, consider wishing this feature
at
connect.microsoft.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<brinda.shree@.gmail.com> wrote in message
news:1173789930.776042.169600@.c51g2000cwc.googlegroups.com...
>I am using the sp_send_dbmail for my mailing activity for my client
> applications. I have created a profile for the SP and assigned my name
> and e-mail address. but when any of my users send a mail from the
> client application the display name of the mail is my name and not the
> person who actually sends the mail.
> Is it possible to set the display name dynamically? or anyother
> workaround ?
> Any help would be appreciated
> Thanxs
>

Database Mail

MSSQL 2005 9.00.1399.06
Win2003 server
I have configured database Mail, and tried the Sent Test E-Mail
and it's working, sp_send_dbmail is working too
When I do some testing on jobs and Notification/Operators on a specific
job I get theese message in the error log:
Date 05.05.2006 12:20:00
Log SQL Agent (Current - 05.05.2006 12:20:00)
Message
[264] An attempt was made to send an email when no email session has bee
n
established
and in the job history I get theese error:
Date 05.05.2006 12:30:00
Log Job History (Replication agents checkup)
Step ID 0
Server RDASP21
Job Name Replication agents checkup
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job succeeded. The Job was invoked by Schedule 2 (Replication agent
schedule.). The last step to run was step 1 (Run agent.). NOTE: Failed to
notify 'Roger Nygrd' via email.
What is wrong in my setup?
-Roger NygrdThis should be fixed if you update to SP1. At least it was for me.
"Roger Nygrd" <roger@.askit.no> wrote in message
news:125maitjqfkc493@.corp.supernews.com...
> MSSQL 2005 9.00.1399.06
> Win2003 server
> I have configured database Mail, and tried the Sent Test E-Mail
> and it's working, sp_send_dbmail is working too
> When I do some testing on jobs and Notification/Operators on a specific
> job I get theese message in the error log:
> Date 05.05.2006 12:20:00
> Log SQL Agent (Current - 05.05.2006 12:20:00)
> Message
> [264] An attempt was made to send an email when no email session has b
een
> established
>
> and in the job history I get theese error:
> Date 05.05.2006 12:30:00
> Log Job History (Replication agents checkup)
> Step ID 0
> Server RDASP21
> Job Name Replication agents checkup
> Step Name (Job outcome)
> Duration 00:00:00
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job succeeded. The Job was invoked by Schedule 2 (Replication agent
> schedule.). The last step to run was step 1 (Run agent.). NOTE: Failed
> to notify 'Roger Nygrd' via email.
> What is wrong in my setup?
> -Roger Nygrd
>|||I have applied the SP1 to MSSQL2005(Microsoft SQL Server 2005 - 9.00.2047.00
(Intel X86)) but the problem still persists. Any idea? Thanks in advnace!
"Michael D'Angelo" wrote:

> This should be fixed if you update to SP1. At least it was for me.
> "Roger Nyg?rd" <roger@.askit.no> wrote in message
> news:125maitjqfkc493@.corp.supernews.com...
>
>|||I forgot to restart the SQLAgent after configuring 'Alert System'. Thanks a
ll
"stephanie" wrote:
[vbcol=seagreen]
> I have applied the SP1 to MSSQL2005(Microsoft SQL Server 2005 - 9.00.2047.
00
> (Intel X86)) but the problem still persists. Any idea? Thanks in advnace
!
> "Michael D'Angelo" wrote:
>

Database Mail

I am using the sp_send_dbmail for my mailing activity for my client
applications. I have created a profile for the SP and assigned my name
and e-mail address. but when any of my users send a mail from the
client application the display name of the mail is my name and not the
person who actually sends the mail.
Is it possible to set the display name dynamically? or anyother
workaround ?
Any help would be appreciated :)
ThanxsThe only way I could think of is to setup several mail profiles and have each user use a different
mail profile. Or, use xp_smtp_sendmail. Also, consider wishing this feature at
connect.microsoft.com.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<brinda.shree@.gmail.com> wrote in message
news:1173789930.776042.169600@.c51g2000cwc.googlegroups.com...
>I am using the sp_send_dbmail for my mailing activity for my client
> applications. I have created a profile for the SP and assigned my name
> and e-mail address. but when any of my users send a mail from the
> client application the display name of the mail is my name and not the
> person who actually sends the mail.
> Is it possible to set the display name dynamically? or anyother
> workaround ?
> Any help would be appreciated :)
> Thanxs
>

Database Mail

MSSQL 2005 9.00.1399.06
Win2003 server
I have configured database Mail, and tried the Sent Test E-Mail
and it's working, sp_send_dbmail is working too
When I do some testing on jobs and Notification/Operators on a specific
job I get theese message in the error log:
Date 05.05.2006 12:20:00
Log SQL Agent (Current - 05.05.2006 12:20:00)
Message
[264] An attempt was made to send an email when no email session has been
established
and in the job history I get theese error:
Date 05.05.2006 12:30:00
Log Job History (Replication agents checkup)
Step ID 0
Server RDASP21
Job Name Replication agents checkup
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job succeeded. The Job was invoked by Schedule 2 (Replication agent
schedule.). The last step to run was step 1 (Run agent.). NOTE: Failed to
notify 'Roger Nygård' via email.
What is wrong in my setup?
-Roger NygårdThis should be fixed if you update to SP1. At least it was for me.
"Roger Nygård" <roger@.askit.no> wrote in message
news:125maitjqfkc493@.corp.supernews.com...
> MSSQL 2005 9.00.1399.06
> Win2003 server
> I have configured database Mail, and tried the Sent Test E-Mail
> and it's working, sp_send_dbmail is working too
> When I do some testing on jobs and Notification/Operators on a specific
> job I get theese message in the error log:
> Date 05.05.2006 12:20:00
> Log SQL Agent (Current - 05.05.2006 12:20:00)
> Message
> [264] An attempt was made to send an email when no email session has been
> established
>
> and in the job history I get theese error:
> Date 05.05.2006 12:30:00
> Log Job History (Replication agents checkup)
> Step ID 0
> Server RDASP21
> Job Name Replication agents checkup
> Step Name (Job outcome)
> Duration 00:00:00
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job succeeded. The Job was invoked by Schedule 2 (Replication agent
> schedule.). The last step to run was step 1 (Run agent.). NOTE: Failed
> to notify 'Roger Nygård' via email.
> What is wrong in my setup?
> -Roger Nygård
>|||I have applied the SP1 to MSSQL2005(Microsoft SQL Server 2005 - 9.00.2047.00
(Intel X86)) but the problem still persists. Any idea? Thanks in advnace!
"Michael D'Angelo" wrote:
> This should be fixed if you update to SP1. At least it was for me.
> "Roger Nygård" <roger@.askit.no> wrote in message
> news:125maitjqfkc493@.corp.supernews.com...
> > MSSQL 2005 9.00.1399.06
> > Win2003 server
> >
> > I have configured database Mail, and tried the Sent Test E-Mail
> > and it's working, sp_send_dbmail is working too
> >
> > When I do some testing on jobs and Notification/Operators on a specific
> > job I get theese message in the error log:
> > Date 05.05.2006 12:20:00
> > Log SQL Agent (Current - 05.05.2006 12:20:00)
> >
> > Message
> > [264] An attempt was made to send an email when no email session has been
> > established
> >
> >
> >
> > and in the job history I get theese error:
> > Date 05.05.2006 12:30:00
> > Log Job History (Replication agents checkup)
> >
> > Step ID 0
> > Server RDASP21
> > Job Name Replication agents checkup
> > Step Name (Job outcome)
> > Duration 00:00:00
> > Sql Severity 0
> > Sql Message ID 0
> > Operator Emailed
> > Operator Net sent
> > Operator Paged
> > Retries Attempted 0
> >
> > Message
> > The job succeeded. The Job was invoked by Schedule 2 (Replication agent
> > schedule.). The last step to run was step 1 (Run agent.). NOTE: Failed
> > to notify 'Roger Nygård' via email.
> >
> > What is wrong in my setup?
> >
> > -Roger Nygård
> >
>
>|||I forgot to restart the SQLAgent after configuring 'Alert System'. Thanks all
"stephanie" wrote:
> I have applied the SP1 to MSSQL2005(Microsoft SQL Server 2005 - 9.00.2047.00
> (Intel X86)) but the problem still persists. Any idea? Thanks in advnace!
> "Michael D'Angelo" wrote:
> > This should be fixed if you update to SP1. At least it was for me.
> >
> > "Roger Nygård" <roger@.askit.no> wrote in message
> > news:125maitjqfkc493@.corp.supernews.com...
> > > MSSQL 2005 9.00.1399.06
> > > Win2003 server
> > >
> > > I have configured database Mail, and tried the Sent Test E-Mail
> > > and it's working, sp_send_dbmail is working too
> > >
> > > When I do some testing on jobs and Notification/Operators on a specific
> > > job I get theese message in the error log:
> > > Date 05.05.2006 12:20:00
> > > Log SQL Agent (Current - 05.05.2006 12:20:00)
> > >
> > > Message
> > > [264] An attempt was made to send an email when no email session has been
> > > established
> > >
> > >
> > >
> > > and in the job history I get theese error:
> > > Date 05.05.2006 12:30:00
> > > Log Job History (Replication agents checkup)
> > >
> > > Step ID 0
> > > Server RDASP21
> > > Job Name Replication agents checkup
> > > Step Name (Job outcome)
> > > Duration 00:00:00
> > > Sql Severity 0
> > > Sql Message ID 0
> > > Operator Emailed
> > > Operator Net sent
> > > Operator Paged
> > > Retries Attempted 0
> > >
> > > Message
> > > The job succeeded. The Job was invoked by Schedule 2 (Replication agent
> > > schedule.). The last step to run was step 1 (Run agent.). NOTE: Failed
> > > to notify 'Roger Nygård' via email.
> > >
> > > What is wrong in my setup?
> > >
> > > -Roger Nygård
> > >
> >
> >
> >