1

While trying to load the FACT table (~15M Rows) in Analysis Service from a Azure SQL Database, I always get this error after one hour:

Failed to save modifications to the server. Error returned: 'A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.);An existing connection was forcibly closed by the remote host. The exception was raised by the IDataReader interface.

I can't seem find the setting to change the timeout Azure SQL Server... Does anybody know where I can find this?

Souf
  • 369
  • 2
  • 16
  • We solved this issue by partitioning the data while importing. – Souf Feb 01 '19 at 14:58
  • Loading the data in batches may also do the trick specially when the tier of the Azure SQL Database is not Premium. – Alberto Morillo Feb 01 '19 at 19:52
  • There is an ExternalCommandTimeout server property that defaults to 3600 seconds (1 hour). Is that when it failed? I would have expected the error to say “timeout”. Since it didn’t maybe just a network glitch?? – GregGalloway Feb 02 '19 at 01:33
  • See if there are any errors in the event log of the database: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-event-log-azure-sql-database?view=azuresqldb-current – David Browne - Microsoft Feb 03 '19 at 16:58

1 Answers1

0

It can be that timeout is set on SSAS side rather than Azure SQL Database?

If you are using modern "Get Data experience (Power Query)" take a look on this thread:

SSAS tabular model timeout raised during processing

To check this, open your model source code by clicking on Model.bim and then F7 and search for keyword "timeout".

The target is to find something like this:

"options": {
          "commandTimeout": "PT1H"
        },

or

Source = Sql.Database("serverName.database.windows.net","databaseName",
[CommandTimeout=#duration(0, 2, 0, 0)])
Alexander Volok
  • 5,630
  • 3
  • 17
  • 33