Hi, I have done a SQL query to show the following fields -

Here is my query - "select primary_database,last_copied_date,last_restored_date  from dbo.log_shipping_monitor_secondary order by primary_database asc"

So I end up with all the values in

$ds = $dataSet.Tables[0]

If I do $ds | Select-Object primary_database, last_copied_date, last_restored_date

This will list out the table.  How do I convert the last_copied_date and last_restored_date to a date field as the 1st 2 rows will be the column header and -----

eg

primary_database                                                                   last_copied_date                                                                   last_restored_date                                                               
----------------                                                                   ----------------                                                                   ------------------                                                               
DB1                                                                           19/10/2009 21:30:20                                                                13/10/2009 22:00:23                                                              
DB2                                                                     19/10/2009 21:30:19                                                                13/10/2009 22:00:23  

   
The reason for doing this is to eventually be able to use the ConvertTo-HTML and in that say
if ([DateTime]::Now.AddDays(-1).Date -ge $last_restored_date){
Make this value red
}
else{
Make it Black
}

So that and HTML table can be emailed and the databases that have a date older than -1 day can be marked as bold and red

Here is the code ive done so far -

#Global Variables
$mailservername = "mailserver"
$standbyservername = "DBServer"
$FromAddress = "server@domain.com"
$ToAddress = "me@domain.com"
$css_style = "<style>"
$css_style = $css_style + "BODY{background-color:white;font-family:Tahoma;font-size:8pt}"
$css_style = $css_style + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$css_style = $css_style + "TH{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:white}"
$css_style = $css_style + "TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:white}"
$css_style = $css_style + "</style>"
$body = "<H3>" + $primaryservername + " to " + $standbyservername + " Log Shipping Info</H3>"
$MessageBody =""


#SQL Connection Section
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=" + $standbyservername + ";Database=msdb;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select primary_database,last_copied_date,last_restored_date  from dbo.log_shipping_monitor_secondary order by primary_database asc"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$ds = $dataSet.Tables[0]


$body = $body + "<h5>There are " + $ds.Rows.Count + " databases being  Log Shipped. <br/></h5>"

$MessageBody = $ds | Select-Object primary_database, last_copied_date, last_restored_date

#Email Section
$MessageBody = $MessageBody | ConvertTo-HTML -head $css_style –body $body
$MessageSubject = "LS on " + $standbyservername +" is Down"
$SMTPMessage = New-Object System.Net.Mail.MailMessage $FromAddress, $ToAddress, $MessageSubject, $MessageBody
$SMTPMessage.IsBodyHtml = $true
$SMTPClient = New-Object System.Net.Mail.SMTPClient $mailservername
$SMTPClient.Send($SMTPMessage)

Does anyone have any ideas?

Cheers,
Peter

You can explicitly cast variables from one datatype to another, I think that's what you need here. For example:

In your case, you'll somehow need to cast the dates as [datetime]. You may be able to do it as:
or may have to use a separate variable:
Investigate along that line and see where you get.

Hi, sorry maybe I didnt explain what was saying correctly.

If I do this -


I will only get the the value from the $varprimary_database and the $varlast_restored_date will be blank.  I've tried changing the order and it only seems to display the 1st lot of values. (maybe this is to do with the dataset)?

I havent even got to the stage of working with the date etc, though I am looking into doing it from the SQL Query.

Thanks for the help!
 

The only question I saw in your post was how to convert the dates. I haven't played with SQL from PowerShell yet, I'll have to look in to it. A quick Google search:
http://www.google.com/webhp?hl=en#hl=en&;source=hp&q=powershell+sql+query&amp;btnG=Google+Search&aq=f&amp;aqi=&amp;oq=powershell+sql+query&;fp=c807e9ccc08a197a

shows several people doing similar things, with no obvious reports of only getting limited data back. You might look at some of those results.