How to setup SQL Mail on SQL 2005 express

Hi All,
I know the full version of SQL 2005 uses SMTP, and I know where to set it
up. I just installed SQL express and I can't find where to setup mail.
TIA,
JoeHere's a quote from 2005 BOL:
"Note:
Database Mail is not available in SQL Server 2005 Express Edition. "
Consider using xp_smtp_sendmail (Google) instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:57DCA9FF-798E-440F-AF6C-B4C6B19D08F2@.microsoft.com...
> Hi All,
> I know the full version of SQL 2005 uses SMTP, and I know where to set it
> up. I just installed SQL express and I can't find where to setup mail.
> TIA,
> Joe
>|||Database Mail is not supported under Express (neither is SQL Mail).
You can write your own or there are also some downloads and scripts
available on the Internet that you can use for Mail in Express. One
download is amDBObj at:
http://www.asql.biz/en/Download2005.aspx
-Sue
On Tue, 13 Mar 2007 06:50:36 -0700, jaylou
<jaylou@.discussions.microsoft.com> wrote:
>Hi All,
>I know the full version of SQL 2005 uses SMTP, and I know where to set it
>up. I just installed SQL express and I can't find where to setup mail.
>TIA,
>Joe

How to setup SQL Mail on SQL 2005 express

Hi All,
I know the full version of SQL 2005 uses SMTP, and I know where to set it
up. I just installed SQL express and I can't find where to setup mail.
TIA,
Joe
Database Mail is not supported under Express (neither is SQL Mail).
You can write your own or there are also some downloads and scripts
available on the Internet that you can use for Mail in Express. One
download is amDBObj at:
http://www.asql.biz/en/Download2005.aspx
-Sue
On Tue, 13 Mar 2007 06:50:36 -0700, jaylou
<jaylou@.discussions.microsoft.com> wrote:

>Hi All,
>I know the full version of SQL 2005 uses SMTP, and I know where to set it
>up. I just installed SQL express and I can't find where to setup mail.
>TIA,
>Joe

How to setup SQL Mail on SQL 2005 express

Hi All,
I know the full version of SQL 2005 uses SMTP, and I know where to set it
up. I just installed SQL express and I can't find where to setup mail.
TIA,
JoeHere's a quote from 2005 BOL:
"Note:
Database Mail is not available in SQL Server 2005 Express Edition. "
Consider using xp_smtp_sendmail (Google) instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:57DCA9FF-798E-440F-AF6C-B4C6B19D08F2@.microsoft.com...
> Hi All,
> I know the full version of SQL 2005 uses SMTP, and I know where to set it
> up. I just installed SQL express and I can't find where to setup mail.
> TIA,
> Joe
>|||Database Mail is not supported under Express (neither is SQL Mail).
You can write your own or there are also some downloads and scripts
available on the Internet that you can use for Mail in Express. One
download is amDBObj at:
http://www.asql.biz/en/Download2005.aspx
-Sue
On Tue, 13 Mar 2007 06:50:36 -0700, jaylou
<jaylou@.discussions.microsoft.com> wrote:

>Hi All,
>I know the full version of SQL 2005 uses SMTP, and I know where to set it
>up. I just installed SQL express and I can't find where to setup mail.
>TIA,
>Joe

HOW TO setup SQL MAIL

hi, I want to receive a mail when SQL Server Agent JOB fail,so I go to setup the SQL MAIL,but it skip an error "xp_get_mapi_profiles: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client." Someboby can tell me how to solve it?

thank you!

Hi,

SQL Mail needs a mapi file.

You can create this file either with Outlook or with Outlook Express.

regards matthias

|||

Thank you ! can you tell me the detailed resolvent.

|||

don't forget, if you're using SQL 2005, you should use "Database Mail" instead of "SQL Mail" which is being deprecated

http://www.microsoft.com/sql/prodinfo/features/top30features.mspx

http://www.sql-server-performance.com/da_email_functionality.asp

sql

How to setup Database Mail for SQLS 2005

Hello,
I have attempted to send test email from SQL Server Management Studio -
Management - Database Mail, without success.
Current setup information...
System information:
The OS is Windows XP SP2.
SQL Server 2005 Developer Edition (9.0.2047)
IIS is installed and IIS admin service is started
SMTP service is installed and SMTP service is started
I am logged in as the administrator.
The environment:
This computer is on a network that has exchange running. This computer
also has MS Outlook installed. I know that Database Mail does not need
either, but those are the facts.
This is my personal network. I am trying to learn how to setup Database
Mail. I am trying to set up mail to send email feedback for processes
such as database maintenance plans and SSIS processes.
These are the steps I have taken to troubleshoot my test email problem:
=B7 I have verified that yes; 'Database Mail' is started in surface
area configuration.
=B7 I ran:
USE msdb
GO
SELECT *
FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id =3D 8 ;
which showed the 'sent_status' column showing "Failed" and the
'last_mod_user' column showing "This is a test e-mail sent from
Database Mail on KEPLER"
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id =3D 8 ;
Which showed the column 'description' showing "The mail could not
be sent to the recipients because of the mail server failure. (Sending
Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
send mails to mail server. Mailbox unavailable. The server response
was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
=B7 I ran:
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
Which showed that yes I am a member of DatabaseMailUserRole.
=B7 I ran:
EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
Which showed the following values for each column in the one profile;
principal_id =3D 2
principal_name =3D guest
profile_id =3D 2
profile_name =3D TestMailProfile
is_default =3D 1
Why does;
principal_name =3D guest
instead of my user name?
=B7 I ran:
EXEC msdb.dbo.sysmail_start_sp
Which showed that mail is started.
=B7 I ran:
EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type =3D 'mail' ;
Which showed the state =3D INACTIVE
Why? Shouldn't it be active?
=B7 I ran:
SELECT sent_account_id, sent_date
FROM msdb.dbo.sysmail_sentitems ;
Which returned no records.
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log ;
Which showed many errors for the many times I tried to send a test
message.
The 'description' colums for each of these errors has the same
error as shown above.
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id =3D 8 ;
Which showed 'sent_status' column as "FAILED"
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id =3D 8 ;
Which also shows the same error as above...
=B7 I verified that I am a user in the msdb database, tha I am a member
of the DatabaseMailUserRole database role in the msdb database, and
that I am a member of sysadmin in the Security Server Roles.
=B7 I ran:
SELECT is_broker_enabled
FROM sys.databases
WHERE name =3D 'msdb' ;
Which shows that the broker is enabled.
=B7 I ran:
USE msdb
go
EXECUTE dbo.sysmail_help_status_sp ;
Which shows that the status is started.
When I tried to setup logging for a Maintenance Plan I got the
following error:
TITLE: Microsoft SQL Server Management Studio
--
There are no operators with email addresses defined on this server.
--
BUTTONS: OK
--
What does this mean?
I also set up a SSIS project where I tried to have mail inform of
success or failure. I got the same error message as above.
I trying to trouble shoot this problem I read and follow all I could
find on troubleshooting this problem.
What am I missing? What should I do next?Hi
I think this is to do with your exchange server's SMTP settings rather than
database mail itself, or possibly who you are setting as the sender and
recipient.
John
"dbuchanan" wrote:

> Hello,
> I have attempted to send test email from SQL Server Management Studio -
> Management - Database Mail, without success.
> Current setup information...
> System information:
> The OS is Windows XP SP2.
> SQL Server 2005 Developer Edition (9.0.2047)
> IIS is installed and IIS admin service is started
> SMTP service is installed and SMTP service is started
> I am logged in as the administrator.
> The environment:
> This computer is on a network that has exchange running. This computer
> also has MS Outlook installed. I know that Database Mail does not need
> either, but those are the facts.
> This is my personal network. I am trying to learn how to setup Database
> Mail. I am trying to set up mail to send email feedback for processes
> such as database maintenance plans and SSIS processes.
> These are the steps I have taken to troubleshoot my test email problem:
> · I have verified that yes; 'Database Mail' is started in surface
> area configuration.
> · I ran:
> USE msdb
> GO
> SELECT *
> FROM msdb.dbo.sysmail_allitems
> WHERE mailitem_id = 8 ;
> which showed the 'sent_status' column showing "Failed" and the
> 'last_mod_user' column showing "This is a test e-mail sent from
> Database Mail on KEPLER"
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log
> WHERE mailitem_id = 8 ;
> Which showed the column 'description' showing "The mail could not
> be sent to the recipients because of the mail server failure. (Sending
> Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
> send mails to mail server. Mailbox unavailable. The server response
> was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
> · I ran:
> EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
> Which showed that yes I am a member of DatabaseMailUserRole.
> · I ran:
> EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
> Which showed the following values for each column in the one profile;
> principal_id = 2
> principal_name = guest
> profile_id = 2
> profile_name = TestMailProfile
> is_default = 1
> Why does;
> principal_name = guest
> instead of my user name?
> · I ran:
> EXEC msdb.dbo.sysmail_start_sp
> Which showed that mail is started.
> · I ran:
> EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail' ;
> Which showed the state = INACTIVE
> Why? Shouldn't it be active?
> · I ran:
> SELECT sent_account_id, sent_date
> FROM msdb.dbo.sysmail_sentitems ;
> Which returned no records.
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log ;
> Which showed many errors for the many times I tried to send a test
> message.
> The 'description' colums for each of these errors has the same
> error as shown above.
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_allitems
> WHERE mailitem_id = 8 ;
> Which showed 'sent_status' column as "FAILED"
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log
> WHERE mailitem_id = 8 ;
> Which also shows the same error as above...
> · I verified that I am a user in the msdb database, tha I am a member
> of the DatabaseMailUserRole database role in the msdb database, and
> that I am a member of sysadmin in the Security Server Roles.
> · I ran:
> SELECT is_broker_enabled
> FROM sys.databases
> WHERE name = 'msdb' ;
> Which shows that the broker is enabled.
> · I ran:
> USE msdb
> go
> EXECUTE dbo.sysmail_help_status_sp ;
> Which shows that the status is started.
> When I tried to setup logging for a Maintenance Plan I got the
> following error:
> TITLE: Microsoft SQL Server Management Studio
> --
> There are no operators with email addresses defined on this server.
> --
> BUTTONS: OK
> --
> What does this mean?
> I also set up a SSIS project where I tried to have mail inform of
> success or failure. I got the same error message as above.
> I trying to trouble shoot this problem I read and follow all I could
> find on troubleshooting this problem.
> What am I missing? What should I do next?
>|||check your port to make sure you can send out via port 25. Anti Virus
sofatware blocks that port
stoney
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I think this is to do with your exchange server's SMTP settings rather tha
n
> database mail itself, or possibly who you are setting as the sender and
> recipient.
> John
> "dbuchanan" wrote:
>

How to setup Database Mail for SQLS 2005

Hello,
I have attempted to send test email from SQL Server Management Studio -
Management - Database Mail, without success.
Current setup information...
System information:
The OS is Windows XP SP2.
SQL Server 2005 Developer Edition (9.0.2047)
IIS is installed and IIS admin service is started
SMTP service is installed and SMTP service is started
I am logged in as the administrator.
The environment:
This computer is on a network that has exchange running. This computer
also has MS Outlook installed. I know that Database Mail does not need
either, but those are the facts.
This is my personal network. I am trying to learn how to setup Database
Mail. I am trying to set up mail to send email feedback for processes
such as database maintenance plans and SSIS processes.
These are the steps I have taken to troubleshoot my test email problem:
I have verified that yes; 'Database Mail' is started in surface
area configuration.
I ran:
USE msdb
GO
SELECT *
FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id = 8 ;
which showed the 'sent_status' column showing "Failed" and the
'last_mod_user' column showing "This is a test e-mail sent from
Database Mail on KEPLER"
I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id = 8 ;
Which showed the column 'description' showing "The mail could not
be sent to the recipients because of the mail server failure. (Sending
Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
send mails to mail server. Mailbox unavailable. The server response
was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
I ran:
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
Which showed that yes I am a member of DatabaseMailUserRole.
I ran:
EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
Which showed the following values for each column in the one profile;
principal_id = 2
principal_name = guest
profile_id = 2
profile_name = TestMailProfile
is_default = 1
Why does;
principal_name = guest
instead of my user name?
I ran:
EXEC msdb.dbo.sysmail_start_sp
Which showed that mail is started.
I ran:
EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail' ;
Which showed the state = INACTIVE
Why? Shouldn't it be active?
I ran:
SELECT sent_account_id, sent_date
FROM msdb.dbo.sysmail_sentitems ;
Which returned no records.
I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log ;
Which showed many errors for the many times I tried to send a test
message.
The 'description' colums for each of these errors has the same
error as shown above.
I ran:
SELECT *
FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id = 8 ;
Which showed 'sent_status' column as "FAILED"
I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id = 8 ;
Which also shows the same error as above...
I verified that I am a user in the msdb database, tha I am a member
of the DatabaseMailUserRole database role in the msdb database, and
that I am a member of sysadmin in the Security Server Roles.
I ran:
SELECT is_broker_enabled
FROM sys.databases
WHERE name = 'msdb' ;
Which shows that the broker is enabled.
I ran:
USE msdb
go
EXECUTE dbo.sysmail_help_status_sp ;
Which shows that the status is started.
When I tried to setup logging for a Maintenance Plan I got the
following error:
TITLE: Microsoft SQL Server Management Studio
There are no operators with email addresses defined on this server.
BUTTONS: OK
What does this mean?
I also set up a SSIS project where I tried to have mail inform of
success or failure. I got the same error message as above.
I trying to trouble shoot this problem I read and follow all I could
find on troubleshooting this problem.
What am I missing? What should I do next?
Hi
I think this is to do with your exchange server's SMTP settings rather than
database mail itself, or possibly who you are setting as the sender and
recipient.
John
"dbuchanan" wrote:

> Hello,
> I have attempted to send test email from SQL Server Management Studio -
> Management - Database Mail, without success.
> Current setup information...
> System information:
> The OS is Windows XP SP2.
> SQL Server 2005 Developer Edition (9.0.2047)
> IIS is installed and IIS admin service is started
> SMTP service is installed and SMTP service is started
> I am logged in as the administrator.
> The environment:
> This computer is on a network that has exchange running. This computer
> also has MS Outlook installed. I know that Database Mail does not need
> either, but those are the facts.
> This is my personal network. I am trying to learn how to setup Database
> Mail. I am trying to set up mail to send email feedback for processes
> such as database maintenance plans and SSIS processes.
> These are the steps I have taken to troubleshoot my test email problem:
> · I have verified that yes; 'Database Mail' is started in surface
> area configuration.
> · I ran:
> USE msdb
> GO
> SELECT *
> FROM msdb.dbo.sysmail_allitems
> WHERE mailitem_id = 8 ;
> which showed the 'sent_status' column showing "Failed" and the
> 'last_mod_user' column showing "This is a test e-mail sent from
> Database Mail on KEPLER"
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log
> WHERE mailitem_id = 8 ;
> Which showed the column 'description' showing "The mail could not
> be sent to the recipients because of the mail server failure. (Sending
> Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
> send mails to mail server. Mailbox unavailable. The server response
> was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
> · I ran:
> EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
> Which showed that yes I am a member of DatabaseMailUserRole.
> · I ran:
> EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
> Which showed the following values for each column in the one profile;
> principal_id = 2
> principal_name = guest
> profile_id = 2
> profile_name = TestMailProfile
> is_default = 1
> Why does;
> principal_name = guest
> instead of my user name?
> · I ran:
> EXEC msdb.dbo.sysmail_start_sp
> Which showed that mail is started.
> · I ran:
> EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail' ;
> Which showed the state = INACTIVE
> Why? Shouldn't it be active?
> · I ran:
> SELECT sent_account_id, sent_date
> FROM msdb.dbo.sysmail_sentitems ;
> Which returned no records.
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log ;
> Which showed many errors for the many times I tried to send a test
> message.
> The 'description' colums for each of these errors has the same
> error as shown above.
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_allitems
> WHERE mailitem_id = 8 ;
> Which showed 'sent_status' column as "FAILED"
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log
> WHERE mailitem_id = 8 ;
> Which also shows the same error as above...
> · I verified that I am a user in the msdb database, tha I am a member
> of the DatabaseMailUserRole database role in the msdb database, and
> that I am a member of sysadmin in the Security Server Roles.
> · I ran:
> SELECT is_broker_enabled
> FROM sys.databases
> WHERE name = 'msdb' ;
> Which shows that the broker is enabled.
> · I ran:
> USE msdb
> go
> EXECUTE dbo.sysmail_help_status_sp ;
> Which shows that the status is started.
> When I tried to setup logging for a Maintenance Plan I got the
> following error:
> TITLE: Microsoft SQL Server Management Studio
> --
> There are no operators with email addresses defined on this server.
> --
> BUTTONS: OK
> --
> What does this mean?
> I also set up a SSIS project where I tried to have mail inform of
> success or failure. I got the same error message as above.
> I trying to trouble shoot this problem I read and follow all I could
> find on troubleshooting this problem.
> What am I missing? What should I do next?
>
|||check your port to make sure you can send out via port 25. Anti Virus
sofatware blocks that port
stoney
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I think this is to do with your exchange server's SMTP settings rather than
> database mail itself, or possibly who you are setting as the sender and
> recipient.
> John
> "dbuchanan" wrote:

How to setup Database Mail for SQLS 2005

Hello,
I have attempted to send test email from SQL Server Management Studio -
Management - Database Mail, without success.
Current setup information...
System information:
The OS is Windows XP SP2.
SQL Server 2005 Developer Edition (9.0.2047)
IIS is installed and IIS admin service is started
SMTP service is installed and SMTP service is started
I am logged in as the administrator.
The environment:
This computer is on a network that has exchange running. This computer
also has MS Outlook installed. I know that Database Mail does not need
either, but those are the facts.
This is my personal network. I am trying to learn how to setup Database
Mail. I am trying to set up mail to send email feedback for processes
such as database maintenance plans and SSIS processes.
These are the steps I have taken to troubleshoot my test email problem:
=B7 I have verified that yes; 'Database Mail' is started in surface
area configuration.
=B7 I ran:
USE msdb
GO
SELECT *
FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id =3D 8 ;
which showed the 'sent_status' column showing "Failed" and the
'last_mod_user' column showing "This is a test e-mail sent from
Database Mail on KEPLER"
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id =3D 8 ;
Which showed the column 'description' showing "The mail could not
be sent to the recipients because of the mail server failure. (Sending
Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
send mails to mail server. Mailbox unavailable. The server response
was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
=B7 I ran:
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
Which showed that yes I am a member of DatabaseMailUserRole.
=B7 I ran:
EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
Which showed the following values for each column in the one profile;
principal_id =3D 2
principal_name =3D guest
profile_id =3D 2
profile_name =3D TestMailProfile
is_default =3D 1
Why does;
principal_name =3D guest
instead of my user name?
=B7 I ran:
EXEC msdb.dbo.sysmail_start_sp
Which showed that mail is started.
=B7 I ran:
EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type =3D 'mail' ;
Which showed the state =3D INACTIVE
Why? Shouldn't it be active?
=B7 I ran:
SELECT sent_account_id, sent_date
FROM msdb.dbo.sysmail_sentitems ;
Which returned no records.
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log ;
Which showed many errors for the many times I tried to send a test
message.
The 'description' colums for each of these errors has the same
error as shown above.
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id =3D 8 ;
Which showed 'sent_status' column as "FAILED"
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id =3D 8 ;
Which also shows the same error as above...
=B7 I verified that I am a user in the msdb database, tha I am a member
of the DatabaseMailUserRole database role in the msdb database, and
that I am a member of sysadmin in the Security Server Roles.
=B7 I ran:
SELECT is_broker_enabled
FROM sys.databases
WHERE name =3D 'msdb' ;
Which shows that the broker is enabled.
=B7 I ran:
USE msdb
go
EXECUTE dbo.sysmail_help_status_sp ;
Which shows that the status is started.
When I tried to setup logging for a Maintenance Plan I got the
following error:
TITLE: Microsoft SQL Server Management Studio
--
There are no operators with email addresses defined on this server.
--
BUTTONS: OK
--
What does this mean?
I also set up a SSIS project where I tried to have mail inform of
success or failure. I got the same error message as above.
I trying to trouble shoot this problem I read and follow all I could
find on troubleshooting this problem.
What am I missing? What should I do next?Hi
I think this is to do with your exchange server's SMTP settings rather than
database mail itself, or possibly who you are setting as the sender and
recipient.
John
"dbuchanan" wrote:
> Hello,
> I have attempted to send test email from SQL Server Management Studio -
> Management - Database Mail, without success.
> Current setup information...
> System information:
> The OS is Windows XP SP2.
> SQL Server 2005 Developer Edition (9.0.2047)
> IIS is installed and IIS admin service is started
> SMTP service is installed and SMTP service is started
> I am logged in as the administrator.
> The environment:
> This computer is on a network that has exchange running. This computer
> also has MS Outlook installed. I know that Database Mail does not need
> either, but those are the facts.
> This is my personal network. I am trying to learn how to setup Database
> Mail. I am trying to set up mail to send email feedback for processes
> such as database maintenance plans and SSIS processes.
> These are the steps I have taken to troubleshoot my test email problem:
> · I have verified that yes; 'Database Mail' is started in surface
> area configuration.
> · I ran:
> USE msdb
> GO
> SELECT *
> FROM msdb.dbo.sysmail_allitems
> WHERE mailitem_id = 8 ;
> which showed the 'sent_status' column showing "Failed" and the
> 'last_mod_user' column showing "This is a test e-mail sent from
> Database Mail on KEPLER"
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log
> WHERE mailitem_id = 8 ;
> Which showed the column 'description' showing "The mail could not
> be sent to the recipients because of the mail server failure. (Sending
> Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
> send mails to mail server. Mailbox unavailable. The server response
> was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
> · I ran:
> EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
> Which showed that yes I am a member of DatabaseMailUserRole.
> · I ran:
> EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
> Which showed the following values for each column in the one profile;
> principal_id = 2
> principal_name = guest
> profile_id = 2
> profile_name = TestMailProfile
> is_default = 1
> Why does;
> principal_name = guest
> instead of my user name?
> · I ran:
> EXEC msdb.dbo.sysmail_start_sp
> Which showed that mail is started.
> · I ran:
> EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail' ;
> Which showed the state = INACTIVE
> Why? Shouldn't it be active?
> · I ran:
> SELECT sent_account_id, sent_date
> FROM msdb.dbo.sysmail_sentitems ;
> Which returned no records.
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log ;
> Which showed many errors for the many times I tried to send a test
> message.
> The 'description' colums for each of these errors has the same
> error as shown above.
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_allitems
> WHERE mailitem_id = 8 ;
> Which showed 'sent_status' column as "FAILED"
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log
> WHERE mailitem_id = 8 ;
> Which also shows the same error as above...
> · I verified that I am a user in the msdb database, tha I am a member
> of the DatabaseMailUserRole database role in the msdb database, and
> that I am a member of sysadmin in the Security Server Roles.
> · I ran:
> SELECT is_broker_enabled
> FROM sys.databases
> WHERE name = 'msdb' ;
> Which shows that the broker is enabled.
> · I ran:
> USE msdb
> go
> EXECUTE dbo.sysmail_help_status_sp ;
> Which shows that the status is started.
> When I tried to setup logging for a Maintenance Plan I got the
> following error:
> TITLE: Microsoft SQL Server Management Studio
> --
> There are no operators with email addresses defined on this server.
> --
> BUTTONS: OK
> --
> What does this mean?
> I also set up a SSIS project where I tried to have mail inform of
> success or failure. I got the same error message as above.
> I trying to trouble shoot this problem I read and follow all I could
> find on troubleshooting this problem.
> What am I missing? What should I do next?
>|||check your port to make sure you can send out via port 25. Anti Virus
sofatware blocks that port
stoney
"John Bell" wrote:
> Hi
> I think this is to do with your exchange server's SMTP settings rather than
> database mail itself, or possibly who you are setting as the sender and
> recipient.
> John
> "dbuchanan" wrote:
> > Hello,
> >
> > I have attempted to send test email from SQL Server Management Studio -
> > Management - Database Mail, without success.
> >
> > Current setup information...
> >
> > System information:
> > The OS is Windows XP SP2.
> > SQL Server 2005 Developer Edition (9.0.2047)
> > IIS is installed and IIS admin service is started
> > SMTP service is installed and SMTP service is started
> > I am logged in as the administrator.
> >
> > The environment:
> > This computer is on a network that has exchange running. This computer
> > also has MS Outlook installed. I know that Database Mail does not need
> > either, but those are the facts.
> >
> > This is my personal network. I am trying to learn how to setup Database
> > Mail. I am trying to set up mail to send email feedback for processes
> > such as database maintenance plans and SSIS processes.
> >
> > These are the steps I have taken to troubleshoot my test email problem:
> >
> > · I have verified that yes; 'Database Mail' is started in surface
> > area configuration.
> >
> > · I ran:
> >
> > USE msdb
> > GO
> > SELECT *
> > FROM msdb.dbo.sysmail_allitems
> > WHERE mailitem_id = 8 ;
> >
> > which showed the 'sent_status' column showing "Failed" and the
> > 'last_mod_user' column showing "This is a test e-mail sent from
> > Database Mail on KEPLER"
> >
> > · I ran:
> >
> > SELECT *
> > FROM msdb.dbo.sysmail_event_log
> > WHERE mailitem_id = 8 ;
> >
> > Which showed the column 'description' showing "The mail could not
> > be sent to the recipients because of the mail server failure. (Sending
> > Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
> > send mails to mail server. Mailbox unavailable. The server response
> > was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
> >
> > · I ran:
> >
> > EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
> >
> > Which showed that yes I am a member of DatabaseMailUserRole.
> >
> > · I ran:
> >
> > EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
> >
> > Which showed the following values for each column in the one profile;
> >
> > principal_id = 2
> > principal_name = guest
> > profile_id = 2
> > profile_name = TestMailProfile
> > is_default = 1
> >
> > Why does;
> > principal_name = guest
> > instead of my user name?
> >
> > · I ran:
> >
> > EXEC msdb.dbo.sysmail_start_sp
> >
> > Which showed that mail is started.
> >
> > · I ran:
> >
> > EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail' ;
> >
> > Which showed the state = INACTIVE
> > Why? Shouldn't it be active?
> >
> > · I ran:
> >
> > SELECT sent_account_id, sent_date
> > FROM msdb.dbo.sysmail_sentitems ;
> >
> > Which returned no records.
> >
> > · I ran:
> >
> > SELECT *
> > FROM msdb.dbo.sysmail_event_log ;
> >
> > Which showed many errors for the many times I tried to send a test
> > message.
> >
> > The 'description' colums for each of these errors has the same
> > error as shown above.
> >
> > · I ran:
> >
> > SELECT *
> > FROM msdb.dbo.sysmail_allitems
> > WHERE mailitem_id = 8 ;
> >
> > Which showed 'sent_status' column as "FAILED"
> >
> > · I ran:
> >
> > SELECT *
> > FROM msdb.dbo.sysmail_event_log
> > WHERE mailitem_id = 8 ;
> >
> > Which also shows the same error as above...
> >
> > · I verified that I am a user in the msdb database, tha I am a member
> > of the DatabaseMailUserRole database role in the msdb database, and
> > that I am a member of sysadmin in the Security Server Roles.
> >
> > · I ran:
> >
> > SELECT is_broker_enabled
> > FROM sys.databases
> > WHERE name = 'msdb' ;
> >
> > Which shows that the broker is enabled.
> >
> > · I ran:
> >
> > USE msdb
> > go
> > EXECUTE dbo.sysmail_help_status_sp ;
> >
> > Which shows that the status is started.
> >
> > When I tried to setup logging for a Maintenance Plan I got the
> > following error:
> >
> > TITLE: Microsoft SQL Server Management Studio
> > --
> > There are no operators with email addresses defined on this server.
> > --
> > BUTTONS: OK
> > --
> >
> > What does this mean?
> >
> > I also set up a SSIS project where I tried to have mail inform of
> > success or failure. I got the same error message as above.
> >
> > I trying to trouble shoot this problem I read and follow all I could
> > find on troubleshooting this problem.
> >
> > What am I missing? What should I do next?
> >
> >sql

How to send the SQL Reports via email.

Hi Everybody

can somebody please help in finding how to mail the sql server reports via email.

Thanks alot

Nee123

Copy this to help you:

Delivered by

Select the delivery extension to use to distribute the report. Selecting a delivery extension causes other settings to appear. The settings vary depending on the delivery extension you choose. Reporting Services provides Report Server File Share and Report Server E-mail delivery.

Report Server E-Mail Settings

Choosing Report Server E-mail shows delivery options that specify how to deliver a report to a mailbox. To e-mail a report, your report server must be configured for e-mail delivery. E-mail subscriptions provide fields that are familiar to e-mail users (for example, To, Subject, and Priority fields). Specify Include Report to embed or attach the report, and Include Link to include a URL to the report. Render Format to choose a presentation format for the attached or embedded report. For more information about how to specify these options, search SQL Server Books Online for the topic How to subscribe to a report.

Report Server File Share

Choosing Report Server File Share shows delivery options that specify how to deliver a report to a location on the file system. You can deliver any report to a file share. However, reports that support interactive features (including matrix reports that support drill-down to supporting rows and columns) are rendered as static files. You cannot view drill-down rows and columns in a file that is based on a matrix report.

The file share must be specified in Uniform Naming Convention (UNC) format (for example, \\myserver\myreportfiles). Do not include a trailing backslash in the path name. The file name is based on the report name, which you can override by setting the File Name option. The report file will be delivered in a file format that is based on the render format (for example, if you choose Excel, the report is delivered as an .xls file). For more information about how to specify these options, search SQL Server Books Online for the topic How to subscribe to a report.

|||If you use Report Manager, the email extension should be the default option when you select New Subscription. If the email extension is not there check the RSReportServer.config file and see if the configuration info for the email extension contains information for the smtpserver and from element. If not, add the correct information and the email extension will show up in Report Manager.|||

Thanks alot guys for your timely help

Best

Nee123

|||Hi I'm having problems getting the Report Server E-mail delivery option to show up I only have the File share option to chose from in my subscriptions. I edited the RSReportServer.config file and when i look at the Reporting Services Configuration Manager it looks like everything is set up correctly. It's using the desired sender address, delivery method = use SMTP server and the desired smtp server is listed.

Any clues to how i can get the e-mail delivery option to show up?

Thanks

|||

Please refer to the following link for the problem:

http://msdn2.microsoft.com/en-us/library/ms159155.aspx

How to send the SQL Reports via email.

Hi Everybody

can somebody please help in finding how to mail the sql server reports via email.

Thanks alot

Nee123

Copy this to help you:

Delivered by

Select the delivery extension to use to distribute the report. Selecting a delivery extension causes other settings to appear. The settings vary depending on the delivery extension you choose. Reporting Services provides Report Server File Share and Report Server E-mail delivery.

Report Server E-Mail Settings

Choosing Report Server E-mail shows delivery options that specify how to deliver a report to a mailbox. To e-mail a report, your report server must be configured for e-mail delivery. E-mail subscriptions provide fields that are familiar to e-mail users (for example, To, Subject, and Priority fields). Specify Include Report to embed or attach the report, and Include Link to include a URL to the report. Render Format to choose a presentation format for the attached or embedded report. For more information about how to specify these options, search SQL Server Books Online for the topic How to subscribe to a report.

Report Server File Share

Choosing Report Server File Share shows delivery options that specify how to deliver a report to a location on the file system. You can deliver any report to a file share. However, reports that support interactive features (including matrix reports that support drill-down to supporting rows and columns) are rendered as static files. You cannot view drill-down rows and columns in a file that is based on a matrix report.

The file share must be specified in Uniform Naming Convention (UNC) format (for example, \\myserver\myreportfiles). Do not include a trailing backslash in the path name. The file name is based on the report name, which you can override by setting the File Name option. The report file will be delivered in a file format that is based on the render format (for example, if you choose Excel, the report is delivered as an .xls file). For more information about how to specify these options, search SQL Server Books Online for the topic How to subscribe to a report.

|||If you use Report Manager, the email extension should be the default option when you select New Subscription. If the email extension is not there check the RSReportServer.config file and see if the configuration info for the email extension contains information for the smtpserver and from element. If not, add the correct information and the email extension will show up in Report Manager.|||

Thanks alot guys for your timely help

Best

Nee123

|||Hi I'm having problems getting the Report Server E-mail delivery option to show up I only have the File share option to chose from in my subscriptions. I edited the RSReportServer.config file and when i look at the Reporting Services Configuration Manager it looks like everything is set up correctly. It's using the desired sender address, delivery method = use SMTP server and the desired smtp server is listed.

Any clues to how i can get the e-mail delivery option to show up?

Thanks

|||

Please refer to the following link for the problem:

http://msdn2.microsoft.com/en-us/library/ms159155.aspx

How to send the SQL Reports via email

Hi Everybody

can somebody please help in finding how to mail the sql server reports via email.

Thanks alot

Nee123

Copy this to help you:

Delivered by

Select the delivery extension to use to distribute the report. Selecting a delivery extension causes other settings to appear. The settings vary depending on the delivery extension you choose. Reporting Services provides Report Server File Share and Report Server E-mail delivery.

Report Server E-Mail Settings

Choosing Report Server E-mail shows delivery options that specify how to deliver a report to a mailbox. To e-mail a report, your report server must be configured for e-mail delivery. E-mail subscriptions provide fields that are familiar to e-mail users (for example, To, Subject, and Priority fields). Specify Include Report to embed or attach the report, and Include Link to include a URL to the report. Render Format to choose a presentation format for the attached or embedded report. For more information about how to specify these options, search SQL Server Books Online for the topic How to subscribe to a report.

Report Server File Share

Choosing Report Server File Share shows delivery options that specify how to deliver a report to a location on the file system. You can deliver any report to a file share. However, reports that support interactive features (including matrix reports that support drill-down to supporting rows and columns) are rendered as static files. You cannot view drill-down rows and columns in a file that is based on a matrix report.

The file share must be specified in Uniform Naming Convention (UNC) format (for example, \\myserver\myreportfiles). Do not include a trailing backslash in the path name. The file name is based on the report name, which you can override by setting the File Name option. The report file will be delivered in a file format that is based on the render format (for example, if you choose Excel, the report is delivered as an .xls file). For more information about how to specify these options, search SQL Server Books Online for the topic How to subscribe to a report.

|||If you use Report Manager, the email extension should be the default option when you select New Subscription. If the email extension is not there check the RSReportServer.config file and see if the configuration info for the email extension contains information for the smtpserver and from element. If not, add the correct information and the email extension will show up in Report Manager.|||

Thanks alot guys for your timely help

Best

Nee123

|||Hi I'm having problems getting the Report Server E-mail delivery option to show up I only have the File share option to chose from in my subscriptions. I edited the RSReportServer.config file and when i look at the Reporting Services Configuration Manager it looks like everything is set up correctly. It's using the desired sender address, delivery method = use SMTP server and the desired smtp server is listed.

Any clues to how i can get the e-mail delivery option to show up?

Thanks

|||

Please refer to the following link for the problem:

http://msdn2.microsoft.com/en-us/library/ms159155.aspx

how to send sql mail using condition statement

I try to run the following code in DTS, but i not work. What i want is
if do not have duplicate data, then the table will be dropped. While
if have duplicate data, the table will not be dropped but an email
notification will be sent. Why when there is no duplicate data, the
email still will be sent out?
if not exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
drop table [dbo].[Rewards_CatalogProducts_CS]
end
else
if exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
use master
go
declare @.FROM NVARCHAR(4000),
@.FROM_NAME NVARCHAR(4000),
@.TO NVARCHAR(4000),
@.CC NVARCHAR(4000),
@.BCC NVARCHAR(4000),
@.priority NVARCHAR(10),
@.subject NVARCHAR(4000),
@.message NVARCHAR(4000),
@.type NVARCHAR(100),
@.attachments NVARCHAR(4000),
@.codepage INT,
@.rc INT
select @.FROM = N'sqlmail@.cyber-village.net',
@.FROM_NAME = N'ChangMian',
@.TO = N'tchangmian@.yahoo.com.sg',
@.CC = N'changmian@.cyber-village.net',
@.priority = N'High',
@.subject = N'Ei,can wor',
@.message = N'&

---

Hello SQL Server SMTP SQL
Mail

---

',
@.type = N'text/html',
@.attachments = N'',
@.codepage = 0
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.FROM,
@.TO = @.TO,
@.CC = @.CC,
@.priority = @.priority,
@.subject = @.subject,
@.message = @.message,
@.type = @.type,
@.attachments = @.attachments,
@.codepage = @.codepage,
@.server = N'mail.cyber-village.net'
select RC = @.rc
goYour send mail script is in a different batch because of the 'GO' batch
terminator. Consider revising your script like the example below:
declare @.FROM NVARCHAR(4000),
@.FROM_NAME NVARCHAR(4000),
@.TO NVARCHAR(4000),
@.CC NVARCHAR(4000),
@.BCC NVARCHAR(4000),
@.priority NVARCHAR(10),
@.subject NVARCHAR(4000),
@.message NVARCHAR(4000),
@.type NVARCHAR(100),
@.attachments NVARCHAR(4000),
@.codepage INT,
@.rc INT
if not exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
drop table [dbo].[Rewards_CatalogProducts_CS]
end
else
begin
select @.FROM = N'sqlmail@.cyber-village.net',
@.FROM_NAME = N'ChangMian',
@.TO = N'tchangmian@.yahoo.com.sg',
@.CC = N'changmian@.cyber-village.net',
@.priority = N'High',
@.subject = N'Ei,can wor',
@.message = N'&

---

Hello SQL Server SMTP SQL
Mail

---

',
@.type = N'text/html',
@.attachments = N'',
@.codepage = 0
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.FROM,
@.TO = @.TO,
@.CC = @.CC,
@.priority = @.priority,
@.subject = @.subject,
@.message = @.message,
@.type = @.type,
@.attachments = @.attachments,
@.codepage = @.codepage,
@.server = N'mail.cyber-village.net'
select RC = @.rc
end
go
--
Hope this helps.
Dan Guzman
SQL Server MVP
"tchangmian" <tchangmian@.yahoo.com.sg> wrote in message
news:6447ee25.0410042022.52f52ac5@.posting.google.com...
>I try to run the following code in DTS, but i not work. What i want is
> if do not have duplicate data, then the table will be dropped. While
> if have duplicate data, the table will not be dropped but an email
> notification will be sent. Why when there is no duplicate data, the
> email still will be sent out?
>
> if not exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
> begin
> drop table [dbo].[Rewards_CatalogProducts_CS]
> end
>
> else
> if exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
>
> use master
> go
> declare @.FROM NVARCHAR(4000),
> @.FROM_NAME NVARCHAR(4000),
> @.TO NVARCHAR(4000),
> @.CC NVARCHAR(4000),
> @.BCC NVARCHAR(4000),
> @.priority NVARCHAR(10),
> @.subject NVARCHAR(4000),
> @.message NVARCHAR(4000),
> @.type NVARCHAR(100),
> @.attachments NVARCHAR(4000),
> @.codepage INT,
> @.rc INT
> select @.FROM = N'sqlmail@.cyber-village.net',
> @.FROM_NAME = N'ChangMian',
> @.TO = N'tchangmian@.yahoo.com.sg',
> @.CC = N'changmian@.cyber-village.net',
> @.priority = N'High',
> @.subject = N'Ei,can wor',
> @.message = N'<HTML><H1>Hello SQL Server SMTP SQL
> Mail</H1></HTML>',
> @.type = N'text/html',
> @.attachments = N'',
> @.codepage = 0
> exec @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = @.FROM,
> @.TO = @.TO,
> @.CC = @.CC,
> @.priority = @.priority,
> @.subject = @.subject,
> @.message = @.message,
> @.type = @.type,
> @.attachments = @.attachments,
> @.codepage = @.codepage,
> @.server = N'mail.cyber-village.net'
> select RC = @.rc
> go

how to send sql mail using condition statement

I try to run the following code in DTS, but i not work. What i want is
if do not have duplicate data, then the table will be dropped. While
if have duplicate data, the table will not be dropped but an email
notification will be sent. Why when there is no duplicate data, the
email still will be sent out?
if not exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
drop table [dbo].[Rewards_CatalogProducts_CS]
end
else
if exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
use master
go
declare @.FROM NVARCHAR(4000),
@.FROM_NAME NVARCHAR(4000),
@.TO NVARCHAR(4000),
@.CC NVARCHAR(4000),
@.BCC NVARCHAR(4000),
@.priority NVARCHAR(10),
@.subject NVARCHAR(4000),
@.message NVARCHAR(4000),
@.type NVARCHAR(100),
@.attachments NVARCHAR(4000),
@.codepage INT,
@.rc INT
select @.FROM = N'sqlmail@.cyber-village.net',
@.FROM_NAME = N'ChangMian',
@.TO = N'tchangmian@.yahoo.com.sg',
@.CC = N'changmian@.cyber-village.net',
@.priority = N'High',
@.subject = N'Ei,can wor',
@.message = N'<HTML><H1>Hello SQL Server SMTP SQL
Mail</H1></HTML>',
@.type = N'text/html',
@.attachments = N'',
@.codepage = 0
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.FROM,
@.TO = @.TO,
@.CC = @.CC,
@.priority = @.priority,
@.subject = @.subject,
@.message = @.message,
@.type = @.type,
@.attachments = @.attachments,
@.codepage = @.codepage,
@.server = N'mail.cyber-village.net'
select RC = @.rc
go
Your send mail script is in a different batch because of the 'GO' batch
terminator. Consider revising your script like the example below:
declare @.FROM NVARCHAR(4000),
@.FROM_NAME NVARCHAR(4000),
@.TO NVARCHAR(4000),
@.CC NVARCHAR(4000),
@.BCC NVARCHAR(4000),
@.priority NVARCHAR(10),
@.subject NVARCHAR(4000),
@.message NVARCHAR(4000),
@.type NVARCHAR(100),
@.attachments NVARCHAR(4000),
@.codepage INT,
@.rc INT
if not exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
drop table [dbo].[Rewards_CatalogProducts_CS]
end
else
begin
select @.FROM = N'sqlmail@.cyber-village.net',
@.FROM_NAME = N'ChangMian',
@.TO = N'tchangmian@.yahoo.com.sg',
@.CC = N'changmian@.cyber-village.net',
@.priority = N'High',
@.subject = N'Ei,can wor',
@.message = N'<HTML><H1>Hello SQL Server SMTP SQL
Mail</H1></HTML>',
@.type = N'text/html',
@.attachments = N'',
@.codepage = 0
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.FROM,
@.TO = @.TO,
@.CC = @.CC,
@.priority = @.priority,
@.subject = @.subject,
@.message = @.message,
@.type = @.type,
@.attachments = @.attachments,
@.codepage = @.codepage,
@.server = N'mail.cyber-village.net'
select RC = @.rc
end
go
Hope this helps.
Dan Guzman
SQL Server MVP
"tchangmian" <tchangmian@.yahoo.com.sg> wrote in message
news:6447ee25.0410042022.52f52ac5@.posting.google.c om...
>I try to run the following code in DTS, but i not work. What i want is
> if do not have duplicate data, then the table will be dropped. While
> if have duplicate data, the table will not be dropped but an email
> notification will be sent. Why when there is no duplicate data, the
> email still will be sent out?
>
> if not exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
> begin
> drop table [dbo].[Rewards_CatalogProducts_CS]
> end
>
> else
> if exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
>
> use master
> go
> declare @.FROM NVARCHAR(4000),
> @.FROM_NAME NVARCHAR(4000),
> @.TO NVARCHAR(4000),
> @.CC NVARCHAR(4000),
> @.BCC NVARCHAR(4000),
> @.priority NVARCHAR(10),
> @.subject NVARCHAR(4000),
> @.message NVARCHAR(4000),
> @.type NVARCHAR(100),
> @.attachments NVARCHAR(4000),
> @.codepage INT,
> @.rc INT
> select @.FROM = N'sqlmail@.cyber-village.net',
> @.FROM_NAME = N'ChangMian',
> @.TO = N'tchangmian@.yahoo.com.sg',
> @.CC = N'changmian@.cyber-village.net',
> @.priority = N'High',
> @.subject = N'Ei,can wor',
> @.message = N'<HTML><H1>Hello SQL Server SMTP SQL
> Mail</H1></HTML>',
> @.type = N'text/html',
> @.attachments = N'',
> @.codepage = 0
> exec @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = @.FROM,
> @.TO = @.TO,
> @.CC = @.CC,
> @.priority = @.priority,
> @.subject = @.subject,
> @.message = @.message,
> @.type = @.type,
> @.attachments = @.attachments,
> @.codepage = @.codepage,
> @.server = N'mail.cyber-village.net'
> select RC = @.rc
> go

how to send mail using trigger

Hello
I have created a update grigger. I want to send a mail whenever is trigger
is fired. I tried using xp_sendmail, but it required mapi account to be
configured in server. I want to avoid this situation. Can I use cdo code in
trigger?
regards
Ashish
"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
I believe so. But I wouldn't recommend it.
It'll really mess up performance.
You're better off having the trigger enter a row into a table and then
having a scheduled task go through once every X minutes sending emails.

> regards
> Ashish
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Well for SQL Server 2000 I agree, but as the Mail Service is now
enabled with SQL Server Service Broker in SQL Server 2005 you *could*
consider doing this within a trigger.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Try xp_smtp_sendmail.
TheSQLGuru
President
Indicium Resources, Inc.
"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
> regards
> Ashish
>
|||DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Hello All
I tried following solution, it is working for me. I like to know pros/cons
on this if any.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20649
Ashish
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegr oups.com...
DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||I already pointed out the cons, the same applies for this solution as
it might stale your application and the transaction with it. I
addition, the process might cancel the transaction if you execute it
within a trigger, depending on the error level coming back, The con
against a solution with a mail table and a scheduled delivery is that
it would not be send immediately, but you could implement something
like a retry whereas you cannot do this in the above procedure used
within a trigger. The best thing is to use the Service broker enabled
mail service if you are using SQL Server 2005. If you are using SQL
Server 2000 you should use a mail table which queues up mails and
sends them periodically.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Sorry, I didn't see the user state he was using SQL Server 2005. Oh, wait.
.. . he didn't! :-)
TheSQLGuru
President
Indicium Resources, Inc.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegr oups.com...
DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de

how to send mail using trigger

Hello
I have created a update grigger. I want to send a mail whenever is trigger
is fired. I tried using xp_sendmail, but it required mapi account to be
configured in server. I want to avoid this situation. Can I use cdo code in
trigger?
regards
Ashish"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
I believe so. But I wouldn't recommend it.
It'll really mess up performance.
You're better off having the trigger enter a row into a table and then
having a scheduled task go through once every X minutes sending emails.

> regards
> Ashish
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Well for SQL Server 2000 I agree, but as the Mail Service is now
enabled with SQL Server Service Broker in SQL Server 2005 you *could*
consider doing this within a trigger.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Try xp_smtp_sendmail.
TheSQLGuru
President
Indicium Resources, Inc.
"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
> regards
> Ashish
>|||DOn=B4t use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Hello All
I tried following solution, it is working for me. I like to know pros/cons
on this if any.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20649
Ashish
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegroups.com...
DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||I already pointed out the cons, the same applies for this solution as
it might stale your application and the transaction with it. I
addition, the process might cancel the transaction if you execute it
within a trigger, depending on the error level coming back, The con
against a solution with a mail table and a scheduled delivery is that
it would not be send immediately, but you could implement something
like a retry whereas you cannot do this in the above procedure used
within a trigger. The best thing is to use the Service broker enabled
mail service if you are using SQL Server 2005. If you are using SQL
Server 2000 you should use a mail table which queues up mails and
sends them periodically.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Sorry, I didn't see the user state he was using SQL Server 2005. Oh, wait.
. . he didn't! :-)
TheSQLGuru
President
Indicium Resources, Inc.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegroups.com...
DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

how to send mail using trigger

Hello
I have created a update grigger. I want to send a mail whenever is trigger
is fired. I tried using xp_sendmail, but it required mapi account to be
configured in server. I want to avoid this situation. Can I use cdo code in
trigger?
regards
Ashish"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
I believe so. But I wouldn't recommend it.
It'll really mess up performance.
You're better off having the trigger enter a row into a table and then
having a scheduled task go through once every X minutes sending emails.
> regards
> Ashish
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Well for SQL Server 2000 I agree, but as the Mail Service is now
enabled with SQL Server Service Broker in SQL Server 2005 you *could*
consider doing this within a trigger.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Try xp_smtp_sendmail.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
> regards
> Ashish
>|||DOn=B4t use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hello All
I tried following solution, it is working for me. I like to know pros/cons
on this if any.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20649
Ashish
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegroups.com...
DOn´t use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||I already pointed out the cons, the same applies for this solution as
it might stale your application and the transaction with it. I
addition, the process might cancel the transaction if you execute it
within a trigger, depending on the error level coming back, The con
against a solution with a mail table and a scheduled delivery is that
it would not be send immediately, but you could implement something
like a retry whereas you cannot do this in the above procedure used
within a trigger. The best thing is to use the Service broker enabled
mail service if you are using SQL Server 2005. If you are using SQL
Server 2000 you should use a mail table which queues up mails and
sends them periodically.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Sorry, I didn't see the user state he was using SQL Server 2005. Oh, wait.
. . he didn't! :-)
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegroups.com...
DOn´t use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

How to Send Mail to a Personal Distribution List (PDL) ?

Hi friends,

Though we can send mails to Individual addresses, I wonder what would be the Syntax to specify in the script task or Format to specify in a "To" Property of Send Mail task that I use to Send a mail to my Personal Distribution List (PDL).

Thanks

Subhash Subramanyam

You cannot do this using the SMTP Mail Task, because SMTP does not have personal lists. That is a feature of your "mail box", and comes with technologies like MAPI and mail servers. You do not want MAPI I assure you, it is horrific to use in a server environment.

Really you need to get a list created at the mail server level, or just send to all people using individual addresses.

|||

Hi Friends,

Surely I accept to the ideas of experts' reply here.

Thanks to dipendra baghel, who found a custom component "nsoftware send email task".

But solution for the Send Mail task to work is right here: When we create a PDL, The exchange server creates a valid address which can route to list of address in PDL.(say, sdfds@. sdd.com) . This address can be fed into "To" property in Send Mail Task

Thanks to my colleagues Sunil Gidwani, Dipendra Baghel and Prashanth Tiwari for their support

Thanks

Subhash Subramanyam

|||

Thanks for your reply, Darren.

I understood here that PDL is a feature of mailbox having MAPI Technology or other Mail Servers.

So I'd rather wish that in the future verison of SSIS, Send mail task should have an option to import addresses from a csv file or address book and should help us allow grouping that help customize broadcasting (Says incase of Newsletters, Alerting the Teams etc)

Thanks

Subhash Subramanyam

|||

As you mentioned in the answer to this post, groups and mailing lists are a function of the email server, not of the task sending the mail. If you want to send to a group of people via the SMTP task, you can already either set up a group email alias on your mail server, or read an external list, and use a script to put a series of email addresses in the To: property on the SMTP task.

|||

Distribution lists can be defined in a personal MAPI "mailbox", in fact it worked for that in the old SQL Mail or DTS Send Mail task, but MAPI is absolute pants in an unattended envrionment., and I cannot stress that enough. You had to install Outlook on your server to get it, and the only version that worked reliably was Outlook 2000. MAPI is owned my the office team, and they have a different focus. They effectively broke MAPI to SMTP delivery in one release, you had to leave Outlook running on the server!

So do not wish for MAPI is my message, you would regret it!

Importing a CSV for sending mail, ma I point out that we have SSIS, see the Flat File source, the recordset desination, and the for each loop. Using those components and tasks you can drive a email list off a CSV file and mail each address. It is not the simplest method, but then SSIS is not supposed to be a bulk mailer. What about featues like unsubscribe and other list management features? Use the right tool for the job I is what I mean. If bulk mail features are too much, then just use the standard mail server itself, they all support distribution lists much better than SSIS or MAPI ever will.

How to send mail throuth Trigger ?...

Hi,

I am using Trigger to send me a mail, when ever any DML operation occur on my DepartmentMaster table. my trigger is as follow.

CREATE TRIGGER reminderON DepartmentMasterFOR INSERT,UPDATE,DELETE AS EXEC master..xp_sendmail'MaryM','Don''t forget to print a report for the distributors.'

delete from DepartmentMaster where deptcode = '02'

my out express is configured with default account. but when ever i delete any row from DepartmentMaster table mail is not sent and i get following error..

Server: Msg 18030, Level 16, State 1, Line 0
xp_sendmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.

Which version of SQL Server are you using?

|||

Sql Server 2000

|||

You need to configure the SQL Mail component. Have a look at:

http://www.evogenic.com/downloads/evo_sql_mail1.pdf SQL Mail Tutorial

How to send mail through SQL Server...

Hi,
I have a requirement of sending mail through SQL Server through SMTP port... But, I dont want to configure my database server as e-mail client...
Is there any way to do it...
Thanks in advance...You can use the procedure xp_sendmail to send email. I'm not sure what you mean with 'client', but sqlserver won't take the place of outlook, pegasus mail or some other email client you have.|||For SQL Server to use xp_xxxmail it MUST be configured as a MAPI-complient client (as the poster noted). To avoid that you can use this:

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=670&lngWId=5|||I have also used vbSendMail from www.freevbcode.com with a lot of success. Again, you have to write a stored procedure to "wrap around" the DLL, but it works quite well.

Regards,

hmscott|||I would also go the CDOSYS way that rdjabarov suggests, then you don't need to worry about other things than the SQL part...

Here is another link that might be worth checking out:
http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech|||True, its hassle-free than direct SQL Mail procedures.

How to send mail programmatically from SQL2000

I tried to use the stored procedure xp_sendmail to send mail
programmatically from MSSQL2000.
But it seems to be inexistent.
Is there any other means to do that?XP_sendmail lives in the master database, so you should use the command
master..XP_sendmail
HTH, Jens Suessmeyer.

How to send mail programmatically from SQL2000

I tried to use the stored procedure xp_sendmail to send mail
programmatically from MSSQL2000.
But it seems to be inexistent.
Is there any other means to do that?Did you qualify with the master database?
EXEC master..xp_sendmail ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoy@.bishoy.com> wrote in message news:%23apBGJDQGHA.516@.TK2MSFTNGP15.ph
x.gbl...
>I tried to use the stored procedure xp_sendmail to send mail
> programmatically from MSSQL2000.
> But it seems to be inexistent.
> Is there any other means to do that?
>|||I wrote now the following code:
exec [master].[dbo].[xp_sendmail]
@.recipients = 'bishoy@.bishoy.com',
@.message = 'Test',
@.subject = 'Test SQL Mail'
and received the following error:
Server: Msg 17985, Level 16, State 1, Line 0
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
Although when I added @.user parameter, I received the following error:
Server: Msg 17981, Level 16, State 1, Line 0
xp_sendmail: Invalid parameter '@.user'|||There are plenty of issues with xp_sendmail. I have seen this in newsgroups
reported before. You'll
probably find a resolution by Google and searching the newsgroup archives.Al
so consider using
xp_smtp_sendmail instead (www.sqldev.net).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoy@.bishoy.com> wrote in message news:%23ErGM4FQGHA.3896@.TK2MSFTNGP15.p
hx.gbl...
>I wrote now the following code:
> exec [master].[dbo].[xp_sendmail]
> @.recipients = 'bishoy@.bishoy.com',
> @.message = 'Test',
> @.subject = 'Test SQL Mail'
>
> and received the following error:
> Server: Msg 17985, Level 16, State 1, Line 0
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> Although when I added @.user parameter, I received the following error:
> Server: Msg 17981, Level 16, State 1, Line 0
> xp_sendmail: Invalid parameter '@.user'
>