SQL statement with DirectQuery option does not load data for creating reports

[pupreti]

Hi All,

I am working on power bi desktop custom connector and i am using Presto as the datasource, i am passing SQL statement with direct query option. I am able to see the data but when i load the data to create reports then desktop throws below error.

"We couldn't connect to your DirectQuery data source or internal model. Double-check that your server and database names are correct, and that you have permission to access them. DirectQuery error: DirectQuery may not be used with this data source – please consider moving to a supported data source or upgrading the SQL Server data source to the latest available version."

On my connctor side code, i am calling Odbc.Query(ConnectionString, SQL) if SQL is not null and if its null then i am calling Odbc.DataSource function. Do you think am i missing any optional property in Odbc.query or is there anything wrong in my code because of which i am getting this error while loading reports?

Below is the snippet of my code:

Connect = 
            if (SQL = null) then 
                Odbc.DataSource(ConnectionString, [
                HierarchicalNavigation = true,
                HideNativeQuery = false,
                TolerateConcatOverflow = true,
                SqlCompatibleWindowsAuth = false,
                ClientConnectionPooling= true,
                SoftNumbers = true,
                SqlCapabilities = [
                    PrepareStatements = true,
                    SupportsTop = true,
                    Sql92Conformance = 8,
                    SupportsNumericLiterals = true,
                    SupportsStringLiterals = true,
                    SupportsOdbcDateLiterals = true,
                    SupportsOdbcTimeLiterals = true,
                    SupportsOdbcTimestampLiterals = true
                    ]
                ])
            else
                Odbc.Query(
                ConnectionString, 
                SQL)
    in
        Connect;

Can anyone suggest me, is there anything worng in my code, or is this issue from Power BI side?

Please suggest anything that anyone has. Thank you so very much in advance. 

PS: I am using the latset Power BI version 2.82.5858.1161 64-bit (June, 2020) and directquery option is enable in my code.

[v-eachen-msft]

Hi @pupreti ,

Your codes seem to be correct, here is the link about Odbc.Query() for your reference.

You could launch Power BI Desktop as an administrator(right click on the power bi app, then select "run as admin"), and then make the connection.

[pupreti]

Hi @v-eachen-msft ,

I had tried launching power bi in administrator mode also but it still shows the same error.

[lbendlin]

I think they are complaining about your SQL server version. Do you know what that is?

[pupreti]

Hi @lbendlin ,

I am using Presto, not directly SQL and also i am not querying to any table of SQL, i am querying to hive Although, my presto server has sql as a connector and has sql installed of version 5.1.73.

Do you think it might be causing the issue?