January 28, 2015 at 4:29 am
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
January 28, 2015 at 4:59 am
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
January 28, 2015 at 5:40 am
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.
January 28, 2015 at 5:44 am
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
January 28, 2015 at 6:42 am
Cheers 🙂
January 28, 2015 at 8:49 pm
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
January 29, 2015 at 12:38 am
atul.jadhav (1/28/2015)
Dear expert, thanks for replyI 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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply