SQL MAIL SENDMAIL

[oublie]

Hi,

Ive recently been trying to get SQLMAIL working on a SQL7 Server. I have successfully set it up to the point where i can notify administrator of failed jobs and alerts using notify so the SQL Agent mail seems to be working fine. However, if i run query analyzer and do a XP_SENDMAIL it just runs forever without any success. I looked at the process and it is ok apart from the icon indicating its waiting for a command. I dont get any errors either directly or through the error log. The command im using is a simple :-

XP_SENDMAIL 'JOEBLOGGS', 'HELLO'

ive tried this putting exec in from, go after and numerous other config. Ive even put the command in a stored proc and tried to run it that way but to no avail. ive stopped started the services, rebooted, used the xp commands to stop and start prior to sendmail command but still not joy. I i 'TEST' SQLMAIL both in AGENT and in Support Services it works fine but i cant use sendmail.

It cant be the DLL otherwise none of the mails would send so i'm completely stuck.

Any suggestions or solutions would be greatly appreciated.

BTW The Domain account and Exchange mail account are all fine and working.

 

[Corran007]

if i remember, you should put the name of the argument its going to.

EXEC xp_sendmail @recipients = 'JOEBLOGGS',
@message = 'HELLO'

It sometimes gets confused.

Other than that it looks fine to me.

 

[tb]

Support Services . . . SQL Mail . . . Properties -> Test

Successful or not?



"All I ask is the chance to prove that money can't make me happy." - Spike Milligan

 

[oublie]

Corran, tried that as well , no joy.

TB, Successful, as i said notification can automatically send emails its just xp_sendmail etc that wont play ball.

 

[oublie]

Found the Answer!

FYI, Its to do with the version on Outlook on the machine and a Microsoft security patch.

http://support.microsoft.com/default.aspx?scid=kb;en-us;263556

Outlook 2000 Client
This client can be used with SQL Server 6.5, SQL Server 7.0, and SQL Server 2000.

Microsoft Outlook 2000 supports Exchange mail and Internet mail (POP3/SMTP) and ships with Microsoft Office 2000. The MAPI model was completely changed in Outlook 2000. Instead of a complete MAPI library, Outlook 2000 uses only a MAPI stub (128 KB in size). This module points to the correct MAPI library file to use, based on the language installed on the Windows NT computer (for example, the Msmapi32.dll file in the \Program Files\Common Files\System\Mapi\1033\NT folder). Replacing the Mapi32.dll stub with a different version breaks Outlook 2000 and you will need to run Fixmapi.exe to correct the problem.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
195795 OL2000: (CW) Troubleshooting Outlook Configuration Problems


Outlook 2000 also has to be installed with the Corporate or Workgroup option. To verify that you have installed Outlook 2000 properly, on the Outlook 2000 Tools menu, click Options. Click the Mail Services tab or the Mail Delivery tab, depending on how Outlook is currently configured. You will see a Reconfigure Mail Support command button. If you click Reconfigure Mail Support, a dialog box opens. If the Corporate or Workgroup option is not selected, click to select it and Outlook 2000 will reconfigure itself without requiring you to remove and reinstall the program. However, you must re-create your mail profile.

NOTE: If you do make a change and you select the Corporate or Workgroup option, you must restart SQL Server for the changes to take effect.

After you install Outlook 2000, there should be at least two Mapi32.dll files, one dated 1/14/1999, version 1.0.2536.0 that is in the \Winnt\System32 directory (for backward compatibility with mail-enabled applications like SQL Mail but is not used by Outlook 2000) and another file dated 1/26/1999, version 1.0.2518.0 in the \Program Files\Common Files\System\Mapi\1033\NT directory (used by Outlook 2000). If you do not have a Mapi32.dll file in the \Winnt\System32 directory, Outlook 2000 may work properly but you will not be able to start SQL Mail. If that happens, copy the Mapi32.dll file from the \Program Files folder to the \Winnt\System32 folder.

In response to the threat posed by e-mail worms, Microsoft released a security patch for Outlook 2000 that notifies the user when a non-Outlook program attempts to send mail and requests the user to indicate whether this should be allowed by clicking Yes or No. Because SQL Mail is run through the SQL Server service, this security pop-up screen is sent to the virtual desktop and is never seen by a user. Because mail will not be sent pending user interaction, the end result is that SQL Mail will hang.

This behavior can be seen with SQL Mail for SQL Server 6.5 or SQL Server 7.0 because they make a simple MAPI connection to the mail client and this will trigger the security pop-up screen. Because SQL Mail for SQL Server 2000 makes an extended MAPI connection, the security pop-up screen is bypassed.

If your version of Outlook 2000 is 9.0.0.4201 or later (as seen in the "About Microsoft Outlook" box in Outlook 2000), you have the Outlook security update installed. Contact your Exchange administrator to see if it is possible to relax the default security constraints.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
263297 OL2000: Admin Info About the Outlook E-mail Security Update

 

[Wyldcard9]

This thread was a great deal of help to me, the sample code worked great on my SQL Server. I looked up xp_sendmail in the help files, and have a question about the attachments. Can you zip and/or password protect the attachment? Does that need to be done before the file is attached in the email?

 

[mrdenny]

xp_sendmail can't compress the attachment. You would have to export the data and compresses it your self ahead of time.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Wyldcard9]

okay.

Thanks