How to use Database mail feature in SQL Server 2000

Question

Post reply

How to use Database mail feature in SQL Server 2000

Viewing 15 posts - 31 through 45 (of 64 total)

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

Lilita Valued Member Points: 63 More actions · Answer 1

I tried it from Query window.

I always test my scripts in Query Window before job's creation.

I suppose your code must work from Query Window too.

I have permissions of administrator.

M&M SSC-Insane Points: 21700 More actions · Answer 2

Lilita (3/11/2010)
I tried it from Query window.
I always test my scripts in Query Window before job's creation.
I suppose your code must work from Query Window too.
I have permissions of administrator.

I came across this link related to your error message.

http://www.sqlservercentral.com/Forums/Topic555269-146-1.aspx

Could you please try

M&M

M&M SSC-Insane Points: 21700 More actions · Answer 3

CirquedeSQLeil (3/9/2010)
Thanks for the article. Unfortunately there are plenty of 2000 servers still in production (75% of mine are 2000). It was quite a refresher course when coming from a SQL 2005 shop.

Thank you Jason

M&M

Lilita Valued Member Points: 63 More actions · Answer 4

Hi,

I read the link you advised me.

Thank you

I tried to connect to server 2000 with sa

and run the ...msdb.dbo.sp_send_dbmail ...

I get the same error: "Could not obtain information about Windows NT group/user 'usr_testmail'"

I saw in Help :

"Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message."

May be you know how to give permission to use the profile. I think that it the point of my problem.

By the way, if i enter credentials as SA for linked server security context, sp_send_dbmail works.

M&M SSC-Insane Points: 21700 More actions · Answer 5

Lilita (3/14/2010)
Hi,
I read the link you advised me.
Thank you
I tried to connect to server 2000 with sa
and run the ...msdb.dbo.sp_send_dbmail ...
I get the same error: "Could not obtain information about Windows NT group/user 'usr_testmail'"
I saw in Help :
"Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message."
May be you know how to give permission to use the profile. I think that it the point of my problem.
By the way, if i enter credentials as SA for linked server security context, sp_send_dbmail works.

You could try this on SQL Server 2005

use msdb

go

GRANT EXECUTE ON [dbo].[sp_send_dbmail] TO [usr_testmail]

Please let us know if this works for you.

M&M

Lilita Valued Member Points: 63 More actions · Answer 6

sorrY

It didn't help

I run job and get

Executed as user: sa. Could not obtain information about Windows NT group/user 'usr_testmail', error code 0xffff0002. [SQLSTATE 42000] (Error 15404). The step failed.

tferguson SSC Veteran Points: 263 More actions · Answer 7

Neat concept, but did you consider that since you've added a linked server to connect to your 2005 instance, anyone with a login on the 2000 instance now can access your 2005 server's msdb database with db_owner permissions via the linked server? Access to a linked server cannot be controlled, so we avoid them.

M&M SSC-Insane Points: 21700 More actions · Answer 8

tferguson (3/16/2010)
Neat concept, but did you consider that since you've added a linked server to connect to your 2005 instance, anyone with a login on the 2000 instance now can access your 2005 server's msdb database with db_owner permissions via the linked server? Access to a linked server cannot be controlled, so we avoid them.

tferguson, Thanks for your comments.

This security question came up earlier in the discussion. In my company, the access control is very strict. Only people in my team can get high level access. The others from the application team etc, would be give only select access on few tables for few hours. Due to this, I did not face

security issues when this was configured on my servers.

M&M

M&M SSC-Insane Points: 21700 More actions · Answer 9

Lilita (3/15/2010)
sorrY
It didn't help
I run job and get
Executed as user: sa. Could not obtain information about Windows NT group/user 'usr_testmail', error code 0xffff0002. [SQLSTATE 42000] (Error 15404). The step failed.

Thanks, will have to check this.

M&M

saby SSCommitted Points: 1681 More actions · Answer 10

Hi all,

I have Implemented the Mail system on my sqlserver 2000.

Here as my jobs fails the mail will be send my the 2005 mailing server.

I have not used the concept of Mohammed M(the article is good) what i did is

--

Here are the steps

[Step 1]

Created two tables on 2000 and 2005 server ---called--Temp_Job_Fail

Write query on last step of the each job on sqlserver 2000

insert into Temp_Job_Fail

select b.name,b.originating_server,a.run_date,a.message,a.step_name from msdb.dbo.sysjobhistory a,msdb.dbo.sysjobs b

where a.job_id = b.job_id and

a.message like '%Error%' and

a.run_date = convert(varchar(10),getdate(),112)

and a.message not like '%DBCC printed error messages%'

(INSERT INTO [Linked Server].Databasename.DBO.Temp_Job_Fail---2005 server

SELECT * FROM Temp_Job_Fail -- 2000 server)

[STEP 2]

createD a trigger on server 2005--- to run the Job

CREATE TRIGGER trg_Insert_Temp_Job_Fail ON Temp_Job_Fail AFTER INSERT AS

BEGIN

if (SELECT NAME from DATABASENAME.dbo.Temp_Job_Fail)is not null

begin

EXEC msdb.dbo.sp_start_job @job_name = 'MailSendingForJobFailedin2000'

end

else

Print 'Issue'

END

[STEP 3]

Created a job on sqlserver 2005 which will send the mail

Jobs steps

--Step1

declare @body1 varchar(100)

set @body1 = ''Server :250''+ '' Email Alert ''

EXEC msdb.dbo.sp_send_dbmail @recipients=''MailId'',

@subject = ''Job Failed Mail Status'',

@body = @body1,

@body_format = ''HTML'',

@query = ''SELECT * from databasename.dbo.Temp_Job_Fail'' ,

@attach_query_result_as_file = 1 ;

---Step2

Truncate table DATABASENAME.dbo.Temp_Job_Fail

saby SSCommitted Points: 1681 More actions · Answer 11

Any suggestion on this will be highly appreciated.......

M A Srinivas SSC-Addicted Points: 494 More actions · Answer 12

If you want to send query result using link server approach , you need to create two way link server and provide access to databases in sql 2000 to the user in sql server 2005

saby SSCommitted Points: 1681 More actions · Answer 13

saby

SSCommitted

Points: 1681

April 3, 2010 at 12:39 am

#1145235

Only one Linkserver need to be created .....

M A Srinivas SSC-Addicted Points: 494 More actions · Answer 14

Since the query is referring to the database in SQL Seerver 2000, when I creaetd the query to call sp_db_mail in SQL 2005 through link server , It came out with databse objet not found

After creating two way link servr it worked.

Provide me how to create a query out put from SQL Server 2000 in dbmail using the specified approach

saby SSCommitted Points: 1681 More actions · Answer 15

srinivas please go for this blog which might clear ur doubts.....

http://databasemail.blogspot.com