Friday, November 22, 2013

Linked table is unavailable. Microsoft access cannot contact the server.

Linked table is unavailable. Microsoft access cannot contact the server.
We got this error message when we tried to export our big fat list to “open with access”
We recently found this issue coming up for one of our lists and we are still searching for the solution but when I tried googlng for solution. The only part I found was a hot fix. We have users complaining about this list not working since morning and the hotfix would not make any sense at this moment because it used to work and everyone swears no changes were made.  
What should be your check list for such type of issue? I have tried to list what I did, hope it helps you.
1.       What are the statists of the list.
·         Number of columns
·         Number of items
·         Number of lookup columns
This part in my case was way above the ordinary 163 columns and 12 thousand+ items
2.       Does it work with a different view and less columns
3.       Does it work with Access 2007 and Access 2010
4.       Does it work with a other lists in the same site or sub site (this worked for me)
5.       Does export to excel option works ( this one didn’t work for me, it just helped us to drill the problem down further to the list contents)
6.       I saved the list as a template without content and created list again and tried opening with access (this worked – not sure what that really mean)
7.       Can you apply the hotfix;EN-US;2552989

Close assumptions:
According to the kb 270262
You cannot receive data from SharePoint lists in Microsoft Access if the following conditions are true:
  • When there is a problem with the health of the Microsoft SharePoint Server (for example, it runs slowly).
  • You have a low bandwidth connection.
  • You have a large or complex SharePoint list/document library.
  • There is a large distance between you and the server.
  • There are more than 255 columns in the SharePoint list/document library.

Out of this the large distance to server sounds funny.  The only point I could use to my defense was” You have a large or complex SharePoint list/document library.”
But MS did not specify what that meant so I checked on the threshold section of MSDN  at
Which says :
SharePoint Server 2013 data is stored in SQL Server tables. To allow for the maximum number of possible columns in a SharePoint list, SharePoint Server 2013 will create several rows in the database when data will not fit on a single row. This is called row wrapping.
To prevent too much load, by default a maximum of six SQL Server rows are allowed Each time that a row is wrapped in SQL Server, an additional query load is put on the server when that item is queried because a SQL join must be included in the query. for a SharePoint item. This limit leads to a particular limitation on the number of columns of each type that can be included in a SharePoint list. The following table describes the limits for each column type.
The row wrapping parameter can be increased beyond six, but this may result in too much load on the server. Performance testing is recommended before exceeding this limit.
And you can find from the table how row wrapping is determined.
For Single line of text SQL Server row wrapping occurs after each 64 columns in a SharePoint list.
So on and so forth. Please check the technet link for more information.
This could be a reason of the error message popping up.
As mentioned I was still not able to determine the cause, will update this article when I can.

No comments:

Popular Posts


The opinions expressed on this blog are the personal views of Pratik's SharePoint Blog, and do not represent or reflect the viewpoints or policies of any past, present, or future employer, colleague, or customer, or any other entity. The posts on this blog are provided ‘as is’ with no warranties, express or implied, and confer no rights. Use of information contained within this blog, including specific technical steps mentioned herein, is at your own risk. References to specific software products, processes, resources, or companies do not imply any endorsement.