asked on

How can I e-mail MSSQL results via PHP?

Hi all,
I have a simply select script that I run in the mornings to find failed jobs for SQL Server that have run/failed overnight. I want to email this over but can't figure out how to email the table/results over.

The subject/body bit works fine but I assumed that if I just stuck $sql_result in the body, it would email results over?

But instead, I got odbc_result_all(Resource id #2)

Appreciate any help

I'm more used to MySQL and forms when it comes to emailing so it's fairly new to me all this

Thanks in advance

(Note: Code in the body area for the email has a number of possible tries I've had)

<?php



?>
<html>
<p><b>Failed Jobs</b></p>
</html>
<?php
header('Refresh: 10');
// create connection
$connection = odbc_connect("ODBC_Prod","user","pass");

// test connection
if (!$connection) {
echo "Couldn't make a connection!";
exit;
}


// create SQL statement
//$sql = "sp_testrunjob";
$sql="

-- Variable Declarations 
DECLARE @PreviousDate datetime 
DECLARE @Year VARCHAR(4) 
DECLARE @Month VARCHAR(2) 
DECLARE @MonthPre VARCHAR(2) 
DECLARE @Day VARCHAR(2) 
DECLARE @DayPre VARCHAR(2) 
DECLARE @FinalDate INT 

-- Initialize Variables 
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Today
SET @Year = DATEPART(yyyy, @PreviousDate)  
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate)) 
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2) 
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate)) 
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2) 
SET @FinalDate = CAST(@Year + @Month + @Day AS INT) 

-- Final Logic 
SELECT   j.[name], 
         s.step_name, 
         h.step_id, 
         h.step_name, 
         h.run_date, 
         h.run_time, 
         h.sql_severity, 
         h.message, 
         h.server 
FROM     msdb.dbo.sysjobhistory h 
         INNER JOIN msdb.dbo.sysjobs j 
           ON h.job_id = j.job_id 
         INNER JOIN msdb.dbo.sysjobsteps s 
           ON j.job_id = s.job_id
           AND h.step_id = s.step_id
WHERE   -- h.run_status = 0 -- Failure 
          h.run_date < @FinalDate 
ORDER BY h.instance_id DESC
";

// prepare SQL statement
$sql_result = odbc_prepare($connection,$sql);

// execute SQL statement and get results
odbc_execute($sql_result);

// format result in HTML table
//odbc_result_all($sql_result,"border=1");

$to = "user@company.com";
$subject = "Job Status";
//$body = "Hi,\n\nDoes this work?";
$body = "odbc_result_all($sql_result)"; $sql_result;
odbc_result_all($sql_result,"border=1");

if (mail($to, $subject, $body))
{
   echo("<p>Message successfully sent!</p>");
}
else
{
   echo("<p>Message delivery failed...</p>");
}





// free resources and close connection
odbc_free_result($sql_result);
odbc_close($connection);

?>

Open in new window

David Beveridge

try something like this...

$body = var_export(odbc_result_all($sql_result));

Steve Bink

It is the same concept as MySQL. Your query returns a result set. Go through the result set to retrieve the rows, adding each row to the body of your email with whatever formatting you need. bevhost has given you a handy shortcut to that. See here for more explanation:

http://www.php.net/manual/en/function.odbc-result-all.php

akashj

ASKER

Thanks both
I tried simply putting in bevhost's line to expect a result on-screen to which I get the number 2
This was correct in showing that I had 2 failed jobs overnight however, despite being part of the e-mail body, it did not send this via email and instead, sent a blank email

Am I putting this is in the wrong area or will I need to re-run the results query within the email body?

ASKER CERTIFIED SOLUTION

David Beveridge

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Swafnil

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

akashj

ASKER

Thans both
I was under the impression it would just post to email the results so to speak

I'll try and get this in today and report back

Thanks again

David Beveridge

How did you go?

akashj

ASKER

Thanks bevhost for chasing this up - I am getting forgetful in my old age! :- )

It worked perfectly with your advice previously with a bit of modification of Swafnil's script too

Now I just need to create a new thread on how I can/should allocate the points :- )