Outputting msdb.dbo.sp_send_dbmail to HTML: quotes

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

Outputting msdb.dbo.sp_send_dbmail to HTML: quotes

Author

Topic

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-05-19 : 16:26:54

Last one for today.

My query now works with the SQL job thanks to visakh16 and peso.

I've found a bunch of articles on outputting the results into an HTML table. I'm testing it out now but could use some help.

In my query I had to change the single quotes to double quotes. I think I now have to do something different in the HTML output code.

Below is my code.

DECLARE @MyQuery varchar(1300)
SET @MyQuery = 
'CREATE TABLE #PrimaryClasses (classID int)
INSERT INTO #PrimaryClasses
SELECT 22 UNION ALL
SELECT 25 UNION ALL
SELECT 28 UNION ALL
SELECT 29 UNION ALL
SELECT 30 UNION ALL
SELECT 36 UNION ALL
SELECT 37 UNION ALL
SELECT 38 UNION ALL
SELECT 39 UNION ALL
SELECT 50 UNION ALL
SELECT 51 UNION ALL
SELECT 52 UNION ALL
SELECT 63 UNION ALL
SELECT 64 UNION ALL
SELECT 65

SELECT     tblFacility.vcFacilityName AS Club, 
			COUNT(CASE WHEN 
					tblmembers.sistatusid = "1" 
					AND classid IS NOT NULL
                      THEN tblmembers.vcMemberID ELSE NULL END) AS [Memberships]
FROM         tblMembers INNER JOIN
                      tblContracts ON tblMembers.iMembershipContractid = mac.dbo.tblContracts.iContractId INNER JOIN
                      tblFacility ON tblMembers.vcCurrentFacilityID = tblFacility.vcFacilityID LEFT OUTER JOIN
                      [#PrimaryClasses] ON classID = tblContracts.siMembershipClassId
WHERE tblFacility.vcFacilityName <> "Corporate" and tblMembers.cMemberCategory = "M"
GROUP BY tblFacility.vcFacilityName'


EXEC msdb.dbo.sp_send_dbmail
@recipients=N'test@test.com',
@body_format = 'text',
@body='Total Memberships as of Tonight' ,
@subject ='Total Memberships',
@profile_name ='Nightly Emails',
@attach_query_result_as_file = 1,
@execute_query_database = 'mac',
@query = @myquery,
@exclude_query_output=1,
@query_result_width=40

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-05-20 : 04:36:40

Something like this:



    
DECLARE @MyQuery varchar(1300)
SET @MyQuery = 
	N'<H1>Total Memberships as of Tonight</H1>' +
    N'<table border="1">' +
    N'<tr><th>Club</th><th>Memberships</th>' +
    CAST ( (CREATE TABLE #PrimaryClasses (classID int)
				INSERT INTO #PrimaryClasses
				SELECT 22 UNION ALL
				SELECT 25 UNION ALL
				SELECT 28 UNION ALL
				SELECT 29 UNION ALL
				SELECT 30 UNION ALL
				SELECT 36 UNION ALL
				SELECT 37 UNION ALL
				SELECT 38 UNION ALL
				SELECT 39 UNION ALL
				SELECT 50 UNION ALL
				SELECT 51 UNION ALL
				SELECT 52 UNION ALL
				SELECT 63 UNION ALL
				SELECT 64 UNION ALL
				SELECT 65

				SELECT     f.vcFacilityName AS Club, 
							COUNT(CASE WHEN 
									m.sistatusid = '1'
									AND classid IS NOT NULL
									  THEN m.vcMemberID ELSE NULL END) AS [Memberships]
				FROM         mac.dbo.tblMembers m INNER JOIN
									  mac.dbo.tblContracts c ON m.iMembershipContractid = c.iContractId INNER JOIN
									  mac.dbo.tblFacility f ON m.vcCurrentFacilityID = f.vcFacilityID LEFT OUTER JOIN
									  [#PrimaryClasses] ON classID = c.siMembershipClassId
				WHERE f.vcFacilityName <> 'Corporate' and m.cMemberCategory = 'M'
				GROUP BY f.vcFacilityName
				FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) + 
    N'</table>'
 
 EXEC msdb.dbo.sp_send_dbmail
@recipients=N'bsimms@macwellness.com',
@body=@MyQuery ,
@subject ='Total Memberships',
@profile_name ='Nightly Emails'

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-05-21 : 12:29:08

Thanks RickD. Right now I'm getting an error....
Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the keyword 'CREATE'. [SQLSTATE 42000] (Error 156) Incorrect syntax near ')'. [SQLSTATE 42000] (Error 102). The step failed.

I'm looking into the code now but nothing is jumping out at me.

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-05-21 : 17:01:52

edit* got it! thanks rick!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources