Forum Discussion
How to connect to any available database when primary one is offline
Super UserHi MIN ,
You should be able to start your M code like this:
let
Source1 = SQL Server 1,
Source2 = SQL Server 2,
pickSource = try Source1 otherwise Source2,
xformSteps = // the rest of your code, using pickSource as the previous step in the first step
Pete
Hello BA_Pete ,
Thank you so much for your suggestion.
However, "try" statement is still sticking to Source1.
Regards,
Min
- BA_Pete
Hi MIN ,
I don't see the issue here, it's just asking you to confirm the credentials isn't it?
Confirm the credentials and these will be saved in the PBIX Data Source area, then the switch should work fine.
Pete
Hi BA_Pete ,
Actually, I'm trying to connect to any of 2 identical databases hoping that 1 of them is accessible in case the latter is offline.
For that reason, I shut down the db1 (Source1) -
to test if "try" statement catchs the error (offline status) and routes to Source2.
Instead, db1 prompts again to provide credential when it goes offline.
On the other hand, I'm not sure it is a correct way to setup a dynamic connection on PBI.
Regards,
Min
- BA_Pete
Hi MIN,
I wonder whether adding a step below each source will force a recognisable error to initiate the switch. Try something like this:
let Source1 = SQL Server 1, chgTypes1 = Table.TransformColumnTypes(Source1,{{"numberColumn1", Int64.Type}}), Source2 = SQL Server 2, chgTypes2 = Table.TransformColumnTypes(Source2,{{"numberColumn1", Int64.Type}}), pickSource = try chgTypes1 otherwise chgTypes2, xformSteps = // the rest of your code, using pickSource as the previous step in the first stepPete