I try to load a simple table through Power BI, but report error "Data Type of column cntnum with searchable property UNSEARCHABLE should be SEARCHABLE or ALL_EXCEPT_LIKE. You can override the supported data types from ODBC driver using SQLGetTypeInfo." How to set data type because Power BI does not call SQLGetTypeInfo API at all.
Here is the error log:
SimpleDocumentEvaluator/GetResult/Compile {"Start":"2018-01-29T03:14:27.4300035Z","Action":"SimpleDocumentEvaluator/GetResult/Compile","HostProcessId":"8744","ProductVersion":"2.54.4970.961 (PBIDesktop)","ActivityId":"7e8bcd2a-e17e-4d3f-a592-a2b2c71080cd","Process":"Microsoft.Mashup.Container.NetFX40","Pid":15572,"Tid":1,"Duration":"00:00:00.0258563"}
SqlTranslator/SqlParser/Parse {"Start":"2018-01-29T03:14:27.4593377Z","Action":"SqlTranslator/SqlParser/Parse","HostProcessId":"8744","SQL":"
SELECT [t0].[cntnum]
FROM
(
(SELECT * FROM [test2])
)
AS [t0]
GROUP BY [t0].[cntnum] ","ProductVersion":"2.54.4970.961 (PBIDesktop)","ActivityId":"7e8bcd2a-e17e-4d3f-a592-a2b2c71080cd","Process":"Microsoft.Mashup.Container.NetFX40","Pid":15572,"Tid":1,"Duration":"00:00:00.0480538"}
SqlExpressionTranslator/Translate {"Start":"2018-01-29T03:14:27.5080472Z","Action":"SqlExpressionTranslator/Translate","HostProcessId":"8744","IsRecognized":"True","Result":"(environment) => Table.RenameColumns(let
t5224 = Table.RenameColumns(Table.PrefixColumns(environment[test2], \"test2\"), {{\"test2.cntnum\", \"t0.cntnum\"}}),
t5228 = Table.Group(t5224, {\"t0.cntnum\"}, {}),
t5229 = Table.SelectColumns(t5228, {\"t0.cntnum\"})
in
t5229, {{\"t0.cntnum\", \"cntnum\"}})","ProductVersion":"2.54.4970.961 (PBIDesktop)","ActivityId":"7e8bcd2a-e17e-4d3f-a592-a2b2c71080cd","Process":"Microsoft.Mashup.Container.NetFX40","Pid":15572,"Tid":1,"Duration":"00:00:00.0899707"}
SimpleDocumentEvaluator/GetResult/Evaluate {"Start":"2018-01-29T03:14:27.4559112Z","Action":"SimpleDocumentEvaluator/GetResult/Evaluate","HostProcessId":"8744","ProductVersion":"2.54.4970.961 (PBIDesktop)","ActivityId":"7e8bcd2a-e17e-4d3f-a592-a2b2c71080cd","Process":"Microsoft.Mashup.Container.NetFX40","Pid":15572,"Tid":1,"Duration":"00:00:00.1553596"}
OdbcQuery/FoldingWarning {"Start":"2018-01-29T03:14:27.6225023Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"8744","Function Name":"Group","ProductVersion":"2.54.4970.961 (PBIDesktop)","ActivityId":"7e8bcd2a-e17e-4d3f-a592-a2b2c71080cd","Process":"Microsoft.Mashup.Container.NetFX40","Pid":15572,"Tid":1,"Duration":"00:00:00.0000226"}
OdbcQuery/FoldingWarning {"Start":"2018-01-29T03:14:27.6225607Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"8744","ErrorMessage":"Data Type of column cntnum with searchable property UNSEARCHABLE should be SEARCHABLE or ALL_EXCEPT_LIKE. You can override the supported data types from ODBC driver using SQLGetTypeInfo.","ProductVersion":"2.54.4970.961 (PBIDesktop)","ActivityId":"7e8bcd2a-e17e-4d3f-a592-a2b2c71080cd","Process":"Microsoft.Mashup.Container.NetFX40","Pid":15572,"Tid":1,"Duration":"00:00:00.0003110"}
OdbcQueryDomain/ReportFoldingFailure {"Start":"2018-01-29T03:14:27.6234957Z","Action":"OdbcQueryDomain/ReportFoldingFailure","HostProcessId":"8744","Exception":"Exception:
ExceptionType: Microsoft.Mashup.Engine1.Runtime.FoldingFailureException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Message: Folding failed. Please take a look the information in the trace.
StackTrace:
at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQuery.Group(Grouping grouping)
at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query, Func`2 operation)
","ProductVersion":"2.54.4970.961 (PBIDesktop)","ActivityId":"7e8bcd2a-e17e-4d3f-a592-a2b2c71080cd","Process":"Microsoft.Mashup.Container.NetFX40","Pid":15572,"Tid":1,"Duration":"00:00:00.0004270"}
