SQL to a Table? 1

[peterlyttle]

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

 

[crobin1]

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

PS C:\> $test = "1"
PS C:\> $test.GetType().Name
String
PS C:\> $test2 = [int] $test
PS C:\> $test2.GetType().Name
Int32

In your case, you'll somehow need to cast the dates as [datetime]. You may be able to do it as:

if ([DateTime]::Now.AddDays(-1).Date -ge [datetime] $last_restored_date)

or may have to use a separate variable:

$date1 = [datetime] $last_restored_date
if ([DateTime]::Now.AddDays(-1).Date -ge $date1)

Investigate along that line and see where you get.

 

[peterlyttle]

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

If I do this -

$varprimary_database = $ds | Select-Object primary_database 
$varlast_restored_date  = $ds | Select-Object last_restored_date 
$varprimary_database
$varlast_restored_date

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!

 

[crobin1]

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#h...=&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.