Email not receved from SQL Server

Question

Email not receved from SQL Server

atul.jadhav

SSC-Addicted

Points: 491
More actions
February 2, 2015 at 2:08 am

#314205

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,
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
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]

Viewing 3 posts - 1 through 3 (of 3 total)

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

atul.jadhav (2/2/2015)
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,
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
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]

Your TEST1 procedure does not appear to do anything other than a SELECT. Why would you expect anything else?

atul.jadhav SSC-Addicted Points: 491 More actions · Answer 2

I want to send that table in HTML format to my email id