sp_send_dbmail return Command(s) completed successfully. instead of mailsent id

SQL Server Administration
1

i have configure the database mail for send the mail creating the profile and sp_configure 'Database Mail XPs', 1; and other settings after execute the **sp_send_dbmail ** it will be return me Command(s) completed successfully. insted of Mail (Id: #####) queued.

2

post your code to call sp_send_dbmail

3

I have created a profile and account as well set my profile as a public profile

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLDbMailProfile',
@recipients = 'chirag@vision.com',
@subject = 'Employees dETAIL',
@body = 'Hello this is a test mail from Db mail.'

4

you haven't specifed the @mailitem parameter

[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

see here

5

Hello gbritton
your suggestion like below query

declare @TheMailID int
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLDbMailProfile',
@recipients = 'chirag@vision.com',
@subject = 'Employees dETAIL',
@body = 'Hello this is a test mail from Db mail.'
@body_format = 'HTML',
@mailitem_id = @TheMailID OUTPUT

Print @TheMailID

this query return me a id but mail was not received in my inbox

6

Ok that's a different problem entirely! Glad you got past the first problem though. If mail is not received, there could be many reasons, including an error in the profile (which MTA it points to), the MTA itself not set up to allow mail from the SQL server, firewall issues, etc. etc.

7

you are right i have checked the fire wall it was not an issue because of on the same server we have a sql2008 r2 and it sending a mail. i have follow some steps for configure from below link
http://www.codeproject.com/Articles/485124/Configuring-Database-Mail-in-SQL-Server

8

compare the profiles used in SQL 2008R2 and the instance you're having problems with.

9

i have created a same profile on SQL 2012

  1. Is their any kind of Service that i have to start?
  2. i have configure Database Mail XPs = 1
  3. EXECUTE msdb.dbo.sysmail_help_status_sp ; = STARTED
  4. SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' = 1
  5. EXECUTE msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail'
    queue_type = mail
    length = 0
    state = RECEIVES_OCCURRING
    last_empty_rowset_time = 2015-11-24 14:12:46.290
    last_activated_time = 2015-11-24 13:55:34.020
10

do you have access to the MTA server? If so, you can logon to it and watch the mail come in (or not) what happens with it.

11

i dont have an rights to look in to MAT server
But as i know if i am sending any mail from dbmail must be log in to sysmail_allitems.
when i am execute the query Select * from sysmail_allitems it will return me nothing

12

Do you get a valid mail item id back from the send mail proc?

13

How could i verify the return id is valid or not

14

is it a number? other than 0?

15

it's a Number like 11658
it's a increment when i execute again and again like 11659,11660.......

16

ok that looks valid. what does sysmail_help_status_sp return?

17

EXECUTE msdb.dbo.sysmail_help_status_sp ;
Result = STARTED

18

the problem has to be elsewhere then. you need to find out if the MTA is getting the mail

19

ok - thanks a lot for your Support
let me talk to our network team now,is message was received on MTA Server or not

20

i have solve the issue , some one change the sp_send_dbmail