error-handling - Excel Power查询，对后备连接/OleDb数据源的错误处理

Question

我有一个PowerQuery连接到DB2数据源，但是由于某种负载平衡，DB Server会定期更改(ip也更改)，因此我无法事先知道应该使用哪个数据源(IP)。尝试一下，看是否出错，然后我必须使用另一个，我在PowerQuery中搜索了错误处理，并找到了一些错误处理的示例，但该示例不适用于我的情况，大多数在连接后处理了错误出现或正在防止找不到缺少的列或文件的错误，我尝试根据我的情况调整示例，但未能做到。

我想要的只是尝试一个IP，如果失败，则使用另一个。

let

//fParametros("ParamQuery",1) is the "standard" Server/Ip address (provider=IBMDADB2.IBMDBCL1;data source=CP3;location=pn8us7ldbcp3.us.mycompany.com:5912)

dbSource = fParametros("ParamQuery",1),

//fParametros("ParamQuery",5) is the "Alternate" Server/Ip address (provider=IBMDADB2.IBMDBCL1;data source=CP3;location=pn8us7ldbcp3h.us.mycompany.com:5912)

AltdbSource = fParametros("ParamQuery",5),
pOrden = Text.From(fParametros("ParamQuery",2)),

//Create the query
dbQuery = "SELECT SAPCP3.vbak.VBELN SO , SAPCP3.vbap.posnr PoLine , SAPCP3.vbep.ETENR Sch_Line , SAPCP3.vbap.matnr Part_Number,SAPCP3.makt.maktx Description,SAPCP3.vbap.kwmeng Qty ,SAPCP3.vbep.BMENG Conf_qty ,SAPCP3.vbap.vrkme UOM ,SAPCP3.vbap.netpr SalesPrice ,SAPCP3.vbap.kpein LotSize FROM SAPCP3.vbak JOIN SAPCP3.vbap ON SAPCP3.VBAp.VBELN = SAPCP3.VBAK.VBELN JOIN SAPCP3.vbep ON SAPCP3.vbep.vbeln = SAPCP3.vbak.vbeln AND SAPCP3.vbap.posnr  = SAPCP3.vbep.posnr JOIN sapcp3.makt ON sapcp3.vbap.matnr=sapcp3.makt.matnr WHERE SAPCP3.VBAK.VKORG = '4000' AND (SAPCP3.vbep.edatu >= '20190701') AND SAPCP3.vbak.VBELN ="& pOrden & " ORDER BY SAPCP3.vbak.VBELN",

//Get the data
Source = OleDb.DataSource(dbSource, [Query=dbQuery]),

//Failed Attempt to handle the error:    

TestForError= try Source,

//next line does not work, I get error saying Source is already defined/declared
Source = if TestForError[HasError] then OleDb.DataSource(AltdbSource, [Query=dbQuery]) else OleDb.DataSource(dbSource, [Query=dbQuery])

in

Source

我还尝试了以下内容:

.
.
.

//Get the data
Source = OleDb.DataSource(dbSource, [Query=dbQuery]),

//Failed Attempt to handle the error:
TestForError= try Source,

Output = if TestForError[HasError] then OleDb.DataSource(AltdbSource, [Query=dbQuery]) else OleDb.DataSource(dbSource, [Query=dbQuery])

in

Output


//This last part works if the dbSource is correct, but if it is not it doesnt catch the error and gives me the Connection error shown below:

DataSource.Error: OLE DB: SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "172.16.0.1". Communication function detecting the error: "connect". Protocol specific error code(s): "10061", "", "". SQLSTATE=08001 Details: DataSourceKind=OleDb DataSourcePath=data source=CP3;location=pn8us7ldbcp3.us.mycompany.com:5912;provider=IBMDADB2.IBMDBCL1 Message= SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "172.16.0.1". Communication function detecting the error: "connect". Protocol specific error code(s): "10061", "", "". SQLSTATE=08001 ErrorCode=-2147467259

我想连接到dbSource(172.16.0.1)，如果不可能，则连接到AltdbSource(172.16.0.2)

目前，我有2个完全相同的excel表格，如果用户遇到连接错误，则需要打开其中一个，我希望它自动更改。

Answer 1

我猜测TestForError = try OleDb.DataSource(BadSource)无法正常工作，因为OleDb.DataSource(...)返回一个表值，而不是在尝试枚举行时仅产生错误。

如果更改TestForError以钻入第一行(如果存在)怎么办:

TestForError= try Source{0}?,

如果仍然无法解决问题，请尝试在第一行中索引到表格单元中。