When I launch a powershell session via powershell.exe and at the PS> prompt enter myscript.ps1 it errors with - PS D:\TEST> D:\TEST\testrun.ps1
Missing ')' in method call.
At D:\TEST\testrun.ps1:1 char:65
- $factory = [System.Data.Common.DbProviderFactories]::GetFactory( <<<< â?oIBM.
Data.DB2â??)
- CategoryInfo : ParserError: (CloseParenToken:TokenId) [], Parse Exception
- FullyQualifiedErrorId : MissingEndParenthesisInMethodCall
What is the reason for it working when running the lines of code individually via CutNPaste into ISE and launching? I receive the email with a nice table from the db extraction when I run it via ISE. This error is probably why it doesn't run in TaskScheduler as well.
$factory = [System.Data.Common.DbProviderFactories]::GetFactory(“IBM.Data.DB2”)
$cstrbld = $factory.CreateConnectionStringBuilder()
$cstrbld.Database = 'dbname'
$cstrbld.UserID = 'USER'
$cstrbld.Password = 'password'
$cstrbld.Server = 'server:50001'
$dbconn = $factory.CreateConnection()
$dbconn.ConnectionString = $cstrbld.ConnectionString
$dbconn.Open()
$dbcmd = $factory.CreateCommand()
$dbcmd.Connection = $dbconn
########################################################################################################################################
$dbcmd.CommandText = 'SELECT PFIWORKUNIT,ACTOR,FLOWDEFINITION,WORKTITLE,STATUS FROM APPLICATION.PFIWORKUNIT WHERE STATUS BETWEEN 0 AND 3'
########################################################################################################################################
$dbcmd.CommandType = [System.Data.CommandType]::Text
$da = $factory.CreateDataAdapter()
$da.SelectCommand = $dbcmd
$ds = New-Object System.Data.DataSet
$da.Fill($ds) | Out-Null
$dbconn.Close()
ForEach ($row in $ds.Tables[0].Rows) {
$ExciterObject = New-Object PSObject
$ExciterObject | Add-Member -MemberType NoteProperty -Name "PFIWORKUNIT" -Value $row.PFIWORKUNIT
$ExciterObject | Add-Member -MemberType NoteProperty -Name "ACTOR" -Value $row.ACTOR
$ExciterObject | Add-Member -MemberType NoteProperty -Name "FLOWDEFINITION" -Value $row.FLOWDEFINITION
$ExciterObject | Add-Member -MemberType NoteProperty -Name "WORKTITLE" -Value $row.WORKTITLE
$ExciterObject | Add-Member -MemberType NoteProperty -Name "STATUS" -Value $row.STATUS
$exportObject += $ExciterObject
}
function ConvertTo-HTMLTable ($obj) {
# Accepts a System.Data.DataTable object or an array of PSObjects and converts to styled HTML table
# add type needed to replace HTML special characters into entities
Add-Type -AssemblyName System.Web
$sb = New-Object -TypeName System.Text.StringBuilder
[void]$sb.AppendLine('<table>')
if ($null -ne $obj) {
if (([object]$obj).GetType().FullName -eq 'System.Data.DataTable'){
# it is a DataTable; convert to array of PSObjects
$obj = $obj | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors
}
$headers = $obj[0].PSObject.Properties | Select -ExpandProperty Name
[void]$sb.AppendLine('<thead><tr>')
foreach ($column in $headers) {
[void]$sb.AppendLine(('<th>{0}</th>' -f [System.Web.HttpUtility]::HtmlEncode($column)))
}
[void]$sb.AppendLine('</tr></thead><tbody>')
$row = 0
$obj | ForEach-Object {
# add inline style for zebra color rows
if ($row++ -band 1) {
$tr = '<tr style="background-color: {0};">' -f $oddRowBackColor
}
else {
$tr = '<tr>'
}
[void]$sb.AppendLine($tr)
foreach ($column in $headers) {
[string]$val = $($_.$column)
#If($NameAd -ne "")
if ($val -notmatch "\S") {
$td = '<td> </td>'
}
else {
$td = '<td>{0}</td>' -f [System.Web.HttpUtility]::HtmlEncode($val)
}
[void]$sb.Append($td)
}
[void]$sb.AppendLine('</tr>')
}
[void]$sb.AppendLine('</tbody>')
}
[void]$sb.AppendLine('</table>')
return $sb.ToString()
}
$headerBackColor = '#4F81BD' # backgroundcolor for column headers
$oddRowBackColor = '#DCE6F1' # background color for odd rows
$style = @"
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Report</title>
<meta name="generator" content="PowerShell" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style type="text/css">
body {
font-family: Verdana, Arial, Geneva, Helvetica, sans-serif;
font-size: 12px;
color: black;
}
table, td, th {
border-color: black;
border-style: solid;
font-family: Verdana, Arial, Geneva, Helvetica, sans-serif;
font-size: 11px;
}
table {
border-width: 0 0 1px 1px;
border-spacing: 0;
border-collapse: collapse;
}
td, th {
margin: 0;
padding: 4px;
border-width: 1px 1px 0 0;
text-align: left;
}
th {
color: white;
background-color: $headerBackColor;
font-weight: bold;
}
</style>
<p><span style="color: #0000ff;"><strong>I'm only pulling only 1 thru 3 for a short email list</strong></span></p>
<p><span style="color: #0000ff;"><strong>WORK UNIT STATUS LEVELS BELOW:</strong></span></p>
<ol>
<li>01 = (<span style="color: #3366ff;"><strong>Ready for processing</strong></span>) using your SA account.</li>
<li>02 = (<span style="color: #3366ff;"><strong>Processing in progresst</strong></span>) using your SA account.</li>
<li>03 = (<span style="color: #3366ff;"><strong>Processing failed</strong></span>) using your SA account.</li>
<li>04 = (<span style="color: #3366ff;"><strong>Processing completed</strong></span>) using your SA account.</li>
<li>05 = (<span style="color: #3366ff;"><strong>Cancellation requested</strong></span>) using your SA account.</li>
<li>06 = (<span style="color: #3366ff;"><strong>Cancellation in progress</strong></span>) using your SA account.</li>
<li>07 = (<span style="color: #3366ff;"><strong>Cancellation failed</strong></span>) using your SA account.</li>
<li>08 = (<span style="color: #3366ff;"><strong>Cancellation completed</strong></span>) using your SA account.</li>
</ol>
"@
####SET EMAIL VALUES####
$bodyA = '{0}</head><body>{1}</body></html>' -f $style, (ConvertTo-HTMLTable $exportObject)
$EmailTo = "you@you.org"
$EmailFrom = "me@me.org"
$Subject = "Failed Work Unit Issue - High Priority"
$Body = $bodyA
$SMTPServer = "mail.server.org"
####EMAIL PROCESS ####
$anonUsername = "anonymous"
$anonPassword = ConvertTo-SecureString -String "anonymous" -AsPlainText -Force
$anonCredentials = New-Object System.Management.Automation.PSCredential($anonUsername,$anonPassword)
Send-MailMessage -smtpserver "$SMTPServer" -from "$EmailFrom" -to "$EmailTo" -subject "$Subject" -bodyAsHtml "$Body" -credential $anonCredentials
Exit
