SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Outputting msdb.dbo.sp_send_dbmail to HTML: quotes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

swims01
Yak Posting Veteran

59 Posts

Posted - 05/19/2009 :  16:26:54  Show Profile  Reply with Quote
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

Edited by - swims01 on 09/03/2013 15:17:52

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 05/20/2009 :  04:36:40  Show Profile  Reply with Quote
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'
Go to Top of Page

swims01
Yak Posting Veteran

59 Posts

Posted - 05/21/2009 :  12:29:08  Show Profile  Reply with Quote
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.
Go to Top of Page

swims01
Yak Posting Veteran

59 Posts

Posted - 05/21/2009 :  17:01:52  Show Profile  Reply with Quote
edit* got it! thanks rick!

Edited by - swims01 on 05/21/2009 17:30:31
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000