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

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

1	<webServices>

2	<soapExtensionTypes>

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

4	</soapExtensionTypes>

5	</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. 




Thanks to Jim Adcock for this post.

Missing server side dependencies Error in SharePoint 2010

Solution I:
I enabled the service and also noticed that in central admin configuration there's an error listed:

Missing server side dependencies. 

[MissingWebPart][WebPart class [8d6034c4-a416-e535-281a-6b714894e1aa] is referenced [12] times in the database, but is not installed on the current farm. Please install any feature/solution which contains this web part. One or more web parts are referenced in the database, but are not installed on the current farm. Please install any feature or solution which contains these web parts.] 

[MissingWebPart][WebPart class [8d6034c4-a416-e535-281a-6b714894e1aa] is referenced [6] times in the database, but is not installed on the current farm. Please install any feature/solution which contains this web part. One or more web parts are referenced in the database, but are not installed on the current farm. Please install any feature or solution which contains these web parts.] 

For more information about this rule, see "http://go.microsoft.com/fwlink/?LinkID=142689".

Failing Services - SPTimerService (SPTimerV4)

This error will appear on a cleanly installed system in the Health Analyzer Reports until you visit the following pages:


SearchAdministration.aspx
SearchFarmDashboard.aspx

These are: Central Administration, General Application Settings, Farm Search Administration and then Search Service Application.


Browse to them and the error will no longer appear in the Health Analyzer Report.


Solution II:

You just have to setup the "SharePoint Foundation Help Search", who will crawl all the Sharepoint Help files (http://{centraladmin}/sites/Help).
After one or two minutes, the error will disapear....

Restoring SQL Server 2008 DB onto SQL Server 2005

The following steps give you complete details of Restoring a Northwind database on SQL Server 2008 to SQL Server 2005:

1. Select Northwind Database go to "Tasks" and select "Generate Scripts..". Generate Scripts wizard will open.

2. Click "Script all objects in the selected database" (see Figure 2), and then click "Next."

3. Amend the script options: Specifically, set "Script for Server Version" to "SQL Server 2005" and set "Script Data" to "True" (see Figure 3). (SQL Server 2000 is also supported.) If you are putting the database on a new instance for the first time, make sure the "Type of data to Script"      option is set to "Schema and data." Click "Next" when you are happy with the options.

4.Select "Script to file," select the file name, and choose "Single file" (see Figure 4). Click "Next" for a summary (see Figure 5).

5. Now click on "Finish" to get progress messages while the script runs and completes (see Figure 6).

If the generation process fails, then you can use the "Report" option to see why.


6. When the scripting is completed, look for the following lines:

 CREATE DATABASE [Northwind] ON  PRIMARY

(NAME = N'Northwind', FILENAME =

N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\northwnd.mdf' ,

SIZE = 3328KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

(NAME = N'Northwind_log', FILENAME =

N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\northwnd.ldf' , 

SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

You will need to amend the paths to a valid path for the computer on which you are running. You also will need to comment out the following lines like this:

--EXEC sys.sp_db_vardecimal_storage_format N'Northwind', N'ON'

--GO

Once you have made these changes, you can run the script in SQL Server 2005 Management Studio to recreate the database in your development environment. You can now test data against SQL Server 2008 and SQL Server 2005.

Uploading a large file: security validation has timed out MOSS2007 with Windows 2008

I am trying to allow the upload of large files (e.g. 150 MB) into a SharePoint 2007 document library. I thought I had switched on all the correct settings, but I still receive an error message: "The security validation for this page has timed out.". This generally happens after about 8 minutes.

Here's what I have done so far:

1. Within SharePoint Central Admin > Application Management > Web Application General Settings I have set the Maximum Upload Size to 300MB and the Web Page Security Validation to 120 minutes.
   
2. The web.config file in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\LAYOUTS is set to:
   <location path="upload.aspx"><system.web><httpRuntime executionTimeout="999999" maxRequestLength="2097151" /></system.web></location>
   
3. The web.config file in C:\inetpub\wwwroot\wss\VirtualDirectories\80 is set to:
   <httpRuntime executionTimeout="999999" maxRequestLength="512000" />
   
4. Also, I have added the following to the web.config file in C:\inetpub\wwwroot\wss\VirtualDirectories\80:
   <system.webServer><security><requestFiltering><requestLimits maxAllowedContentLength="524288000" /></requestFiltering></security></system.webServer>

This will work for Document Libraries.

If you want to attach large files to List then you need to change the following things

when ur uploading file into library it will redirect to upload.aspx page; coming to list its not redirecting to upload.aspx page. It will be in Editform.aspx page

 so try to add the code in 12 hive web.config file as

<location path="editform.aspx"> 

<system.web> 

<httpRuntime executionTimeout="999999" maxRequestLength="2097151" /> 

</system.web> 

</location>

Installation of SharePoint 2010 in a Small Farm Topology

http://www.sharepointfabian.com/blog/Lists/Posts/Post.aspx?ID=91

Using jQuery to Make a Field Read Only in SharePoint

So,let’s say you have a field on a SharePoint Form and you want to make it read only. You COULD just open it up in SPD and easily make it read only, but some people are purists and don’t like use SPD or modify the default new/edit/disp forms. I try to avoid modifying these forms and it seemed like such a simple task that I didn’t want to create a new un-ghosted form.  So how do you do it?

Anyway, here’s the entire script:

<script src="jquery.min.js" type="text/javascript"></script>

<script type="text/javascript">

jQuery(document).ready(function($){

     //capture keypress on our read only field and return false
     $('#idOfInputField').keypress(function() {
            return false;
     });

})

</script> 

You can find the ID of your input field by viewing the source, this ID stays consistent as long as you don’t muck with the list or form in the wrong way.  Please note, you CANNOT disable the input field as an alternative to capturing the keypress. If you do this and save the form, any data in the disabled fields will be wiped out.

SharePoint InstantListFilter Demo

This is a demo of SharePoint InstantListFilter, a Javascript based solution that adds filter textboxes to every column of a SharePoint list view (SPGridView) with filter-as-you-type functionality. It uses jQuery to add the textboxes and perform the filtering.
You can download it for free from http://www.codeplex.com/instantlistfilter
You can play around by entering values in the textboxes and see how the result sets get filtered immediately. Be aware of the fact that the amount of rows and columns do affect the performance. As this is a 100% client side solution, the spec of your machine will also affect the performance.
This demo was implemented by only inserting some javascript into a Content Editor Web Part on this page. It does not require anything to be installed on the server, which is pretty handy for sitations where you have limited access to your SharePoint environment (which is the case for this demo site!).

Open PDF Files in a New Window in SharePoint 2010

A request that I get occasionally is how to force SharePoint to open up PDF files up in a new window.  Assuming you have Adobe Reader installed, the default behavior is to prompt the user to open the PDF file.  For some customers, they want it to automatically open up in the same browser window.  This is easy to do by following the answer shown here. (Note: this does assume that the browser plug-in is installed and enabled).

For me, I like this solution and simply use the back button when I’m done and want to return to SharePoint.  If I want it in a new window, I either hold down Ctrl and click the link or just right-click the link and select Open in New Window.  Basic browser stuff, right?  In most cases, I convince the customer that this is a training issue on how to use the browser. However, that doesn’t always fly with every customer, does it?

If you to force the PDF file to load into a new browser window, keep reading...

There are a number of posts on workarounds to the problem.  One says that you should write a httphandler.  Not a bad solution, very thorough, but a complex one unless your a seasoned SharePoint and ASP.NET developer.  Another talks about editing the global onet.xml file (gasp!) which is not supported by Microsoft and this targets much more than just PDF files.  Others suggest a somewhat elegant JavaScript solution.  This searches through the <A> tags on the page and inserts a target attribute and sets it to _blank which opens it in a new window.  The problem with this is that the target attribute is deprecated when using the strict XHTML doctype which will present problem with SharePoint 2010 sites.

I think I have a pretty-good workaround that addresses the problem.  This is a page-specific fix, so it is not global to all libraries or links in your farm. I like it this way as you have granular control where you want to use it, meaning you may only want to use it on certain pages within team sites.

The solution is to add this code to a Content Editor Web Part (CEWP) on the page:

<script language="javascript" type="text/javascript">
    _spBodyOnLoadFunctionNames.push("OpenPDFInNewWindow()"); 

    function OpenPDFInNewWindow()
    {
      // Get the collection of <a> tags
      var aAllLinks = document.getElementsByTagName('a'); 

      // For each <a> tag
      for(var i=0;i<aAllLinks.length;i++)
      {
         var oA = aAllLinks[i];
         var sHREF = oA.attributes["href"].value.toLowerCase();
         // Is this a pdf link?
         if(sHREF.indexOf(".pdf") > 0)
            oA.href="javascript:window.open('" + sHREF + "').focus();";
      }
    }
</script>

Credit goes to Daniel whose code I modified to have this functionality.  To simplify this for all of you, I am also making this available using a .DWP web part.  You can simply upload this into the web part gallery of your site collection which allows you to more easily add it to any page. To download, click here.