6 Replies Latest reply on Jan 20, 2016 9:22 AM by pthivierge Branched from an earlier discussion.

    PowerShell: Issue passing TimeStamp into PI OLEDB Enterprise query


      I'm facing the a time stamp issue for the following query.


      $queryTimeStamp = "{0:dd/MM/yyyy HH:mm:ss}" -f $timeStamp
      SELECT eh.Name towercode,tc1.height,tc."100speed",tc."90speed",tc."80speed1",tc."80speed2",


      tc."60speed",tc."30speed",tc."10speed",tc."100direction",tc."90direction",tc."60direction" ,

      tc."30direction",tc."10direction" ,tc."80temperature",tc."10temperature",tc."80humidity",


      FROM(SELECT Date('" + $queryTimeStamp + "')Time) t, [HYREF].[Asset].[ElementHierarchy] eh

          CROSS APPLY [HYREF].[DataT].[TransposeArchive_WindTowerTemplate](eh.ElementID, t.Time,t.Time) tc

          CROSS APPLY [HYREF].[DataT].[TransposeSnapshot_WindTowerTemplate](eh.ElementID)tc1

          WHERE eh.Path like '%\%' and eh.Name ='F4401W0001H0060'



      When I run the above SQL in powershell script, the  timestamp prints incorrect way:


      ForEach-Object : Exception calling "Open" with "4" argument(s): "[PI SDK] The time is invalid." + 10/01/201


      At D:\Windpower\SSE\PI_Windpower_Script.ps1:35 char:11

      + 0..143 | % <<<<  {

          + CategoryInfo          : NotSpecified: (:) [ForEach-Object], MethodInvocationException

          + FullyQualifiedErrorId : ComMethodTargetInvocation,Microsoft.PowerShell.Commands.ForEachObjectCommand


      The below lines are printing in From clause (look at the date format),


      FROM(SELECT Date('" + 10/01/2016 00:00:00 + "') Time) t, [HYREF].[Asset].[ElementHierarchy] eh


          CROSS APPLY [HYREF].[DataT].[TransposeInterpolateDiscrete_WindTowerTemplate](eh.ElementID, t.Time) tc

          WHERE eh.Path like '%\%' and eh.Name like 'F4401%'


      But when I hardcode the value as 10/01/2016 or any other day, the script is running OK. Any suggestion pls? The same variable ($queryTimestamp) executes OK for other SQLs although.



        • Re: PowerShell: Issue passing TimeStamp into PI OLEDB Enterprise query
          Roger Palmen


          It's a better idea to create a new topic op PI Square for a new issue. That way more people wil notice and will be able to assist.

          • Re: PowerShell: Issue passing TimeStamp into PI OLEDB Enterprise query

            Hello Rashmi,


            As Roger said, posting an additional question to a question that has an answer marked correct doesn't generate much attention. Please note that I have branched your new question out of this other one.

            Without knowing of what type $timestamp is, it's a bit difficult to understand what's going wrong for you. I have assumed $timestamp is of type System.DateTime and simplified the query but below is a complete example on how one could execute queries against PI OLEDB Enterprise through PowerShell.


            # Please change the value of $PIServerName to refer your PI Data Archive host
            $PIServerName = "PIServer"
            # Create the connection string
            $PIOLEDB_Connection_String = "Provider=PIOLEDB;Data Source=$PIServerName;Integrated Security=SSPI;"
            # Create TimeStamp of type Syste3m.DateTime and convert it to string
            $TimeStamp = [System.DateTime]::Now
            $queryTimeStamp = "{0:dd/MM/yyyy HH:mm:ss}" -f $timestamp
            # Build the query string
            $PIOLEDB_Query_String = "SELECT DATE('" + $queryTimeStamp + "') as Time"
            # Create OLEDBConnection using the predefined connection string
            $PIOLEDB_Connection = New-Object System.Data.OLEDB.OleDbConnection($PIOLEDB_Connection_String)
            # Open the connection
            # Create the OLEDB Command object, refer the query string and the connection
            $PIOLEDB_Command = New-Object system.Data.OleDb.OleDbCommand($PIOLEDB_Query_String,$PIOLEDB_Connection)
            # Create reader object and execute the command
            $Reader = $PIOLEDB_Command.ExecuteReader()
            # Get the amount of columns 
            $ColumnCount = $Reader.FieldCount
            # Create an object array to hold the results
            $Results = @()
            # Repeat reading while there is content
            while ($Reader.Read()) 
                # Create result object as PSObject
                # There are other options but this helps tremendously with 
                # formatting when returning results to the console
                $Result = New-Object psobject        
                # Read row content column by column
                for ($iColumn = 0; $iColumn -lt $ColumnCount; $iColumn++)
                    # Title
                    $ColumnHeader = $Reader.GetName($iColumn)
                    # Value
                    $ColumnValue = $Reader.GetValue($iColumn).ToString()
                    # Assign result to $Result object
                    $Result | Add-Member -type NoteProperty -name ($ColumnHeader) -value ($ColumnValue)
                # Add $Result to the $Results array
                $Results += $Result
            # Output $Results to console
            # Close the reader and the connection
            2 of 2 people found this helpful
            • Re: PowerShell: Issue passing TimeStamp into PI OLEDB Enterprise query
              Roger Palmen

              Looking at the SQL, you try to pass the date string " + 10/01/2016 00:00:00 + " into the Date function and you pass in a datetime string, starting with "+ ".


              I suspect that dropping the "+ " will help, as you mentioned yourselves by stating that the string "10/01/2016" does work ok.

              1 of 1 people found this helpful