My career in technology

Several months ago users started reporting an inability to edit lists in a datasheet, and to import from or export to Excel. It was first reported in our production environment, and when we investigated, we found the problem in the dev environment as well.

Google searches were unsuccessful at finding any relevant reports of causes for the symptoms. Mostly the references were for single users experiencing the problem, but for us no one was able to perform these tasks. One page referred to Web Service Enhancements (WSE) 2, but this was not something that was relevant to our environment.

But recently we discovered that a lesser-used “staging” version of our site was not experiencing the issue! This gave us a baseline to start making some comparisons against.

Symptoms

Here is a full description of the symptoms:

When you select Actions >> Edit in Datasheet, and make changes:

(message at bottom of screen) You have errors. Resolve.
(message at bottom of screen) Read Only.
(Dialog) For this error, you can retry or discard your changes.
(buttons) Discard my changes. Retry my changes.

When you try to exit the page:

(Dialog) You have pending changes, or unresolved conflicts and errors. Do you want to wait for this operation to complete?

When trying to edit in datasheet, the user is able to change the cells, but the changes are not completed on the server.

Notice the Access icon in the upper left corner of the datasheet. The datasheet is an “Access Web Datasheet”. I spent a while chasing down what would cause that to fail to work properly.

Another symptom was in Actions >> Export to Spreadsheet:

(Dialog) Do You want to open or save this file? filename.iqy file
(Dialog) Microsoft Office has identified a potential security concern.
(Dialog) Excel cannot connect to the SharePoint list.

First message when trying to Export to Spreadsheet

Second message when trying to Export to Spreadsheet. This message is standard and not an indication of error.

Third message when trying to Export to Spreadsheet.

Another was in Actions >> Open with Access:

When attempting to Open with Access, Access would open but without any opening any databases. The accdb file for the SharePoint list would appear in the Open Recent Database pane, but clicking on it got a dialog box -
(Dialog) Could not find the file filepath\filename.accdb

When trying to Open in Access, the access file is not able to be found.

And in Create >> Import Spreadsheet:

Error message when trying to create a list from a spreadsheet import

Error message when trying to create a list from a spreadsheet import

Troubleshooting

Once we had identified an environment where these features were working, we were able to start closing in on the problem. Locating the web.config file for the working web application, I substituted the working web.config file for the one in the dev system (after backing up the current file!), and the problem no longer occurred.

To get the locations of the web.config files, open IIS, right-click on the web site and choose properties. Click on the “Home Directory” tab, and find the Local Path, which should be something like C:\Inetpub\wwwroot\wss\VirtualDirectories\######. Your web.config file is in there.

Use a file comparison tool. I started out with Windiff, but was advised by a co-worker to use Beyond Compare, and boy was that ever a good choice! With Beyond Compare, I was able to compare the files side-by-side, with the differences highlighted, and to move blocks of code from one version to the other with a single click, and undo with CTRL-Z. It could not have been easier.

After substituting the working web.config file for the one causing the errors, I then added sections of code back into the now working web.config file from the backed-up file, and tested after restoring each section.

Cause

   <webServices>
      <soapExtensionTypes>
        <add type="Cognos.Portal.Services.SoapPatchExtension, Cognos.BI.WebPart, Version=1.0.0.0, Culture=neutral, PublicKeyToken=################" priority="1" group="0" />
      </soapExtensionTypes>
    </webServices>

It was that “simple”.

I inherited the site from a previous administrator, so I wasn’t in on all of the decisions prior to my apotheosis. And there is definitely no knock on the previous guy, who is great. But there is a story to this…

Prior to his departure, he was trying to add some third party web parts (Cognos, as you can tell from the code) that had been approved for use in our environment to fill a particular business need. The implementation didn’t work, and from what I understand, he spent quite a bit of time working with the vendor to get the web parts configured correctly, apparently to no avail. The project got shelved.

It was after his departure that the problem was first reported, and, having no idea what changes had been made when (yes, I am implementing a change log), or when exactly the problem had begun…

Solutions

So for starters, since the web parts were never configured properly, the easy way out is to comment out the “add type” line, which does resolve the issue.

In the longer term, the issue will be to get the web part to play well with the other features in SharePoint, like editing in datasheets, using excel to import and export, and so on. That the web service is not Microsoft Web Services Extensions 2 per se threw me off originally, but now that I know the problem is in the webservices section of the web.config file, I will be taking a closer look at the Web Service Enhancements (WSE) 2 article, which points to using “remove type” in a “location path” in the web.config file. I’ll be experimenting with how to implement that in a way that works for our issue.

I still don’t have a full understanding of why this causes a problem, but if you are experiencing these symptoms, I hope this post helps you to find your solution.

More posts about SharePoint.

About these ads

Comments on: "Error: SharePoint List – Cannot Edit in Datasheet, Export to Excel, or Import Spreadsheet" (15)

  1. Were you ever able to find a solution to this? we use cognos at our company and I just “installed” the web parts to one of our sharepoint sites and immediately started seeing this issue the next morning. For now we are going to do like you did and just revert the web.config.

    • I would love to say yes, but…

      We have decided that the move to SP2010 takes precedence. We’re going to complete that first and then evaluate the need for the webparts in question, and, if we do need them, determine if we see the same problem in the 2010 environment.

      If we do decide to go ahead with them in the SP2010 environment, I will be sure to report if we experience the same issue or not, and what resolution we come to if the problem still exists in 2010.

      • Jim, Thanks for responding. Our cognos application owner will be getting in touch with cognos on this issue. I hope they have better luck than you did in that regard. We currently have the web parts deployed to one of our web apps, however people don’t seem to use the features that the required web.config changes break in that web app. Honestly I’m appalled that cognos doesn’t have a deployable sharepoint solution for this addon but thats beside the point.

        In the mean time I have rolled back the web.configs to our main SP web application where I did the install. We’ll see what cognos has to say. If we find a solution I’ll post it here. :)

  2. Couple of other things that might help if you are planning to add Cognos webparts into your sharepoint 2007 environment.

    1. Definitely use beyond compare when making changes to your web.config file (after you save a backup)
    2. If you have other custom webparts (like bamboo) you will not be able to use the wss_cognostrust.config. You will need to pick out what you need and add that to your trust file so you don’t break your other webparts.
    3. Make sure that when you are doing this in your DEV environment that you are dealing with the same config as you will in production, amazing the amount of the things break.

    I still haven’t got the soap piece fixed. I actually just removed it and called it day because the functionality I was looking for worked without.

  3. [...] Error: SharePoint List – Cannot Edit in Datasheet, Export to Excel, or Import Spreadsheet [...]

  4. [...] Error: SharePoint List – Cannot Edit in Datasheet, Export to Excel, or Import Spreadsheet [...]

  5. How can you fix this particular problem without the web.config file? I am having the “Excel cannot connect to Sharepoint file”. But I cannot locate the web.config file on any of the local computers that are having the problem. One of the computers that can export the file I am not sure if he has that file on his computer.

    • The web.config file is on the SharePoint server. If some but not all of your users are experiencing the problem, then the web.config isn’t your issue. The web.config problem will affect all of the users.

  6. [...] Error: SharePoint List – Cannot Edit in Datasheet, Export to Excel, or Import Spreadsheet [...]

  7. [...] Error: SharePoint List – Cannot Edit in Datasheet, Export to Excel, or Import Spreadsheet [...]

  8. Hi Jim,

    At our end the issue is that, when we edit the records in Datasheet view then then update for the some records works fine but for the few records it gives the Unexpected error. And display the same Resolve Error Box. But as we said that the Error is Unexpected.

    Any help will be appreciated, if you can give us some clue.

    Thanks!

    Sam

    • I don’t have an answer for intermittent errors when trying to edit in datasheet, but I do have some advice:

      1. If you haven’t already, check the SharePoint logs, see if there is anything there more helpful than “Unexpected Error”
      2. Try with users who have more rights (does the site collection administrator have the same error?)
      3. Is it always the same list items that generate errors? If so, what is different about them? Can they be edited normally?
      4. Is anyone else trying to edit the items at the same time?

      Hopefully these ideas may help you get to the answers you need. I’d love to hear back when you find a solution!

  9. Raphael Almeida said:

    I suggest remove tag in the in the webconfig file.

    • That was what we ended up doing, then our migration into 2010 took precedence over continuing to work a solution in order to add the Cognos webparts. One reader said they were able to remove the line in the config file and still get the functionality they needed. but we had never gotten the Cognos web parts successfully configured.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 974 other followers

%d bloggers like this: