Sending Email from SQL thru store procedure

Question

Post reply

Sending Email from SQL thru store procedure

atul.jadhav

SSC-Addicted

Points: 491
More actions
January 28, 2015 at 4:29 am

#302661

Dear Expert, I have below code to send email in HTML table format with help of store procedure. from my
Database = "CreditControl"
Table = "Testing$"
and code as below
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= @recipients,
@profile_name = @profile_name,
@subject = @subject,
@body_format = @body_format ;
but error show :- "Msg 208, Level 16, State 1, Line 8
Invalid object name 'CreditControl.Testing$'."
___________________________________________________________________________________________
also find store procedure which show error "Msg 102, Level 15, State 1, Procedure TEST1, Line 2
Incorrect syntax near '='."
SQL Mail profile name is = 'mssqlmail'
USE [CreditControl]
GO
/****** Object: StoredProcedure [dbo].[TEST1] Script Date: 1/28/2015 3:51:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TEST1]
@recipients = 'atul.jadhav@netafim-india.com',
@profile_name = 'mssqlmail',
@subject = 'hi',
@body_format = 'HTML' ;
AS
BEGIN
SELECT *
FROM Testing$
END
Please help to get out from this

Viewing 7 posts - 1 through 6 (of 6 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Regarding the first error: are you executing the script in the correct database?

Regarding the second error: you forgot the parameters data types.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gazareth One Orange Chip Points: 27737 More actions · Answer 2

FROM [CreditControl].[Testing$] - this is trying to find an object called Testing$ in a schema called CreditControl.

Change the query to include the schema name (presumably dbo):

FROM [CreditControl].dbo.[Testing$]

or

FROM [CreditControl]..[Testing$]

For the test1 proc, your syntax is all wrong & it doesn't send an email.

Parameters are missing datatypes, e.g. @recipients DATATYPE (guessing nvarchar(length))

The ; after parameters should be removed.

The parameters are not used in the body of the proc, all it's doing is selecting from the table.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Gazareth (1/28/2015)
FROM [CreditControl].[Testing$] - this is trying to find an object called Testing$ in a schema called CreditControl.
Change the query to include the schema name (presumably dbo):
FROM [CreditControl].dbo.[Testing$]
or
FROM [CreditControl]..[Testing$]

Good catch. Missed the mistake in the 3-part naming.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gazareth One Orange Chip Points: 27737 More actions · Answer 4

Gazareth

One Orange Chip

Points: 27737

January 28, 2015 at 6:42 am

#1773666

Cheers 🙂

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

Dear expert, thanks for reply

I solved first error and but could not understand error in second code

could you please provide more or correct code, I am not much expert in codding

please help

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

atul.jadhav (1/28/2015)
Dear expert, thanks for reply
I solved first error and but could not understand error in second code
could you please provide more or correct code, I am not much expert in codding
please help

About which error are we talking exactly?

The more information you give us, the easier it is for us to help you.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP