This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(140.8, 95%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EL%3C/text%3E%3C/svg%3E)
We experience a very strange issue when uploading data from our SQL server to our analysis service, which we are hoping to get a bit of sparring on.
Briefly explained, the issue is that simple select queries for our largest tables terminate successfully but they only deliver a partial set of the rows ~80-90%.
We have a view called factView.[sales quotations sent] with 72901240 rows:
Our full-load partition in our analysis service is defined as:
Executing this returns the following: (it will be a variable amount of rows every execution. Sometimes 65, sometimes 68m, etc.):
The majority of the time, it complete early and "successfully". We did succeed in getting the following error once, but usually the job just terminates without any error:
The connection either timed out or was lost.
We run our SQL instance on an azure Hyperscale: Standard-series (Gen5) and our analysis service on an azure analysis service s4 instance as well. The tables are configured with clustered columnstores indexes, in case that's significant.
It does not matter if we scale the SQL server to any number of additional cores and neither which size the analysis service is.
We have also tried increasing the Connect retry count for the Data Link, which yielded no results.
What can cause a query to not complete successfully, but the analysis service thinks it did?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(227.2, 46%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENN%3C/text%3E%3C/svg%3E)
@Laurids
Just checking in to see if you could accept the answer below. And, if you have any further query do let us know.
Problem solved.
In case anyone else runs into a similar situation, the solution for us was to connect to the AAS through SSMS and find the ExternalCommandTimeout setting in Properties>General>Tick Show Advanced, as this was set to 3600 second, which not so coincidentally happened to be the time the queries timed out with a successful completion.
@Laurids
I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .
**Ask:**The user is experiencing an issue where simple select queries for their largest tables in SQL Server only deliver 80-90% of the rows when uploading data to Azure Analysis Services. The job often completes early and “successfully” without errors, but sometimes it returns a timeout error.
Solution: The problem was solved by connecting to Azure Analysis Services through SSMS and adjusting the ExternalCommandTimeout setting to a higher value.
If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.
If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.
Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.