?
Solved

SQL2005 : Database Mail from DTS Package

Posted on 2008-11-11
7
Medium Priority
?
562 Views
Last Modified: 2013-11-30
I need to have the e-mail alert sending out from DTS Package from SQL2005 Server; but, found can not select e-mail profile from DTS package - there is no e-mail profile can be selected from the selection list.

I have checked the following:
1. From Server Surface Area configurateion - the Database Mail store procedure is enabled;

2. Try to send Test E-mail from Database Mail -
   a. the mail is in the queue with "unsent" status
        SELECT * FROM msdb.dbo.sysmail_allitems
       

   b. and, it can not be found from the log
        SELECT * FROM msdb.dbo.sysmail_log

Please advise ...

0
Comment
Question by:dannytsao
  • 3
  • 3
7 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 22937576
Go on the SQL Server properties under Enterprise Manager, the Server Settings tab, SQL Mail section there and hit Change in case there is nothing there. Pay attention to the text:
"Enter a valid MAPI profile name. This profile name must be configured and tested for the startup user account of the MSSQLServer service." When trying to put an invalid account there, it will give you more valuable information:

"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."

i think it won't work with an HTTP account.
0
 

Author Comment

by:dannytsao
ID: 22937658
Enterprise Manger ???
We are running with SQL2005; I guess you are referreing with SSMS ? If it is, SQL Mail  is disabled in SQL2005, and replaced by database mail; shoud I enable it?
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 22937668
whoops sorry, yes enable
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:dannytsao
ID: 22937796
I hit waring message when I tried to enable SQL Mail :
"SQL Mail does not work with 64bit versioin of SQL Server"... ; can not enalbe SQL Mail.
0
 
LVL 9

Expert Comment

by:miron
ID: 22937850
did you try to see helpful links, hope these will help
-- cheers

vbscript to test ability to send email using SMTP server of your choice
    http://support.microsoft.com/kb/286431

user apparently experienced similar trouble with database mail
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2872068&SiteID=1 

Troubleshooting Database Mail MSDN article
    http://msdn.microsoft.com/en-us/library/ms188663.aspx
0
 
LVL 31

Accepted Solution

by:
James Murrell earned 375 total points
ID: 22937868
just seen this http://sql-server-performance.com/Community/forums/p/22045/126977.aspx#126977


Service Pack 1 fixed this for DB Mail. I was just looking at http://blogs.msdn.com/sanchan/archive/2006/04/23/581639.aspx which says this.

 See http://msdn2.microsoft.com/en-us/library/ms187891.aspx for SQL Mail to DB Mail conversion, this is what you want to use in SQL 2005. General how-to setup database mail: http://msdn2.microsoft.com/en-us/library/ms188298.aspx
0
 

Author Comment

by:dannytsao
ID: 22992228
Dear All,

I had our Coopreate IT support to help it out. The e-mail in the DTS Package, migrated from SQL2000, is referred to Outlook exchange e-mail account. It won't call database mail, unless change the DTS package.

At the end, I need to creat an outlook profile so that it can be selected from the DTS e-mail drop-down selection.  So, it solved this part of the issue.

But, on the other hand, the testing e-mail from database mail still can not fire up; unless I dobule click "databasemail90.exe".

I have gone thru a lot of posts but still can not slove this issue.

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question