June 18, 2010 at 6:05 am
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.
June 18, 2010 at 6:47 am
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."
June 18, 2010 at 6:50 am
where? in the SQL I pass or in the stored procedure
June 18, 2010 at 7:12 am
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."
June 18, 2010 at 10:43 am
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...
June 18, 2010 at 7:45 pm
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
June 18, 2010 at 7:47 pm
My HMTL body output is getting cut-off and now showing all the content:
Anybody know why?
June 18, 2010 at 8:12 pm
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
June 21, 2010 at 7:32 am
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."
June 27, 2010 at 3:24 pm
No, the order of the columns even after adding the "order by" clause are still being displayed in alpha order. Anybody have any ideas
June 28, 2010 at 8:57 am
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."
June 30, 2010 at 2:47 pm
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
🙂
March 12, 2015 at 11:17 am
please i need RDP and Inbox SMTP
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply