SQL Mail not working properly

dontelimpike
dontelimpike used Ask the Experts™
on
I have a SQL 2000 Server that is sending an email for a sucessfull backup job. I have also set it to send an email  when the integrity check jobs succeeds but it does not send.  I have also set a report to be sent from the database maintenance plan (reporting tab) but it does not send.
Please advise
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Expert/Infrastructure Architect
Commented:
This will get you started:
http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/2003_Server/Q_22605236.html

If you want a workaround without outlook client, look at:
http://www.karaszi.com/SQLServer/info_no_mapi.asp

I have used Karaszi's solution with great success. But it's not supported by Microsoft. Then again, neither is SQL 2000.

/Marten
Commented:
Flag SQL Mail ...  use SMPT Mail

http://www.sqldev.net/xp/xpsmtp.htm 

remember to enable smpt mail relay for you SQL Server within exchange ...
Marten RuneSQL Expert/Infrastructure Architect
Commented:
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks for the links, there looks like some interesting ways of getting SQM Mail to do exactly what is needed.  This does not, however, answer the question of why it is sending an email for a backup but not for a db maintenance plan report
Marten RuneSQL Expert/Infrastructure Architect
Commented:
Well SQL 2000 is not supported anymore. If it's on a supported platform, some OS updates with security implications may very well mess things up for you. To help we need SQL with SP and build, OS with SP and build (and language if not EN/US), and what version of outlook (or other mime client), build sp language, that is being used.

My recommendation is to set up the alternative solution, just a simple registration and grant within SQL. It will not kill your other mail chain, but if this works. Then I would prefere it over a MIME client.

Come back with info so we may help

/Marten

Author

Commented:
Thanks Martin/TAB8,
I see what you are saying.  I hope that the windows service packs have not broken SQL Mail as it might be tricky getting the service packs out again.  I would rather not have to do to much work on the SQL Server regarding mail as the server is due to be replaced in the not to distant future.  I have got a lot of info ouof SQL Query Analyser but no more than the version for Outlook. Hope is enough...

Outlook info
Outlook 2000
SP3 (9.0.0.6627)

SQL Server info
SELECT SERVERPROPERTY('ProductLevel')
Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Dec 17 2002 14:22:05   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

SERVERPROPERTY('ProductLevel')
SP3

sp_server_info
1      DBMS_NAME      Microsoft SQL Server
2      DBMS_VER      Microsoft SQL Server  2000 - 8.00.534 (Intel X86)   Nov 19 2001 13:23:50   Copyright (c) 1988-2000 Microsoft Corporation  Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
10      OWNER_TERM      owner
11      TABLE_TERM      table
12      MAX_OWNER_NAME_LENGTH      128
13      TABLE_LENGTH      128
14      MAX_QUAL_LENGTH      128
15      COLUMN_LENGTH      128
16      IDENTIFIER_CASE      MIXED
17      TX_ISOLATION      2
18      COLLATION_SEQ      charset=iso_1 collation=Latin1_General_CI_AS
19      SAVEPOINT_SUPPORT      Y
20      MULTI_RESULT_SETS      Y
22      ACCESSIBLE_TABLES      Y
100      USERID_LENGTH      128
101      QUALIFIER_TERM      database
102      NAMED_TRANSACTIONS      Y
103      SPROC_AS_LANGUAGE      Y
104      ACCESSIBLE_SPROC      Y
105      MAX_INDEX_COLS      16
106      RENAME_TABLE      Y
107      RENAME_COLUMN      Y
108      DROP_COLUMN      Y
109      INCREASE_COLUMN_LENGTH      Y
110      DDL_IN_TRANSACTION      Y
111      DESCENDING_INDEXES      Y
112      SP_RENAME      Y
113      REMOTE_SPROC      Y
500      SYS_SPROC_VERSION      8.00.375

master..xp_msver
1      ProductName      NULL      Microsoft SQL Server
2      ProductVersion      524288      8.00.760
3      Language      1033      English (United States)
4      Platform      NULL      NT INTEL X86
5      Comments      NULL      NT INTEL X86
6      CompanyName      NULL      Microsoft Corporation
7      FileDescription      NULL      SQL Server Windows NT
8      FileVersion      NULL      2000.080.0760.00
9      InternalName      NULL      SQLSERVR
10      LegalCopyright      NULL      © 1988-2003 Microsoft Corp. All rights reserved.
11      LegalTrademarks      NULL      Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12      OriginalFilename      NULL      SQLSERVR.EXE
13      PrivateBuild      NULL      NULL
14      SpecialBuild      49807360      NULL
15      WindowsVersion      143851525      5.0 (2195)
16      ProcessorCount      2      2
17      ProcessorActiveMask      3      00000003
18      ProcessorType      586      PROCESSOR_INTEL_PENTIUM
19      PhysicalMemory      1024      1024 (1073258496)
20      Product ID      NULL      NULL




























Marten RuneSQL Expert/Infrastructure Architect
Commented:
As I see it you have two options.

Path one, apply SP3 and SP4 on the server, this is likely to fix  virtualsical toyour problem. If you have the infrastructure for it, do a physical to virtual, and put the virtual test machine in a test environment. Confirm the same issue still exists. Apply SP 3 and 4. See if it has been resolved.

Path two. Implement the SMTP fix above, easy to uninstall as if it was never there. So in my opinion you have nothing to loose with this approach. Perhaps you should consider path one, and if needed path two.

/Marten
Commented:
I would go with Path 2 also ...

SMTP mail is a sold platform that wont give you the same issues as SQL Mail in SQL 2000 .. I have seen the sending of a SQL Mail kill the SQL Server Service ...  !!  

It will take you about 1/2 an hour to rollout and test  (then you have to update all your jobs and procs that use SQL MAIL :) "  but you wont look back

I would suggest that you still test and rollout SP4 as there are some security holes that are addressed

ALSO

Start planing your migration to SQL 2005 .. great platform .. Supported ...  Database Mail "replaces SQL Mail" is solid ...   but most of all better performance  

Author

Commented:
Thank you both.  The service pack sorted out all the issues.  Good advise also on the SMTP fix - would much prefer not having Outlook installed

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial