April 24, 2008 at 1:53 pm
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.
April 24, 2008 at 5:53 pm
Thanks Paul for answer my question.. so Ill try with the sp you've recommended..
April 28, 2008 at 2:07 am
How can i do in sql server2000
it's possible in sql server
April 28, 2008 at 2:10 am
s_kumarmsc (4/28/2008)
How can i do in sql server2000it's possible in sql server
Why don't you read the previous posts 🙂
May 2, 2008 at 12:29 pm
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!
May 3, 2008 at 4:00 am
Hi Michael, thank you very much.
Regarding your problem, have you defined the variables as specified in step 7? And does your SQL task use an *ADO.NET* connection? This last is particularly important, because there are subtle differences in the ways parameter and variable values are passed with the different connection types. That's not to say that you can't make it work with another connection type, but the way you call the parameter and variable assignments is then different also.
May 5, 2008 at 12:52 am
Michael,
It sounds to me like a problem with the scope of the variables. They've got to be created with package level scope, otherwise they'll not be visible. The scope is determined by the focus at the moment of creation of the variables.
Regards,
David.
May 9, 2008 at 9:53 am
Paul,
Thank for the information. I changed the connection to ADO.Net and I get a valid XML see below:
{<ROOT><ErrorLog><dur><load_error_id>1236048</load_error_id><PackageRunDate>2008-05-09T00:00:00</PackageRunDate><ErrorTask>test</ErrorTask><ErrorStep>1</ErrorStep><ErrorCode>111</ErrorCode><ErrorDesc>Test</ErrorDesc></dur></ErrorLog></ROOT>}
my xsl file is
-------------------------------------------
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="ROOT">
<html>
<head>
<style type="text/css">
body {margin-left: 20px; font-family: segoe ui, tahoma, sans-serif;}
h1{color: #f00;}
h2{size: 70%; color: #00f;}
td{padding-right: 10px;}
</style>
</head>
<body>
<h1>XML ERROR</h1>
<xsl:value-of select="ROOT"/>
Please debug the package.
</body>
</html>
</xsl:template>
<xsl:template match="ErrorLog">
<html>
<head>
<style type="text/css">
body {margin-left: 20px; font-family: segoe ui, tahoma, sans-serif;}
h1{color: #f00;}
h2{size: 70%; color: #00f;}
td{padding-right: 10px;}
</style>
</head>
<body>
<h1>Daily Upload Results</h1>
<xsl:apply-templates select="dur"/>
</body>
</html>
</xsl:template>
<xsl:template match="dur">
<DIV style="margin-bottom:20px;">
<table>
<tr>
<th>Date</th>
<th>Task</th>
<th>Step</th>
<th>Source</th>
<th>Code</th>
<th>Description</th>
<th>Details</th>
</tr>
<div style="color:#f00; font-size: 90%;">
<tr>
<td><xsl:value-of select="PackageRunDate"/></td>
<td><xsl:value-of select="ErrorTask"/></td>
<td><xsl:value-of select="ErrorStep"/></td>
<td><xsl:value-of select="ErrorStep"/></td>
<td><xsl:value-of select="ErrorSource"/></td>
<td><xsl:value-of select="ErrorCode"/></td>
<td><xsl:value-of select="ErrorDesc"/></td>
<td><xsl:value-of select="ErrorDetails"/></td>
</tr>
</div>
</table>
</DIV>
<hr />
</xsl:template>
<xsl:template match="NoRecords">
<html>
<head>
<style type="text/css">
body {margin-left: 20px; font-family: tahoma, sans-serif;}
</style>
</head>
<body>
<h1>Daily Upload Results</h1>
<div>
No Errors were found.
</div>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
-------------------------------------------
I must be doing something wrong as I get nothing entered into the htm test document.
May 21, 2008 at 8:01 am
Hi Michael...sorry for the delay in replying.
Seeing the xsl file on it's own (without the relevant XML input) probably isn't going to be much help, but if you'd like I can send you a copy of my original package so you can try it out yourself and see where the differences (if any) lie. Send me a mail via my website - http://www.360data.nl - and I'll get back to you ASAP.
May 21, 2008 at 4:52 pm
I figured out the problems.
I eliminated the duplicate errorstep listing and I needed to drop ErrorDetails as it's an XML field by itself (the value of which will change depending on the table in question )
Once I dropped it - it worked as expected!
Thanks PAUL for a great article!!!
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="ErrorLog">
<html>
<head>
<style type="text/css">
body {margin-left: 20px; font-family: segoe ui, tahoma, sans-serif;}
h1{color: #f00;}
h2{size: 70%; color: #00f;}
td{padding-right: 10px;}
</style>
</head>
<body>
<h1>Daily Upload Results</h1>
<xsl:apply-templates select="dur"/>
</body>
</html>
</xsl:template>
<xsl:template match="dur">
<DIV style="margin-bottom:20px;">
<table border=".5">
<tr>
<th>Date</th> <th>Task</th> <th>Step</th> <th>Source</th> <th>Code</th>
<th>Description</th> <th>Details</th>
</tr>
<tr>
<td>
<xsl:value-of select="PackageRunDate"/>
</td>
<td>
<xsl:value-of select="ErrorTask"/>
</td>
<td>
<xsl:value-of select="ErrorStep"/>
</td>
<td>
<xsl:value-of select="ErrorSource"/>
</td>
<td>
<xsl:value-of select="ErrorCode"/>
</td>
<td>
<xsl:value-of select="ErrorDesc"/>
</td>
<td>
See Log Error Table
</td>
</tr>
</table>
</DIV>
<hr/>
</xsl:template>
<xsl:template match="NoRecords">
<html>
<head>
<style type="text/css">
body {margin-left: 20px; font-family: segoe ui, tahoma, sans-serif;}
h1{color: #f00;}
</style>
</head>
<body>
<h1>Daily Upload Results</h1>
<div>
No Errors were found.
</div>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
June 4, 2008 at 2:52 pm
This is great! I got it all working with some tweaks. I couldn't use the variable to pass the XML, so I added a script that wrote it to a xml on the server, then picked that up from a file connection to populate the HTML for the email. Here's my issue now, hopefully someone can shed a little light??? 🙂 I can run the package just fine (email sends) in BIDS, and also when I import into SMS. However, when I create a job to run the package, it fails. The job runs under a proxy with my own credentials.
Any thoughts?
June 4, 2008 at 3:13 pm
Do you mean that when you run the job manually it fails, or when it runs as scheduled (and thus using the permissions of the SQL Server Agent account?) that it fails?
June 4, 2008 at 3:47 pm
I can run the package manually in management studio (right-click on package and click execute) and everything works just fine. When I try and run the job (right-click on job and click start job) is when it fails. I can't seem to find anywhere in the history log that says why it failed. I can see that the html and xml files that are supposed to be generated aren't modified either when trying to run the job
Thanks for your help! I'm no DBA, I know just enough of BIDS and management studio to be dangerous - all self-taught.
June 4, 2008 at 4:17 pm
Does calling other packages (that don't use the mailing functions) from SQL jobs work for you? In other words, is this the only SSIS job that doesn't work for you (meaning that it's a problem with permissions on something this package uses) or do others fail also (meaning that there's probably a general problem with the SQL Server Agent account when calling the SSIS executables)?
Try changing the SQL Server Agent account temporarily to your own or another high-privilege network or domain account and see if it works then.
June 4, 2008 at 5:23 pm
I had a similar problem. We tried to use a network account to run the SQL server agent. At first it crashed the server. Once we set the account as a a member of the correct local group - it worked fine. Our case required two sets of permissions local and network.
my 2 cents.
Viewing 15 posts - 16 through 30 (of 84 total)
You must be logged in to reply to this topic. Login to reply