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 ««12345»»»

Sending scheduled SQL query results via HTML e-mail using SSIS Expand / Collapse
Author
Message
Posted Wednesday, April 23, 2008 9:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:01 PM
Points: 43, Visits: 191
Thank you, sir! Glad to be of assistance.
Post #489355
Posted Wednesday, April 23, 2008 9:04 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 6:38 PM
Points: 501, Visits: 251
Good lord...

I have it and did not realize it.

I never spend much time on the data server.

Erik


Dam again!
Post #489359
Posted Thursday, April 24, 2008 12:43 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:02 AM
Points: 648, Visits: 1,874
Very good overview article, Paul.

I'm glad to see I'm not the only one writing about this stuff.

http://www.sqlservercentral.com/Authors/Articles/David_McKinney/155294/

I look forward to seeing what else you come up with.

David McKinney.
Post #489694
Posted Thursday, April 24, 2008 4:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:01 PM
Points: 43, Visits: 191
Thanks, David...I wish I'd seen your excellent XSL article back when I was figuring out how to get all this working; it would have saved me a lot of time. If you're interested there are a few more similar articles posted on my website at http://www.360data.nl/EN/News.aspx
Post #489788
Posted Thursday, April 24, 2008 11:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 21, 2009 3:36 PM
Points: 2, Visits: 14
is any way to get the same result but using SQL Server 2000, i don't know if there is a native tool or a third party one.

Ia have some reports, a lot of data i could distribute in html format, but actually i cant create an HTML file from a DTS.

Thanks in advance... :)

Sorry about my english.. is not as good as I'd wish..
Post #490152
Posted Thursday, April 24, 2008 1:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:01 PM
Points: 43, Visits: 191
Hi Alberto,

Funnily enough, there is a way of doing almost the same thing in SQL 2000, but the way you do it is completely different. I used to have a home-mad alerting system running on SQL 2000 a few years ago that created a HTML summary page every 15 minutes from a SQL query result set.

How this worked would take an article in itself to describe, but the basic setup was to call sp_makewebtask with a predefined HTML template to define the HTML output, then call sp_runwebtask from a scheduled job to (re)create the actual HTML file with the formatted SQL query results. The next step of the job was to call SQL Mail with this file as an attachment.

There are all kinds of disadvantages to this method, but within the limitations of these SPs it works well and very reliably. Both SPs are reasonably well documented in Books Online. I hope this helps you.
Post #490284
Posted Thursday, April 24, 2008 5:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 21, 2009 3:36 PM
Points: 2, Visits: 14
Thanks Paul for answer my question.. so Ill try with the sp you've recommended..
Post #490366
Posted Monday, April 28, 2008 2:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 19, 2008 4:17 AM
Points: 1, Visits: 2
How can i do in sql server2000
it's possible in sql server
Post #491233
Posted Monday, April 28, 2008 2:10 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:02 AM
Points: 648, Visits: 1,874
s_kumarmsc (4/28/2008)
How can i do in sql server2000
it's possible in sql server


Why don't you read the previous posts
Post #491235
Posted Friday, May 2, 2008 12:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 22, 2013 10:25 AM
Points: 13, Visits: 94
Great Article!

I had 1 problem on Step 9 - no variable showed up so I created one. Then it looked like the articles

When testing in step 10 the HTML is empty I tested the SQL in Management studio and I see a XML result set returned.

I checked on the Variable and the value is

<ROOT><?MSSQLError HResult=\"0x80004005\" Source=\"Microsoft XML Extensions to SQL Server\" Description=\"No description provided\"?></ROOT>\r} String


Any ideas on what went wrong?

Thanks in advance!
Post #494485
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse