PowerShell Script DB Query not passing all results

Question

Good morning stackoverflow. I have a PowerShell script that is executing a SQL query against an Oracle database and then taking the results and passing them to a local shell command. It works, mostly. What is happening is some of the results are being dropped and the only significance I can see about these is that they have a couple of columns that have null values (but only 2 out of the 8 columns that are being returned). When the query is executed in sQL developer I get all expected results. This issue applies to the $eventcheck switch, the $statuscheck works fine. The Powershell script is below:

param(
    [parameter(mandatory=$True)]$username,
    [parameter(mandatory=$True)]$password,
    $paramreport,
    $paramsite,
    [switch]$eventcheck,
    [switch]$statuscheck
)

$qry1 = Get-Content .\vantageplus_processing.sql

$qry2 = @"
    select max(TO_CHAR(VP_ACTUAL_RPT_DETAILS.ETLLOADER_OUT,'YYYYMMDDHH24MISS')) Completed

    from MONITOR.VP_ACTUAL_RPT_DETAILS

    where VP_ACTUAL_RPT_DETAILS.REPORTNUMBER = '$($paramreport)' and VP_ACTUAL_RPT_DETAILS.SITE_NAME = '$($paramsite)'

    order by completed desc
"@

$connString = @"
    Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST="HOST")(PORT="1521"))
    (CONNECT_DATA=(SERVICE_NAME="SERVICE")));User ID="$username";Password="$password"
"@

function Get-OLEDBData ($connectstring, $sql) {
    $OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($connectstring)
    $OLEDBConn.open()
    $readcmd = New-Object system.Data.OleDb.OleDbCommand($sql,$OLEDBConn)
    $readcmd.CommandTimeout = '300'
    $da = New-Object system.Data.OleDb.OleDbDataAdapter($readcmd)
    $dt = New-Object System.Data.DataTable
    [void]$da.fill($dt)
    $OLEDBConn.close()
    return $dt
}

if ($eventcheck)
    {
        $output = Get-OLEDBData $connString $qry1
        ForEach ($lines in $output)
        {
            start-process -NoNewWindow -FilePath msend.exe -ArgumentList @"
                -n bem_snmp01 -r CRITICAL -a CSG_VANTAGE_PLUS -m "The report $($lines.RPT) for site $($lines.SITE) has not been loaded by $($lines.EXPDTE)" -b "vp_reportnumber='$($lines.RPT)'; vp_sitename='$($lines.SITE)'; vp_expectedcomplete='$($lines.SIMEXPECTED)'; csg_environment='Production';"
"@ # KEEP THIS TERMINATOR AT THE BEGINNING OF LINE       
        }
    }

if ($statuscheck)
    {
        $output = Get-OLEDBData $connString $qry2
        # $output | foreach {$_.completed}
        write-host -nonewline $output.completed
    }

So that you can see the data, below is a csv output from Oracle SQL Developer with the ACTUAL results to the query that is being referenced by my script. Of these results lines 4, 5, 6, 7, 8, 10 are the only ones being passed along in the ForEach loop, while the others are not even captured in the $output array. If anyone can advise of a method for getting all of the results passed along, I would appreciate it.

"SITE","RPT","LSDTE","EXPDTE","CIMEXPECTED","EXPECTED_FREQUENCY","DATE_TIMING","ETME"
"chrcse","CPHM-054","","2014/09/21 12:00:00","20140921120000","MONTHLY","1",
"chrcse","CPSM-226","","2014/09/21 12:00:00","20140921120000","MONTHLY","1",
"dsh","CPSD-176","2014/09/28 23:20:04","2014/09/30 04:00:00","20140930040000","DAILY","1",1.41637731481481481481481481481481481481
"dsh","CPSD-178","2014/09/28 23:20:11","2014/09/30 04:00:00","20140930040000","DAILY","1",1.4162962962962962962962962962962962963
"exp","CPSM-610","2014/08/22 06:42:10","2014/09/21 09:00:00","20140921090000","MONTHLY","1",39.10936342592592592592592592592592592593
"mdc","CPKD-264","2014/09/24 00:44:32","2014/09/30 04:00:00","20140930040000","DAILY","1",6.35771990740740740740740740740740740741
"nea","CPKD-264","2014/09/24 01:00:31","2014/09/30 03:00:00","20140930030000","DAILY","1",6.34662037037037037037037037037037037037
"twtla","CPOD-034","","2014/09/29 23:00:00","20140929230000","DAILY","0",
"twtla","CPPE-002","2014/09/29 02:40:35","2014/09/30 06:00:00","20140930060000","DAILY","1",1.27712962962962962962962962962962962963
"twtla","CPXX-004","","2014/09/29 23:00:00","20140929230000","DAILY","0",

You're running exactly what is in the local vantageplus_processing.sql in SQL Developer, against the same (committed) data - you don't just have uncommitted data in that session? Maybe showing that SQL might reveal something. Does fill return the number of rows added to the data table, and if so is it seeing 11 rows at that point? — Alex Poole
– Alex Poole, Commented Sep 30, 2014 at 16:34
I'll add the SQL, somehow, it makes me exceed the maximum number of characters allowed by like 16k. As for commited or uncommited, I'm not certain how to tell, I do not admin the db, but I'll do some research and see if I can figure it out. — billhubb84
– billhubb84, Commented Sep 30, 2014 at 17:05
Well, did you create any of the missing rows through SQL Developer? If you run your query in a new session (maybe a different client to be sure; SQL*Plus, Toad, whatever; or disconnect and reconnect if SQL Developer is set up to share connections/sessions between worksheets) do you see the rows with nulls? Or just commit in SQL Developer to be sure, of course, as long as you're sure you want to keep all outstanding changes. — Alex Poole
– Alex Poole, Commented Sep 30, 2014 at 17:09
With the OleDB code outside a function and using the fill I still see only the 6 results. I did not write any of the rows into the database they've all been written by a separate app, and with a fresh connection (GPO keeps me from using an alternative app) I still see 10 rows in SQL developer. STill looking for a way to get the SQL posted somewhere, but in short it is a series of case statements and concatenations. — billhubb84
– billhubb84, Commented Sep 30, 2014 at 17:24
If you're sure you're running exactly the same code then it may not be relevant, and if it's > 16k then it probably won't be useful anyway. Just ticking off obvious things - uncommitted data, connected to the wrong schema, wrong database, different query... Hopefully someone who knows about OleDB will have some other ideas. — Alex Poole
– Alex Poole, Commented Sep 30, 2014 at 17:30

billhubb84 · Accepted Answer · 2014-09-30 18:04:38Z

1

It appears, actually, that somehow a comment that was in the query was causing this issue. I removed it and the results started returning normal. I have no idea why this would be the case, unless it has to do with the way the import works (does it import everything as one line?). Either way, the results are normal now.

answered Sep 30, 2014 at 18:04

billhubb84

1954 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PowerShell Script DB Query not passing all results

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related