Oracle Server Down

[Steve_Lang]

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.

[Anonymous]

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]

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 · Accepted solution]

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

[Expiscornovus]

Hi @Steve_Lang,

I would say you are on the right track, I would also use error handling for this:

https://learn.microsoft.com/en-us/power-query/error-handling

Have you tried putting next server connection in the otherwise part of your expression?

https://stackoverflow.com/questions/78102909/using-try-and-otherwise-in-powerquery