Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
darentengdrake
Resolver II
Resolver II

OLE DB or ODBC error: [DataSource.Error]

Hi,

 

I've got this error code while applying query changes:

 

OLE DB or ODBC error: [DataSource.Error] An error happened while reading data from the provider: '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.)'.

 

I am able to load the data into query editor and view it but whenever I try to apply the query, it loads for a few minutes and this error pops up. I have tried changing the connection and command timeout but it's still not working.

 

Any idea what's going on here and how to fix it?

 

Thanks!

Daren

1 ACCEPTED SOLUTION

Hi,

 

I tried changing the parameters previously and it didn't work.

 

However, I did a workaround on it. My query was merged with another table and that seems to be causing the error. Hence, instead of using merge, I did a LOOKUPVALUE function in the table instead to get the columns I needed and that seems to work out just fine. Not sure why the merge would cause the error, but LOOKUPVALUE was the only workaround I could think of.

 

Thanks!

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

I'm facing the same isssue while importing the data into powerBI -  OLE DB or ODBC error: [DataSource.Error] SAP Business Warehouse: No memory of length 336 available for OCCURS area. area. Could anyone help me on this please?

Anonymous
Not applicable

@darentengdrake 

When working with SQL Server it creates a Server Process ID that is used to manage access to temporary tables. For any number of reasons, this connection could be lost and then it assigns you a new SPID. But when you try to submit the query it is acting on the old one. 

 

Contact your SQL Admin and get them to change the Auto Close property on the database to False.

 

---
Please hit the "Accept as Solution" button if my post answered your question! If my post was helpful please consider giving it a "Thumbs Up."

Hi @Anonymous 

 

I will give this a try and I will let you know if it works!

 

Thanks for the suggestion!

 

Daren

Hi @darentengdrake 

 

Any updates on this thread?

 

If the error still exists, you can try playing with various options in the Sql.Database access data function in your power BI query.

Looking at the MSDN for this function https://msdn.microsoft.com/en-us/library/mt260902.aspx there are plenty of options to choose from. My favorite ones are CommandTimeout, MultiSubnetFailover and MaxDegreeOfParallelism.

 

MaxDegreeOfParallelism - degree of parallelism, that is the number of processors employed to run a single statement, for each query that has a parallel execution plan. There should be a server wide setting, however in some scenarios this might work as an overwrite.

 

MultiSubnetFailover- Is useful if you have an SQL Server Always On Availability cluseter. Here is what MSDN has to say about this option in the connection string:

"Always specify MultiSubnetFailover=True when connecting to a SQL Server 2012 availability group listener or SQL Server 2012 Failover Cluster Instance. MultiSubnetFailover enables faster failover for all Availability Groups and or Failover Cluster Instance in SQL Server 2012 and will significantly reduce failover time for single and multi-subnet AlwaysOn topologies. During a multi-subnet failover, the client will attempt connections in parallel. During a subnet failover, will aggressively retry the TCP connection."

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Hi,

 

I tried changing the parameters previously and it didn't work.

 

However, I did a workaround on it. My query was merged with another table and that seems to be causing the error. Hence, instead of using merge, I did a LOOKUPVALUE function in the table instead to get the columns I needed and that seems to work out just fine. Not sure why the merge would cause the error, but LOOKUPVALUE was the only workaround I could think of.

 

Thanks!

Hi @darentengdrake 

 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly.thanks!

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

Hi @darentengdrake 

 

What;s the data source?

Hi @Anonymous 

 

I used Get Data > SQL Server.

 

 

Daren

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.