Certain basic troubleshooting techniques can be used to diagnose and resolve most SQLMail problems. Typically, errors in starting a mail session or sending mail fall into two categories: permissions problems when accessing Shared File System (SFS) shares being used as post offices, and registry setup problems.
Check the following, preferably in the order given here:
This is the Registry key used by SQLMail:
HKEY_USERS
\.DEFAULT
\Software
\Microsoft
\Mail
If this Registry key is not set up properly, ensure that you are able to start a mail session using the account you are currently logged in under, and then rerun SQL Setup or SQL Enterprise Manager, ensuring that the "Copy SQLMail Configuration from Current User Account" is checked.
Access to the file share can be tested by using the xp_cmdshell extended stored procedure. For instance, the following commands can be used to test read and write access to the server:
xp_cmdshell "dir \\mailserver\poshare"
and
xp_cmdshell "echo 'hello' > \\mailserver\poshare\test.txt"
Note If the post office share is on an NTFS drive, permissions must be set for both the network share and the file system.
\\HKEY_LOCAL_MACHINE
\System
\CurrentControlSet
\Services
\LanmanServer
\Parameters
Name: NullSessionShares
Type: REG_MULTI_SZ
Data: COMCFG
A Registry key can be added by using the Registry Editor (REGEDT32.EXE). For information, see the online Help for the Registry Editor. You can also set the SQL Server service (MSSQLServer) to run under a Windows NT user account, which does not require the NullSessionShares Registry entry.