SQL Server Performance Forum – Threads Archive
SQLAgentMail
Hi- I am trying to configure SQLAgentMail with SQL server 2000 i have done it before but the only different was back then i was using exchange server now i we don’t have exchange any more we use lotus notes i have gathered from a bit of a research on the net that microsoft does not support Notes for SQLMail. the problem i am facing is that i am using outlook 2002 with a smtp/pop mapi profile the sqlagentmail can use this profile but the emails stay in the outlook 2002. when i login in outlook2002 to check the sent folder for history all of the emails get released and sent out only then. i read somewhere that i have to leave the outlook client 2002 open on the server for SQLMail make sense but what about SQLAgentmail we can’t do that for SQLAgentmail. is this true that using outlook 2000 with sqlserver 2000 will not require SQLagentMail to have outlook client keep running on the server.Please help Thanks
Ajjee
SQL Mail establishes an extended MAPI connection with a mail host, while SQLAgentMail establishes a separate extended MAPI connection. Both SQL Mail and SQLAgentMail can connect with Microsoft Exchange Server, or a Post Office Protocol 3 (POP3) server. http://www.blat.net/194/ – using BLat
http://support.microsoft.com/default.aspx?scid=kb;en-us;263556&sd=tech – about SQLmail setup. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thanks for your reply but i am still confused: 1- when i try to use outlook 2002 profile with SQLagentMail it sents out the emails but they stay in outlook. 2- when i try to start the outlook 2002 client at the same time it says ".pst file is inuse" it is not possible to start both SQLserverAgent Mail and outlook 2002 with the same MAPI profile then how can i leave the outlook running???????
You should not be using a .pst file. You need to have all email stored on the server. Otherwise, you can run into ALL kinds of issues. If you change that, you should be able to open outlook while SQLMail is "running". MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Thanks for your message. derrickleggett well you in other words saying to use Exchange server,
I mentioned earlier that we dont have exchange server is there anyother method i can use??????
If you can avoid exchange/ outlook then below procedure can help.. create this stored proc and call it.. CREATE PROCEDURE [dbo].[sp_send_cdontsmail]
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@CC varchar(100) = null,
@BCC varchar(100) = null
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate ‘CDONTS.NewMail’, @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, ‘From’,@From
EXEC @hr = sp_OASetProperty @MailID, ‘Body’, @Body
EXEC @hr = sp_OASetProperty @MailID, ‘BCC’,@BCC
EXEC @hr = sp_OASetProperty @MailID, ‘CC’, @CC
EXEC @hr = sp_OASetProperty @MailID, ‘Subject’, @Subject
EXEC @hr = sp_OASetProperty @MailID, ‘To’, @To
EXEC @hr = sp_OAMethod @MailID, ‘Send’, NULL
EXEC @hr = sp_OADestroy @MailID
EXEC sp_send_cdontsmail ‘[email protected]‘,’[email protected]‘,’Test of CDONTS’,’It works…’ Deepak Kumar
Deepak Thanks for your reply, But still no one has answered the question why the emails are not leaving outlook????
inorder for emails to leave the server i have to stop MS SQL agent open outlook mail profile and then all the emails leave the server whey cant they just go without me doing all of this i know i can do it with the exchange server and a domain account but why does any not say this that SQL2000 agent Mail with an smtp/POP3 account are not compatible
even this don’t help ?????
Q2: Why did SQL Mail stop working after I upgraded Microsoft Outlook or applied the Outlook security patch? A2: An Outlook 2000 E-Mail Security Update released in August 2001 prevents the spread of e-mail worms that propagate by sending themselves to everyone in your address book. The security patch detects when a non-Outlook program sends an e-mail and a dialog box with this message appears:
A program is trying to automatically send e-mail on your behalf. Do you want to allow this?
You must click Yes if you want to send the mail. SQL Mail for SQL Server 6.5 and SQL Server 7.0 is treated as a non-Outlook program that is attempting to send mail. However, because SQL Mail is called in the SQL Server service, the dialog box is sent to the "virtual desktop" of the SQL Server service and does not appear on the server. Because no user interaction occurs, SQL Mail stops responding. The security features can be relaxed with the assistance of your Exchange administrator; however, a better solution is to upgrade to SQL Server 2000. SQL Mail in SQL Server 2000 makes an extended MAPI connection, which bypasses the security dialog box. For additional information on the Outlook security patch, click the article number below to view the article in the Microsoft Knowledge Base:
262631 OL2000: Information About the Outlook E-mail Security Update
Microsoft Outlook 2002 includes the security features found in the Outlook 2000 security update. If you are already using SQL Server 2000 and SQL Mail stops working after you upgrade to Outlook 2002, you are probably using an Internet Mail server. You could configure previous versions of Outlook so that you could send mail from a Microsoft Windows service. To improve the performance when working with non-Exchange mail, the mail spooler runs in the process space of Microsoft Outlook 2002, which requires the Outlook client to be open at all times when you send mail to a non-Exchange server. If you do not want to leave the Outlook 2002 client running on your server at all times (requiring you to be logged on to the server), you must use an Exchange server as your mail server or roll back to a previous version of Microsoft Outlook.
GOT IT After Hours and hours of research on the internet Finally got the answer.
Please donot use OUTLOOK 2002 with SQLagent/SQL Mail user Outlook 2000 instead and all of you problems will be solved….. Thanks for the help from all of you.
]]>