Send query result as HTML Mail

Question

Send query result as HTML Mail

Viewing 13 posts - 16 through 27 (of 27 total)

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

drwhitaker SSC-Addicted Points: 404 More actions · Answer 1

This stored procedure is great, but I still see one flaw. When passing the SQL, the output always sorts the columns in alpha order on output not the order I passed. How can this be fixed.

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 2

drwhitaker (6/18/2010)
This stored procedure is great, but I still see one flaw. When passing the SQL, the output always sorts the columns in alpha order on output not the order I passed. How can this be fixed.

Add an order by clause?

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

"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."

drwhitaker SSC-Addicted Points: 404 More actions · Answer 3

where? in the SQL I pass or in the stored procedure

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 4

try passing it in first, since that would be easiest, and see what you get. I don't have this one set up to test at the moment. Let me know if it doesn't work.

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

"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."

nathan.syverson Old Hand Points: 388 More actions · Answer 5

Yes, try it in the sql you pass into the proc. I use this to mail out two tables--I don't seem to recall this limitation...

drwhitaker SSC-Addicted Points: 404 More actions · Answer 6

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

drwhitaker SSC-Addicted Points: 404 More actions · Answer 7

My HMTL body output is getting cut-off and now showing all the content:

Anybody know why?

drwhitaker SSC-Addicted Points: 404 More actions · Answer 8

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

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 9

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[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"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."

drwhitaker SSC-Addicted Points: 404 More actions · Answer 10

No, the order of the columns even after adding the "order by" clause are still being displayed in alpha order. Anybody have any ideas

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 11

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[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"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."

drwhitaker SSC-Addicted Points: 404 More actions · Answer 12

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

🙂

gf_griever SSC Rookie Points: 44 More actions · Answer 13

gf_griever

SSC Rookie

Points: 44

March 12, 2015 at 11:17 am

#1783668

please i need RDP and Inbox SMTP