Setting up Database mail

Question

Post reply

Setting up Database mail

Infamous

Valued Member

Points: 68
More actions
April 16, 2010 at 3:06 am

#142648

(I'm not sure if this is the right place to post this but here goes :P)

I know this has been posted lots of times here, but this is the problem i'm facing :

I want to notify a few mail addresses using Database mail and operators. I'm using SQL server 2005 and this is what my version check returns :

Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Created a Database mail and tested it, sends mail perfectly. But when i create a operator and set it to notify on job completion, it doesn't work. I get the error message :

The job succeeded. The Job was invoked by User. The last step to run was step 1 (CB Sync). NOTE: Failed to notify 'new_notify' via email.

[264] An attempt was made to send an email when no email session has been established

And yes i have tried most or all of suggestions , enabled Database mail profile in SQL server agent , restarted my agent million times so far :angry: . But i still keep getting the same message, no matter what port i use (25,465,587 ). using gmail account btw

Any suggestions would be helpful

Viewing 7 posts - 1 through 6 (of 6 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Bhuvnesh

SSC Guru

Points: 59351

April 16, 2010 at 7:32 am

#1152827

Enable DatabaseMail on the SQL Server Agent -> properties -> Alert System (check the checkbox), and then restart SQL Server Agent.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Infamous Valued Member Points: 68 More actions · Answer 2

Infamous

Valued Member

Points: 68

April 16, 2010 at 8:45 am

#1152883

Did that already. Restarted after that too. Its still not working 🙁

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Check following things:

1. did see any new entries in msdb.sysmail_allitems ?

2. Check Database Mail log

3. What service pack you have ?

4. What result you got from it ? use msdb



go

set nocount on

declare @instname nvarchar(100)

declare @instnum nvarchar(20)

declare @registrypath nvarchar(1000)

set @instname = isnull(convert(nvarchar(100), serverproperty('InstanceName')), N'MSSQLSERVER')

select @instname instname

create table #registry

(KeyName nvarchar(100),

 value nvarchar(1000))

insert into #registry

EXEC master.sys.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instname

select @instnum = value from #registry

set @registrypath = N'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instnum + '\SQLServerAgent'

select @registrypath registrypath

delete #registry

insert into #registry

exec xp_regread N'HKEY_LOCAL_MACHINE', @registrypath, N'DatabaseMailProfile'

select j.notify_level_email, o.name as "Operator Name", o.email_address, p.name as "Profile Name", a.name as "Account Name", a.email_address

from sysjobs j join

	sysoperators o on j.notify_email_operator_id = o.id cross join

	(sysmail_profile p join

	sysmail_profileaccount pa on p.profile_id = pa.profile_id join

	sysmail_account a on pa.account_id = a.account_id )join #registry r on p.name = r.Value)

where j.name = 'Your Job name here'

drop table #registry

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Infamous Valued Member Points: 68 More actions · Answer 4

1. I have service pack 2. So rules out the bug issue in SQL 2005

2. Checked mail log , it says relay error.

3. All test mails from database mail has been sent.I checked from msdb.sysmail_allitems. My issue is not in database mail, its configured properly, the issue comes up when i use an operator to send notifications on scheduled jobs.

3. As for your code , i'll check it out tomorrow and get back to you. Not at work place today. Thanks for the help by the way.

Infamous Valued Member Points: 68 More actions · Answer 5

Infamous

Valued Member

Points: 68

April 19, 2010 at 5:36 am

#1153713

This is what my version check returns :

Infamous Valued Member Points: 68 More actions · Answer 6

Oh wait....I missed the details, SQL server is not service pack 2, its RTM. My os is service pack 2. Maybe thats the problem. I'll ask the system admin to update to SP2. Thanks , that should solve the issue.

Release Sqlservr.exe

RTM 2005.90.1399

SQL Server 2005 Service Pack 1 2005.90.2047

SQL Server 2005 Service Pack 2 2005.90.3042

SQL Server 2005 Service Pack 3 2005.90.4035