Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Send query result as HTML Mail Expand / Collapse
Author
Message
Posted Friday, June 18, 2010 7:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 2:07 PM
Points: 16, Visits: 85
Thanks, I will try that.

What about in the message body, I see this first before the columns:

<td

What is this and how do I get rid of it
Post #939906
Posted Friday, June 18, 2010 7:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 2:07 PM
Points: 16, Visits: 85
My HMTL body output is getting cut-off and now showing all the content:

Anybody know why?
Post #939908
Posted Friday, June 18, 2010 8:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 2:07 PM
Points: 16, Visits: 85
I tried it and it failed:

exec sun_sp_send_mailhtml
'OUTBOUND.SMTP.USI.NET', --SMTP Server
'chemapptabware@sunocoinc.com', --From
'TabWareRTCheck@sunocoinc.com', --To
'', --CC
'', --CCi
'NOTICE: RT TabWare - Missing GL Transactions', --Subject
'select plant, convert(varchar, transaction_date,110) transaction_date, transaction_type
from NEV_TW_PROD.dbo.in_log_material
where plant = ''RT'' and
transaction_date = convert(smalldatetime,convert(varchar, getdate() - 1,101),101) and
transaction_type in (''Cvar'',''Xvar'',''Xrecp'',''Accr'',''Taxes'',''Frght'',''Xinv'') and carrier is null
order by plant, convert(varchar, transaction_date,110), transaction_type'

Here is my results:

[Execute SQL Task] Error: Executing the query "exec sun_sp_send_mailhtml 'OUTBOUND.SMTP.USI.NET', --SMTP Server 'chemapptabware@sunocoinc.com', --From 'TabWareRTCheck@sunocoinc.com', --To '', --CC '', --CCi 'NOTICE: RT TabWare - Missing GL Transactions', --Subject 'select plant, convert(varchar, transaction_date,110) transaction_date, transaction_type from NEV_TW_PROD.dbo.in_log_material where plant = ''RT'' and transaction_date = convert(smalldatetime,convert(varchar, getdate() - 1,101),101) and transaction_type in (''Cvar'',''Xvar'',''Xrecp'',''Accr'',''Taxes'',''Frght'',''Xinv'') and carrier is null order by plant, convert(varchar, transaction_date,110), transaction_type'" failed with the following error: "The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Added "TOP 100 PERCENT" and it worked
Post #939913
Posted Monday, June 21, 2010 7:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:12 AM
Points: 2,627, Visits: 19,093
so, just to be clear, you fixed your issues with the table cell (<td stuff), added Top 100 percent and it works now?

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #940314
Posted Sunday, June 27, 2010 3:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 2:07 PM
Points: 16, Visits: 85
No, the order of the columns even after adding the "order by" clause are still being displayed in alpha order. Anybody have any ideas
Post #943573
Posted Monday, June 28, 2010 8:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:12 AM
Points: 2,627, Visits: 19,093
Think this part is causing you issues:
-- prepare query
set @SqlCmd = 'select * into ##tempHTML1 from (' + @Query + ') as t1'
execute (@SqlCmd)

Try adding another parameter for @OrderBy, then change that part to :
-- prepare query
set @SqlCmd = 'select * into ##tempHTML1 from (' + @Query + ') as t1 ORDER BY ' + @OrderBy
execute (@SqlCmd)


and see what you get.


---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #943997
Posted Wednesday, June 30, 2010 2:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 2:07 PM
Points: 16, Visits: 85
This is resolved now, I found the problem. Since our database was case sensitive, I was having issues with tempdb.information_schema.columns. So I changed them to upper case tempdb.INFORMATION_SCHEDMA.COLUMNS and now all works great

Post #945790
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse