Send auto Email from SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Send auto Email from SQL Server

Author

Topic

atuljadhavnetafim
Starting Member

25 Posts

Posted - 2015-02-03 : 00:01:49

Dear Expert

I have below code which send email from SQL Table "testing$" with use of store procedure

but I am not getting any email, please check where is the error

and if I run below code as query then it give me expected result but mail not received,

[CODE="other"]

DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @recipients nvarchar(max)
DECLARE @profile_name nvarchar(max)
DECLARE @body_format nvarchar(max)

SET @tableHTML =
N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd) { background-color:#eee; }
tr:nth-child(even) { background-color:#fff; }
</style>'+
N'<H3><font color="Red">All Rows From [VCSQL].[CreditControl].[Testing]</H3>' +
N'<table id="box-table" >' +
N'<tr><font color="Green"><th>Code</th>
<th>Year</th>
<th>Month</th>
<th>Customer</th>
<th>Collection</th>
</tr>' +
CAST ( (

SELECT td = CAST([Code] AS varchar(100)),'',
td = [Year],'',
td = [Month],'',
td = [Customer] ,'',
td = [Collection]
FROM [CreditControl]..[Testing$]
ORDER BY [Code]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'

EXEC TEST1
@recipients= 'atul.jadhav@netafim-india.com',
@profile_name = 'mssqlmail',
@subject = 'hi',
@body_format = 'html' ;

[\CODE]

Store procedure codding

[CODE]

USE [CreditControl]
GO
/****** Object: StoredProcedure [dbo].[TEST1] Script Date: 2/2/2015 2:39:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TEST1]
@recipients [nvarchar](4000),
@profile_name [nvarchar](4000),
@subject [nvarchar](4000),
@body_format [nvarchar](4000)
AS
BEGIN
SELECT *
FROM Testing$
END

[\CODE]

Atul Jadhav

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-02-03 : 01:45:56

Could you:
1) check to see the database mail is configured and you can send a test email?
2)What errors - if any - can you report?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

atuljadhavnetafim
Starting Member

25 Posts

Posted - 2015-02-03 : 02:36:26

Dear Sir, thanks for reply,

I am receiving test email from server

and when I run this code in query window then it give me result but not receiving thru email

Atul Jadhav

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-02-03 : 06:10:43

SQL Server has its own proc for sending mail, EXEC sp_send_dbmail. Try changing the stored proc for the code below, your proc is not doing much, you could try adding theze 2 lines before EXEC TEST1, although I cant test:

USE MSDB
Go
EXEC sp_send_dbmail
EXEC TEST1

DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @recipients nvarchar(max)
DECLARE @profile_name nvarchar(max)
DECLARE @body_format nvarchar(max)

SET @tableHTML =
N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd) { background-color:#eee; }
tr:nth-child(even) { background-color:#fff; }
</style>'+
N'<H3><font color="Red">All Rows From [VCSQL].[CreditControl].[Testing]</H3>' +
N'<table id="box-table" >' +
N'<tr><font color="Green"><th>Code</th>
<th>Year</th>
<th>Month</th>
<th>Customer</th>
<th>Collection</th>
</tr>' +
CAST ( (

SELECT td = CAST([Code] AS varchar(100)),'',
td = [Year],'',
td = [Month],'',
td = [Customer] ,'',
td = [Collection]
FROM [CreditControl]..[Testing$]
ORDER BY [Code]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'

Use msdb
Go
EXEC sp_send_dbmail
@profile_name='mssqlmail',
@recipients='atul.jadhav@netafim-india.com',
@subject='hi',
@body_format='html',
@importance = 'High',
@query = 'Use Testing$
Go
SELECT *
FROM Testing$',
@attach_query_result_as_file = 0;
Go

[\CODE]

Store procedure codding

[CODE]

USE [CreditControl]
GO
/****** Object: StoredProcedure [dbo].[TEST1] Script Date: 2/2/2015 2:39:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TEST1]
@recipients [nvarchar](4000),
@profile_name [nvarchar](4000),
@subject [nvarchar](4000),
@body_format [nvarchar](4000)
AS
BEGIN
SELECT *
FROM Testing$
END
[\CODE]

We are the creators of our own reality!

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-02-04 : 01:51:30

if you want to add COPY and BCC for the EXEC sp_send_dbmail option read:
http://www.sqlserver-dba.com/2011/07/sql-sp_send_dbmail-and-bcc.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

atuljadhavnetafim
Starting Member

25 Posts

Posted - 2015-02-04 : 03:36:17

Dear Sir

I am not able to find this store procedure in said data base, how can I installed this ?

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-02-04 : 04:54:51

The stored procedure is in the msdb database

USE msdb -- use db to run sp, for example checking logs.
GO

SELECT *
FROM sysmail_mailitems
GO

SELECT *
FROM sysmail_log
GO

-- Use to test email working

Use msdb
Go

EXEC sp_send_dbmail
@profile_name='mssqlmail', -- double check your db mail and make sure this is correct profile!
@recipients='atul.jadhav@netafim-india.com',
@copy_recipients='testemail@someaddress.com', -- add another email address to double check!!
@subject='hi',
@body_format='html',
@importance = 'High',
@query = 'Use Testing$
Go
SELECT *
FROM Testing$',
@attach_query_result_as_file = 0;
Go

Further reading:
https://msdn.microsoft.com/en-us/library/ms190307.aspx

We are the creators of our own reality!

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-02-05 : 01:44:54

You must have Database mail enabled and configured

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources