Sql Mail Error

Question

Post reply

Sql Mail Error

kpowers

SSC Veteran

Points: 279
More actions
October 9, 2002 at 12:07 pm

#58397

I'm having a hell of a time trying to figure out what went wrong. Up until yesterday SQL Mail was running fine. Now it it fails everytime I try to start it.

OS: NT 4 Server SP7

SQL Server 7 SP?

Mail: Microsoft Windows NT Mail

When I manually start SQL Mail I get the following message.

Microsoft SQL-DMO (ODBC SQLState: 42000)

Error 17952: Failed to start SQL Mail session. Check error log ...

In the error log it wirtes:

Starting SQL Mail session...

2002-10-09 12:33:59.57 ods Error: 17903, Severity: 18, State: 1

2002-10-09 12:33:59.57 ods MAPI login failure..

2002-10-09 12:33:59.57 ods Error: 17951, Severity: 18, State: 1

2002-10-09 12:33:59.57 ods Failed to start SQL Mail session..

I can go into Outlook and send and recieve mail. I can even go into SQL Mail properties and test and it says it works fine. I have no idea what else to do. Help!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

Allen Cui-55137

SSC Guru

Points: 51650

October 9, 2002 at 2:50 pm

#438194

When you login into Outlook, which NT account did you use? Do you use same account to start SQL Server? The SQL Mail test only check the mail profile and it does not send out email.

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 728159

October 9, 2002 at 2:58 pm

#438196

Check and see if something may have changed on the Exchange server.

Steve Jones

sjones@sqlservercentral.com

http://qa.sqlservercentral.com/columnists/sjones

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 3

17903 is a connectivity error, generally meaning a bad password or username. The times I've seen it, it turned out to be the agent login didn't have rights to the exchange profile. The last time I saw it was when the IT guys were changing us over to active directory, and the mail server was changed over one day prior to the sql servers. Happened on every server using mail.

kpowers SSC Veteran Points: 279 More actions · Answer 4

Allen,

I use the admin account for everything on my server. After I could not get it running I have pretty much gone through the SQL Mail steps 2, 3 times. I did get the SQLAgentMail working, but still not SQL Mail.

Steve,

I'm using Windows NT Mail with Outlook 98 then using Internet Mail(POP3) to send to our Groupwise server.

Ramesh Old Hand Points: 375 More actions · Answer 5

Ramesh

Old Hand

Points: 375

October 10, 2002 at 12:00 am

#438214

May be MSSQLServer and SQLServerAgent services are using the System Account.

check that these accounts are using domain accounts.

--Ramesh

Sydney

Edited by - ramesh on 10/10/2002 12:02:07 AM

Ramesh

MikeOzanne Valued Member Points: 73 More actions · Answer 6

I also have had this problem a lot recently.

I do not know why it happens but the only way I have found make it work again is

Stop SQL Server and SQL Agent in Services and set then to Manual, reboot the SQL Server and start the Services manually again, I know this is not a fix but it works for me. I agree the accounts must be using Domain Accounts. If any one else know a fix I would be pleased to find out.

Mike (Guernsey, Channel Isands)

Greg M. Lucas Right there with Babe Points: 782 More actions · Answer 7

We got so fed up with SQL Mail/Outlook problems, especially on clusters, that we now always install SMTP service on all database servers and use CDONTS rather than Outlook.

Funnily enough - even when SQL Mail falls over regularly, SQL Agent mail always seems to carry on working.

There are numerous examples of using CDONTS mail around - alternatively I can provide the stored procedure we use if anyone wants it.

. . Greg

Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa

jackdonnell SSC Veteran Points: 273 More actions · Answer 8

jackdonnell

SSC Veteran

Points: 273

October 10, 2002 at 9:54 am

#438270

Check also if your Exchange inbox has been moved.. you will have to do a full reboot ..of the server and make sure your profile points to the correct exchange server

kpowers SSC Veteran Points: 279 More actions · Answer 9

kpowers

SSC Veteran

Points: 279

October 10, 2002 at 10:49 am

#438279

Well it works! I really have no idea why. I rebooted the server this morning and SQL Mail came right up. No clue. Thanks for all the feedback, it's greatly appreciated.

J.J. Cartledge Newbie Points: 3 More actions · Answer 10

Good evening,

I am having a problem with SQL Mail. I am running Win 2003 and SQL 2000. I have installed Office XP in order to use SQL Mail. Have ever after reading several articles which including leaving Outlook open and configuration of SQL Mail. I am still recieving an error which states xp test mapi profile: failed with mail error x800-40111. I am real excited about using this feature so any advice would be greatly appreciated.

Thanks

J.J.

philcart SSC-Forever Points: 47794 More actions · Answer 11

The error you're getting is a MAPI Login failure. Do you specifically have to use SQL Mail functioanlity? eg: xp_sendmail, xp_readmail

If not I'd suggest you save yourself some headaches and use xp_smtp_sendmail from http://www.sqldev.net

Removes the requirement of Outlook installed and running on the server. It also avoids issues that arise when your Exchange server is not available.

--------------------
Colt 45 - the original point and click interface

Daljit-208782 SSChasing Mays Points: 623 More actions · Answer 12

I agree with Phill Carter, as the SQL Mail maintenace is very time consuming. Some times is hangs and we need to restart the SQL Server Agent. Some times restarting SQL server agent do not works and we get a error 'mail thread already running' for that we need to kill that thread and restart the mail services.

And some time back, it was a routine activity for our DBA team to manage SQL Mail. So we replace SQL Mail with xpsmptp jobs.

there are some limitations with xpxmtp mails like sending attachments or results so we overcome that programmatically. I am enclosing one example for your reference:

TRUNCATE TABLE T_SPACE

USE DBA

GO

ALTER PROCEDURE PRC_DBSPACE

AS

/*

*********************************************************************************************

NAME: DBSPACECALC

DESCRIPTION:

GATHER THE DATA AND LOG SPACE FOR ALL DATABASES ON THE SYSTEM

AND INSERT THE INFORMATION INTO T_SPACE. THE FOLLOWING DATABASES

ARE NOT ADDED TO T_SPACE:

PUBS

NORTHWIND

MODEL

TEMPDB

**********************************************************************************************

*/

SET NOCOUNT ON

DECLARE @ERR INT

SELECT @ERR = 0

/*

CREATE THE TEMP TABLES TO HOLD THE RESULTS OF DBCC

COMMANDS UNTIL THE INFORMATION IS ENTERED INTO

T_SPACE.

*/

CREATE TABLE #LOGSPACE (

DBNAME VARCHAR( 100),

LOGSIZE FLOAT,

PRCNTUSED FLOAT,

STATUS INT

)

CREATE TABLE #DATASPACE

( FILEID INT,

FILEGRP INT,

TOTEXT INT,

USDEXT INT,

LFILENM VARCHAR( 100),

PFILENM VARCHAR( 100)

)

/*

GET THE LOG SPACE

*/

INSERT INTO #LOGSPACE

EXEC ('DBCC SQLPERF( LOGSPACE)')

INSERT T_SPACE

SELECT DBNAME,

LOGSIZE,

(LOGSIZE * (PRCNTUSED/100)),

(1 - ( PRCNTUSED/ 100))*100,

DBNAME,

'LOG',

GETDATE()

FROM #LOGSPACE

WHERE DBNAME NOT IN

( 'MODEL',

'TEMPDB',

'PUBS',

'NORTHWIND')

-- GET THE DATA SPACE USE A CURSOR TO LOOP THROUGH THE RESULTS FROM DBCC

-- SINCE YOU HAVE TO RUN THIS COMMAND FROM EACH DATABASE WITH A USE COMMAND.

DECLARE @DB CHAR( 40), @CMD CHAR( 500)

DECLARE DBNAME CURSOR

FOR SELECT DBNAME FROM #LOGSPACE

OPEN DBNAME

FETCH NEXT FROM DBNAME INTO @DB

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @CMD = 'USE ' + RTRIM( @DB) + ' DBCC SHOWFILESTATS'

INSERT #DATASPACE

EXEC( @CMD)

IF @DB NOT IN

( 'MODEL',

'TEMPDB',

'PUBS',

'NORTHWIND'

&nbsp

INSERT T_SPACE

SELECT SUBSTRING( LFILENM, 1, 20),

((CAST( TOTEXT AS NUMERIC( 10, 4))* 32) / 512),

((CAST( USDEXT AS NUMERIC( 10, 4))* 32) / 512),

((CAST( TOTEXT - USDEXT AS NUMERIC( 10, 4))* 32) / 512),

@DB,

'DATA',

GETDATE()

FROM #DATASPACE

FETCH NEXT FROM DBNAME INTO @DB

DELETE #DATASPACE

END

DEALLOCATE DBNAME

/*

DROP THE TEMPORARY TABLES

*/

DROP TABLE #LOGSPACE

DROP TABLE #DATASPACE

/*

REMOVE OLD INFORMATION FROM THE T_SPACE TABLE.

*/

DECLARE @LOGI VARCHAR(50),

@TOT NUMERIC(10,4),

@USED NUMERIC(10,4),

@PER NUMERIC(10,4),

@DATAB VARCHAR(30),

@TYP VARCHAR(10),

@ENTRYDT VARCHAR(11),

@STR VARCHAR(8000),

@DT VARCHAR(11),

@SERVER VARCHAR(30)

SET @SERVER = @@SERVERNAME

SET @dt = GETDATE()

DECLARE DATA CURSOR FOR

SELECT LNM, TOT, USED, PRCNT, DB, TYP, ENTRYDT FROM DBA..T_SPACE WHERE ENTRYDT > @dt ORDER BY DB ASC

SET @STR = '<HTML><H2>Database Size Information for :' + @SERVER +'</H1>

<TD WIDTH="20%"> Database </TD>

<TD WIDTH="20%"> Logical Device Name</TD>

<TD WIDTH="10%"> Total</TD>

<TD WIDTH="10%"> Percentage</TD>

<TD WIDTH="10%"> Date</TD>'

OPEN DATA

FETCH DATA INTO @LOGI, @TOT, @USED, @PER, @DATAB, @TYP, @ENTRYDT

WHILE @@FETCH_STATUS = 0

BEGIN

SET @STR = @STR + '<TR><TD> ' + LTRIM(RTRIM(@DATAB)) + '</TD>' +

'<TD> ' + LTRIM(RTRIM(@LOGI)) + '</TD>' +

'<TD> ' + CONVERT(VARCHAR,@TOT) + '</TD>' +

'<TD> ' + CONVERT(VARCHAR,@USED) + '</TD>' +

'<TD> ' + CONVERT(VARCHAR,@PER) + '</TD>' +

'<TD> ' + LTRIM(RTRIM(@TYP)) + '</TD>' +

'<TD> ' + LTRIM(RTRIM(@ENTRYDT)) + '</TD></TR>'

FETCH DATA INTO @LOGI, @TOT, @USED, @PER, @DATAB, @TYP, @ENTRYDT

END

CLOSE DATA

DEALLOCATE DATA

SET @STR = @STR + ' </TABLE>

If you are having any questions related to SQL Server Issues:

<A href="mailto:MAILTO:<A HREF="MAILTO:somebody@some.com?SUBJECT=Mail related to SQL Mailer">Daljit S. Saini</A></HTML>'

EXEC MASTER.DBO.XP_SMTP_SENDMAIL

@FROM = 'somebody@some.com',

@FROM_NAME = N'SQL Mailer',

@CC = 'somebody@some.com',

@TO = 'somebody@some.com',

@SUBJECT = 'Database Space Information',

@TYPE = 'TEXT/HTML',

@MESSAGE = @STR

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

now we are on process to test notification services

Amy Boyd SSC Journeyman Points: 92 More actions · Answer 13

Amy Boyd

SSC Journeyman

Points: 92

August 31, 2010 at 1:06 pm

#1214570

Thanks, Mike. I found this post very helpful in resolving why SQL Mail wouldn't start on my SQL 7.0 server.

--Amy