none
Unable to refresh SQL Server data source through Data Management Gateway

    Question

  • I just installed the version 1.1.5226.8 of Data Management Gateway and tried to refresh a simple query on a table connected to SQL Server, with no transformations in Power Query.

    This is the error I obtain:

    Errors in the high-level relational engine. The following exception occurred while the managed IDataReader interface was being used: transfer service job status is invalid.

    I am wondering whether my Power BI is still not updated to handle such a connection type, or there could be something else not working?

    I correctly created the data source in admin panel following instructions in Release Notes, and test Power Query connection is ok.


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Friday, May 02, 2014 11:17 AM

Answers

All replies

  • I have an update.

    A simple transformation works (just remove columns).

    Another query containing a calculated column with the function DateTimeZone.FixedUtcNow() is reporting the error.

    I would guess that if query unfolding is not possible for the entire query, it doesn't work. Is this a limitation of the current release?

    Thanks,

    Marco


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Friday, May 02, 2014 1:05 PM
  • The code that does the refresh should be basically the same as the code that runs the query inside Excel, so it wouldn't have any different folding characteristics during refresh. I've forwarded this on to the team that built the refresh service.
    Friday, May 02, 2014 1:30 PM
  • Hi Marco,

    Thanks for reporting this issue. Can you refresh the workbook to ensure this is not intermittent issue? Meanwhile, do you see any correlational ID so that we can investigate on our side? Thanks.

    Samuel

    Friday, May 02, 2014 1:39 PM
  • I tried 3 times, in different moments, and I Always had the same error. Here are the correlation IDs:

    f5c76afb-f818-4f71-9294-d8b46453619a

    a498e24d-a1d0-4c8d-accb-96ac256c25c0

    380d8eea-393c-4dce-b16d-f70a0a9a4121

    Another workbook, which is very similar but doesn't have such a calculated column, refresh correctly


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Friday, May 02, 2014 2:42 PM
  • I made other tests and I found important information (maybe there is a bug, but read the following).

    The functions DateTime.LocalNow and DateTime.FixedLocalNow work correctly, generating these statements to SQL Server:

        convert(datetime2, '2014-05-03 06:37:52.1135108') as [LocalNow],
        convert(datetime2, '2014-05-03 06:37:52.0525061') as [FixedLocalNow],

    The functions DateTimeZone.FixedLocalNow, DateTimeZone.FixedUtcNow, DateTimeZone.LocalNow, and DateTimeZone.UtcNow stop the scheduled refresh with the error I mentioned in my previous messages, generating these statements to SQL Server:

        '2014-05-03 06:37:52.0525061+02:00' as [TZFixedLocalNow],
        '2014-05-03 04:37:52.0525061+00:00' as [TZFixedUtcNow],
        '2014-05-03 06:37:52.1135108+02:00' as [TZLocalNow],
        '2014-05-03 04:37:52.1135108+00:00' as [TZUtcNow]

    I solved the issue by placing the DateTimeZone calls after a Table.Buffer call, so query folding does not translate in SQL these functions. However, it seems like something to fix.


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Saturday, May 03, 2014 5:55 AM
  • Thanks for drilling into this; your analysis looks very useful. The SQL statements you've provided here came from doing the refresh, yes? Are they the same as the SQL statements that you get when refreshing the query directly from within Excel?
    Saturday, May 03, 2014 12:31 PM
  • Actually I only captured SQL Profiler by doing the refresh with Excel (or within the Power Query window). I didn't catch the SQL code sent during a scheduled refresh (it's hard to know when it will reach the server). I also noted that when I use DateTimeZone the scheduled refresh action takes very long to complete (10-15 minutes), whereas it requires less (minutes) to be refreshed when DateTimeZone is not used.I hope it helps.

    Marco


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Saturday, May 03, 2014 12:41 PM
  • I see; thanks. It would be interesting to see whether the SQL being run from the gateway looks any different. One way you could capture that would be to create a new user for use solely with the gateway and then setup SQL Profiler to capture queries for just that user. Alternatively, you might be able to filter on just that client machine name.

    Saturday, May 03, 2014 12:53 PM
  • I can confirm that the query sent during scheduled refresh is the same.

    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Saturday, May 03, 2014 6:37 PM