Forum Discussion

Steve_Lang's avatar
Steve_Lang
New Member
1 year ago
Solved

Oracle Server Down

I'm using Power BI to monitor multiple oracle environments for payrolls. The problem comes when one of the environments is down and the transfer of data from all environments stops.

I'm using Report server.


Is it possible to ignore an environment when it's down but still connect to the other environments, cheers for any help

Hi have tried a lightweight check

= try Oracle.Database(OraEnv, [Query="SELECT 1 FROM DUAL"]) otherwise null

 

Where OraEnv is the Oracle server.

And i still get the error message.
DataSource.Error: Oracle: ORA-12541: TNS:no listener
Details:
DataSourceKind=Oracle
DataSourcePath=******
Message=ORA-12541: TNS:no listener
ErrorCode=-2147467259

I know the server is offline and i should not be able to connect, but i don't want the fact that this one server is down stop the monitoring of the other servers.

I can't see anything in Report Server to set ignore if errored.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Steve_Lang ,

     

    I continued to test and found that the TestForError step should deepen into the table, otherwise it returns False even though the service is available.

    TestForError = try Source[<One table name in your database>],

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Steve_Lang ,

     

    Please try the logic shared in the blog:

    Chris Webb's BI Blog: Returning Error Messages As Well As Results In Power Query

    The data source I'm testing with is SQL Server database, but that shouldn't affect the final result.

    let
  //Connect to SQL Server
  Source = Sql.Database("localhost", "AdventureWorksDW2019"), 
  //Get DimDate table
  dbo_DimDate = Source{[Schema = "dbo", Item = "DimDate"]}[Data], 
  //Remove all other columns except EnglishDayNameOfWeek
  #"Removed Other Columns" = Table.SelectColumns(dbo_DimDate, {"EnglishDayNameOfWeek"}), 
  //Get distinct values from this column
  #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"), 
  //Output in case of error
  AlternativeOutput = #table(type table [EnglishDayNameOfWeek = text], {}), 
  //Does the Removed Duplicates set error?
  TestForError = try #"Removed Duplicates", 
  Output = 
    if TestForError[HasError] then
      //In case of error return empty table
      //and attach error message in metadata
      (AlternativeOutput meta [message = TestForError[Error]])
    else
      //If there’s no error
      //just return the table plus a message
      (#"Removed Duplicates" meta [message = "Query executed successfully"])
in
  Output

    I've turned off the service here.

    We can find the returned error in the metadata.

    Finally based on it you can control the output.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • Steve_Lang's avatar
      Steve_Lang
      New Member
      1 year ago

      HI,

      It seems to error when calling Oracle.Database(...)


      let
      Source = Oracle.Database("..server.."),
      TestForError = try Source,
      Output =
      if TestForError[HasError] then
      Table.FromRecords({}, {..Field names..})
      else
      let
      Source2 = Oracle.Database("..server..", [Query="..SQL..", HierarchicalNavigation=true]),
      #"Changed Type" = Table.Transf... etc
      #"Final Transformation" = Table.AddCol..
      in
      #"Final Transformation"
      in
      Output

      I have created a pararmeter to switch on or off and if it does not try the Oracle Server i have no Error, when the Parameter ServerUp is set to False

      if ServerUp = true then try Oracle.Dat....

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Steve_Lang ,

         

        I continued to test and found that the TestForError step should deepen into the table, otherwise it returns False even though the service is available.

        TestForError = try Source[<One table name in your database>],

         

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly --  How to provide sample data in the Power BI Forum