5 Replies Latest reply on Apr 13, 2017 8:25 AM by BrunoDevos

    ft_Summarize not visible in ODBC 3.01.05

    BrunoDevos

      When using PI SQL Commander 2016 R2, I can see a table Data.ft_Summarize.

      When on the same computer I use PI ODBC Driver 3.01.05, I can't see see the table Data.ft_Summarize.

      Error message:

      HY000 0 [PIOLEDBENT] Table/view/table-valued function name 'xxx.Data.Summarize' is invalid. 
      

       

      How can I get access to it through ODBC?

        • Re: ft_Summarize not visible in ODBC 3.01.05
          rsun

          Hi Bruno,

          We have a newer PI ODBC Driver for download https://techsupport.osisoft.com/Downloads/File/269d4ced-c448-4cf1-963e-7087f959802d.

          Could you try this and see if you can see Data.ft_Summarize.

          Thanks.

          Ricky

            • Re: ft_Summarize not visible in ODBC 3.01.05
              BrunoDevos

              I installed PI ODBC 3.02.16300.01 and have the same problem: I can't see see the table Data.Summarize.

              Error message:

              HY000 0 [PIOLEDBENT] Table/view/table-valued function name 'xxx.Data.Summarize' is invalid.   
              
                • Re: ft_Summarize not visible in ODBC 3.01.05
                  Marcos Vainer Loeff

                  Hello Bruno,

                   

                  I think the problem is related to the PI OLEDB Enterprise. Could you access the machine where PI SQL DAS is installed and run a query using PI OLEDB Enterprise?

                   

                  What are the versions of the PI OLEDB Enterprise and PI SQL DAS?

                   

                  On one of our internal call, I've found the following comment: "Transpose functions generated with the newer PIOLEDBENT 2016 are not backward compatible with the 2012 version, so any views using the newer TVF will be seen as invalid in the 2012 version. The SQL DAS PIOLEDBENT needs to be updated to the 2016 version."

                   

                  We need to investigate if this issue is the same of yours. Let's start by updating PI OLEDB Enterprise and PI SQL DAS to the latest version. If that doesn't work, I would rebuild the tables again.

                    • Re: ft_Summarize not visible in ODBC 3.01.05
                      BrunoDevos

                      Hello, I upgrade PI OLEDB Enterprise and PI SQL DAS to the latest version. The situation is still the same: I can see table Summarize in OLEDB, but I can't see it in ODBC.

                       

                      After upgrading DAS, I get the following error message:

                       

                      [\\xxx\Smart_District_Heating\Load Forecast\CZ\Havirov|DHN Load] [-11059] No Good Data For Calculation

                       

                      The query was:

                      select *

                                              from Smart_District_Heating.Data.ft_Summarize

                                             where ElementAttributeID = '00015cce-0000-0000-1bd3-ffffffffffff'

                                             and StartTime = N'2016-10-01'

                                             and EndTime   = N'2017-03-23 05:00:00'

                                             and TimeStep  = N'1h'

                                             and TimeType  = N'MostRecentTime' /*EarliestTime*/

                                             and month(Time) not in (5,6,7,8,9)

                                             and SummaryType = N'Average'

                                             AND CalculationBasis = N'TimeWeighted'

                                             ORDER BY 1

                       

                      But when using the Archive:

                      select *

                                              from Smart_District_Heating.Data.Archive

                                             where ElementAttributeID = '00015cce-0000-0000-1bd3-ffffffffffff'

                                             and Time >= N'2016-10-01'

                                             and Time   <= N'2017-03-23 05:00:00'

                                             and month(Time) not in (5,6,7,8,9)

                                             ORDER BY 1

                      I get:

                       

                      ElementAttributeID              Time               Value       ValueInt ValueDbl    ValueStr     ValueGuid ValueDateTime Status Annotated IsGood Questionable Substituted

                      ------------------------------------ ----------------------- ---------------- -------- ---------------- ----------------- --------- ------------- ------ --------- ------ ------------ -----------

                      00015cce-0000-0000-1bd3-ffffffffffff 2016-10-01 00:00:00.000 23,8199996948242     23,8199996948242 23.81999969482422                    192FalseTrue   False   False
                      00015cce-0000-0000-1bd3-ffffffffffff 2016-10-01 01:00:00.000 27,4300003051758     27,4300003051758 27.43000030517578                    192FalseTrue   False   False
                      00015cce-0000-0000-1bd3-ffffffffffff 2016-10-01 02:00:00.000 22,0499992370605     22,0499992370605 22.04999923706055                    192FalseTrue   False   False
                      00015cce-0000-0000-1bd3-ffffffffffff 2016-10-01 03:00:00.000 24,5799999237061     24,5799999237061 24.57999992370606                    192FalseTrue   False   False
                      00015cce-0000-0000-1bd3-ffffffffffff 2016-10-01 04:00:00.000 28,6800003051758     28,6800003051758 28.68000030517578                    192FalseTrue   False   False
                • Re: ft_Summarize not visible in ODBC 3.01.05
                  BrunoDevos

                  OK, after upgrading PI OLEDB, the problem was solved.