CODEWITHSUNDEEP
sql-serversp-send-dbmail
user3848036
user3848036

Reputation: 179

sql server sp_send_dbmail

I am using SQL Server's sp_send_dbmail stored procedure to send mail via the database.

But when I execute that procedure it's shutting down the db mail procedure. I tried to start it again by sysmail_start_sp. But it's again shutting down in 7-8 seconds.

I tried with the code below:

EXEC msdb..sp_send_dbmail @profile_name = 'Test',
@recipients = '[email protected]',
@subject = 'test mail',
@body_format = 'HTML',
@body = 'test mail',
@from_address = '[email protected]'

When I tried to check the error log I found the below error:

Message:

The read on the database failed. Reason: The error description is 'Whitespace is not allowed at this location.'.Data: System.Collections.ListDictionaryInternalTargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.QueueItem GetQueueItemFromCommand(System.Data.SqlClient.SqlCommand)HelpLink: NULLSource: DatabaseMailEngineStackTrace Information===================

Also when I checked sql email log history I found this error:

Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail.

Upvotes: 3

Views: 21306

Answers (3)

Servet bulut
Servet bulut

Reputation: 1

This works,

USE msdb ;  
GO  
  
EXECUTE dbo.sysmail_start_sp ;  
GO

visit there

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-start-sp-transact-sql?view=sql-server-ver16

Upvotes: 0

user7488971
user7488971

Reputation:

Whenever you try to send Test mail from Database mail; it throws below error message:

Msg 14641, Level 16, State 1, Procedure sp_send_dbmail, Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail.

  1. First of all make sure that Service Broker Message Delivery in Databases is enabled by executing the following command in SSMS:

    SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'

    If the result of above query is 0, then activate the service broker.

    Activating Service Broker allows messages to be delivered to the database. A Service Broker endpoint must be created to send and receive messages from outside of the instance.

    To activate Service Broker in a database use the following command:

    USE master ;
GO

ALTER DATABASE DatabaseName SET ENABLE_BROKER ;
GO

  2. If the Service Broker is enabled then confirm whether Database Mail is enabled or not by executing below queries in SQL Server Management Studio:

    sp_configure 'show advanced', 1
GO

RECONFIGURE
GO

sp_configure
GO

    If the result set shows run_value as 1 then Database Mail is enabled.

  3. If the Database Mail option is disabled then run the below queries to enable it:

    sp_configure 'Database Mail XPs', 1; 
GO

RECONFIGURE;
GO

sp_configure 'show advanced', 0; 
GO

RECONFIGURE;
GO

  4. Once the Database Mail is enabled then to start Database Mail External Program use the below mentioned query on msdb database:

    USE msdb ;       
EXEC msdb.dbo.sysmail_start_sp;

  5. To confirm that Database Mail External Program is started, run the query mentioned below :

    EXEC msdb.dbo.sysmail_help_status_sp;

  6. If the Database Mail external program is started then check the status of mail queue using below statement:

    EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';

Upvotes: 13

Rupesh Boorugu
Rupesh Boorugu

Reputation: 1

There are few things to troubleshoot, firstly check to make sure database Mail is enabled by executing the following 

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'

If the result of above is 0, activate the service broker by following this guide

If the result of above is 1, then check the status of Database Mail, execute the following statement:

EXECUTE dbo.sysmail_help_status_sp

To start Database Mail in a mail host database, run the following command in the msdb database:

EXECUTE dbo.sysmail_start_sp

Upvotes: 0

Related Questions