The operation is timed out.. The exception was raised by the IDataReader interface

[C4YNelis]

Hi all,

you've guessed it, I'm receiving an annoying error and I'm currently at a loss how to solve this, other than starting a support ticket or completely rebuilding the report from scratch. However, I'd rather prefer just to solve the issue.

What I am trying to accomplish is (re-)publishing a report that I've migrated from a MySQL datasource to a SQL Server datasource. I've done this under a new name, so as far as the service is concerned, this is a completely new report, operating via a new Gateway. I'm writing republishing though, since I've changed an existing report, rather that starting completely blank.

I've already done the same with approximately twenty different reports, they all work like a charm. This one however, though a fairly basic report at its core, gives me the error below:

The operation is timed out.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action.

There are a few other (unrelated) minor changes to the queries compared to the original version of the report, yet nothing that would warrant an error of sorts.

Now I've been reading a lot of simular threads and tried all of the solutions that were mentioned, yet to no avail.

- I'm not using .csv files, so imho UTF is not likely to be an issue here. The only source is a SQL Server which I use succesfully for many other reports too.

- I've tried creating new queries,

- I've deleted the report from the service (including the dataset), cleared the cache in PBI and republished,

- I've double, even triple checked all my datatypes, there's nothing special there (there are only a few very basic relations to begin with),

- I've included extra checks to remove all null values,

- there are no dashboard tiles

It's worth mentioning that the errors in most threads are slightly different from mine, in that I haven't found any thread yet mentioning the operation timed out.

I'm receiving this error always when refreshing in the service (manually or scheduled), while refreshing in PBI works just fine. Also (not very surprising, reading the error message), while refreshes in PBI take at most one minute, it takes about 15 minutes in the service before it throws the error.

If anyone has a good suggestion as to something helpful I might try, I'd be most grateful.

Cheers,
Niels

[Ananaqaco]

Did you find any solutions? I have same issue and can't update reports.

[C4YNelis]

Hi @Ananaqaco,

other than what I wrote before, I have no more related advice I can give you.

For me the issues was solved by cleaning all my (text-)input, using the Transform function in Power Query, because apparently there were unsupported characters in there.

However, do note that for me it was only the one report that threw an error. If you have multiple reports with the same issue, it seems to me that that is a different issue.

Good luck solving the problem!

Cheers,

Niels

[C4YNelis]

Hi @GilbertQ ,

thank you for your reply!

I had not thought about the gateway to be honest. I'm also not sure whether it could be the culprit. The gateway definitely has more than enough resources. It's a brand new machine, 20 core cpu, 128Gb RAM, .M2 SSD's, plenty of space (TB's) and a decent network connection. The SQL Database is installed on the same machine and is not used for other purposes. Other reports refresh just fine from the same gateway and also the error is consistent in its behavior. If it were a resource issue, I'd be expecting more erratic behavior. On top of that, it really is a very simple report, which is why it's puzzling me why specifically this report throws an error.

I have enabled the logs, refreshed the dataset (with an error) and dug a bit through the logs. I must admit I'm not very familiar with the logs, but I did find several errors in the mashup log.

It is throwing (nonfatal) errors trying to write to the local cache, saying it cannot access path: power bi gateway cannot find the path below:

C:\\Windows\\ServiceProfiles\\PBIEgwService\\AppData\\Local\\Temp\\Microsoft\\MashupProvider\\Temp

Followed by a few more specific errors where it cannot access specific cache files:

C:\\Windows\\ServiceProfiles\\PBIEgwService\\AppData\\Local\\Temp\\Microsoft\\MashupProvider\\Cache\\LQlwCgAXs=\\Cache.Version (I shortened the identity key)

Specifically searching for the IDataReader, I have found that it is complaining about SSL certificates not being signed by a trusted entity. However, how this would translate to a solution is puzzling me. I find it peculiar that all the other reports do refresh just fine? They access the same resources with the same credentials?

Action":"SimpleDocumentEvaluator/GetResult<IDataReaderSource>","HostProcessId":"14448","PartitionKey":"Section1/Query1","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.ResourceEncryptedConnectionException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=3...5\r\nMessage: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - De certificaatketen is verleend door een niet-vertrouwde instantie.)\r\nStackTrace:\n   bij Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.<>c__DisplayClass4_0.<BeginGetResult>b__0(EvaluationResult2`1 result)\r\n\r\nInnerException\r\nException:\r\nExceptionType: Microsoft.Data.SqlClient.SqlException, Microsoft.Data.SqlClient, Version=1.13.20136.2, Culture=neutral, PublicKeyToken=2...5\r\nMessage: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - De certificaatketen is verleend door een niet-vertrouwde instantie.)\r\nStackTrace:\n   bij Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   bij Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n   bij Microsoft.Data.SqlClient.TdsParserStateObject.SNIWritePacket(SNIHandle handle, SNIPacket packet, UInt32& sniError, Boolean canAccumulate, Boolean callerHasConnectionLock)\r\n   bij Microsoft.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate)\r\n   bij Microsoft.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate)\r\n   bij Microsoft.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec, FeatureExtension requestedFeatures, SessionData recoverySessionData, Nullable`1 fedAuthFeatureExtensionData, SqlClientOr ...

[GilbertQ]

Hi @C4YNelis 

Can you confirm that you have got enough disk space on the C: Drive?

Can you also make sure that the service account running the Gateway service has got full read/write access to the temp location?

[C4YNelis]

Hi @GilbertQ ,

I have around 600gb available on the C drive, which should be more than enough.

The Temp directory itself does not exist, however, other reports refresh just fine. Shouldn't they be suffering the same problem if this would have been a rights issue?

To answer your question: the default service account (PBIEgwService) has full rights in the folder and all its subfolders.

What's more, I've also checked the logs for one of the other reports (that does run just fine) and I've found the exact same error, which leads me to believe this is an unrelated error.

Personally I'm inclined to believe it is something in the report rather than the Gateway.

cheers,

Niels

[GilbertQ · Accepted solution]

Hi @C4YNelis 

Yeah that does sound a little weird, it might be something in the data source. Is this the only report which uses this data source?

[C4YNelis]

Hi @GilbertQ ,

I'm closing this case, since I cannot get what exactly is the issue. Imho it cannot be the datasource, since that is a SQL Server database, which works just fine for all other reports. There are a few unique tables not being used by other reports, but like I mentioned before, I've doublechecked all data types, explicitely checked for nulls / 0 / empty string values, trimmed and removed duplicates (not that there were any afaik) or anything else that might be conflicting in the existing relations.

In the end:

- I started a blank report

- literally copied all queries to the new report (no modifications)

- set the report options straight (to be in line with the original -> I still used auto-dates for instance in the old report)

- recreated the model to be an exact copy

- reapplied the themefile (which might be slightly different from the old one)

- recreated measures and an incidental calculated column (the only change I made, I made here, by moving the measures to measuretables)

- copied all visuals and dragged the new measures to substitute the old broken measures so the columns would be reflected as desired.

- repopulated the filterpane again

and finally republished under the exact same name so it would overwrite the problematic dataset.

So in short, I created a near exact copy where I didn't change a thing, except for the measure columns and the theme-file.

Guess what.. it works now? Don't ask me why. 😉 I know Microsoft claims .pbix files cannot be corrupt, yet something is obviously off here.

Anyway, I think it's not worth investing more time. I'm pretty sure we wouldn't be able to get down to the root cause.

Thank you very much for your help!

Cheers,
Niels

[GilbertQ]

Hi @C4YNelis 

Thanks for letting us know how you got it resolved!

[C4YNelis]

Hi all,

I owe you a little correction here.

As I mentioned before, after reuploading it finally refreshed just normal. Or so I thought... It turns out it was a one time thing only. As soon as the regular refresh schedule kicked in, it started failing again. I have a ton of other work to do too, so I left it for a few days, but today I thought I'd take another crack at it, mainly because it's bugging me that I didn't find the root cause of the error. After checking all the obvious stuff again to no avail, I decided to go though all the columns in every source query, to see what's going on there. It wouldn't be the first time I run into unexpexted problems due to a malaligned type.

So, checking all numerical columns for non-numerical values (beyond the first 1000 rows I mean), checking all dates, etc. Nothing special there, however, I did see that I have several large comment columns, which have a huge load of external (also manual) input from many different countries and languages that I use. I also found several uncommon (to me) symbols, that made me wonder whether the conversion from MySQL to SQL Server (where I use the Nvarchar data type (Unicode characters)), which MySQL does not support in that same manner, might have something to do with it.

So my first idea was to just clean the columns with custom input (clean removes all control characters), besides the regular trimming that I already did. And guess what? Now it does work (so far).

So, long thread (and post) short: my advise, besides all the obvious tips that are already out there (checking data types and relations and so on), is to also clean all your custom input data if you didn't already. Clean as in: with the transform function.

Hopefully this still helps someone who one day runs into this same obsure problem.

Cheers,

Niels

[GilbertQ]

Hi @C4YNelis 

It appears that your error is from your Gateway. I would enable the logs on your Gateway and see what is happening there when refreshing the dataset?

It could be either that your gateway has not enough CPU, Memory and Bandwidth to the SQL Server or that the SQL Server is not returning the data fast enough.

Troubleshoot gateways - Power BI | Microsoft Docs