"This step results in a query that is not supported in Direct Query mode" error when creating query

[Anonymous]

Hi there,

I am trying to create a Data Source to an Azure SQL Database.  After creating the data source as a DirectQuery connection, the data from the selected table seems to load in the preview.  When I go to 'Apply' I get the error:

Failed to save modifications to the server.  Error returned: 'An unexpected error occurred (file 'tmsavepoint.ccp', line 1518, function 'TMSavePoint::GetProxyImpl').'.

After closing this error and clicking on the 'Source' step, there is another error:

This step results in a query that is not supported in DirectQuery mode.

I have, in the past, successfully connected to the same source but using the Import mode.  Am I missing a step in setting this up as a DirectQuery connection vs. import?  Has anyone else experienced this?

Thanks

[v-haibl-msft]

@Anonymous

There are currently a few limitations to using Direct Query, you can take a look at this document. You may need to check which part in your Azure DB is not supported in DirectQuery mode.

Best Regards,

Herbert

[Anonymous]

I modified the data source and used a SQL query to eliminate the problem columns with binary data and the error seems to have gone away.  I add a column of type char (although I'm not sure of the specific column datatype), and the query seems successful.

However, when I 'Apply' changes from the query editor, it throws an error " Table LocalDateTable_xxxx ' with ShowAsVariationOnlyProperty set to '1' must be a target of a variation when variation notation is enabled.'.

I also tried removing the column and then creating a relationship between this table and a DateTable (on 2 date columns) and it throws the same error but lists a different 'LocalDateTable_xxxx' name.

Has anyone seen this error before and do you know what is causing it?

Thanks

[Anonymous]

I'm getting this error on all direct queries.  I've tried pointing to SQL server on my local machine and azure sql server.  I've pointed to the simplest table I can imagine.  Every one of them says "This step results in a query that is not supported in Direct Query mode".

[mattjoseph]

Did you ever find a solution to this issue? I've come across the same issue in a report today.

[ankitpatira]

@Anonymous I can't replicate this issue. I have seen that error in past when you're trying to do something that is not supported in directquery otherwise never had issue connecting to auzre sql db. I would suggest you go through this article to follow steps to connect. "Failed to save modification" error seems odd and it is not helpful in troubleshooting. If you're getting it often i would suggest contact MS Support. The only option I can think of is if any modification are being made to DB while you're connecting. Also refer to this post.

[Anonymous]

Thanks @ankitpatira .   Interesting articles.. there are columns in the table I'm querying that store images.  Maybe that is the problem.  Is it possible to connect to views in Power BI (does it know the difference)?