Wednesday, June 27, 2012

The specified file is not a valid spreadsheet or contains no data to import

While creating a list based on excel sheet we usually opt the option of  "Import Spreadsheet".
We had a requirement where we have to store the information in excel sheet (nearly 65k rows). I could able to import it as a list in sharepoint in both my development and staging environment.

Bur finally while moving to production, while importing the spreadsheet I received an error

"The specified file is not a valid spreadsheet or contains no data to import"

The data was correct even i used the same spread sheet in multiple locations. But its not working with the production site.

The solution for this issue is, the site has to be added as Trusted site in IE as follows

Open the site in IE go to Internet Options, Security tab, select Trusted Sites and add your site here.
Apply and say Ok.

Refresh your page and now I could able to import the spread sheet.
Quite wierd but finally I could be able to sovle that.

Wednesday, June 20, 2012

Shrink SharePoint Content Database Log file

Few times while inspecting the content databases of my sharepoint server, i found certain log files are unusually large compared to the mdf files and its eating up the space in the db server which is causing several other issues such as not able to add or delete content in the site, creating sub sites etc.

After searching many websites, i found the below useful way to reduce the size of log files i.e. by shrinking them.

Following are the commands that should be run in SQL Server Management studio


USE WSS_ContentDBName;
GO

BACKUP LOG WSS_ContentDBName_log TO DISK = 'filepath\*.bak' -- better to back up the file before shrinking it
   -- the recovery mode is to be made simple otherwise the shrinking will not happen
GO

ALTER DATABASE WSS_ContentDBName

SET RECOVERY SIMPLE;

GO

DBCC SHRINKFILE (WSS_ContentDBName_log, size to shrink) WITH NO_INFOMSGS

 -- you can mention to which size (in MB) to shrink your file. This parameter is optional. If nothing is specified, then it will be shrinked to the default size.GO

ALTER DATABASE WSS_ContentDBName

SET RECOVERY FULL;  -- after done with the shrinking, again make the recovery mode to FULL. recovery mode is to be made full because, when database backup is happening, then for full back up both the mdf and ldf will be taken back up. therefore recovery can be made at any point of time.


 

Tuesday, June 19, 2012

Event ID : 3760, Database error, Sharepoint

The following error was repeatedly appearing since 2 days in one of my WFE continuosly every 5 mins.

Event Type: Error
Event Source: Windows SharePoint Services 3
Event Category: Database
Event ID: 3760
Date:
Time: 9:15:27 PM
User: N/A
Computer: XXXXXXX
Description:
SQL Database 'WSS_Content_XXXXX' on SQL Server instance 'DBSERVER' not found. Additional error information from SQL Server is included below.

Cannot open database "WSS_Content_XXXXX" requested by the login. The login failed.
Login failed for user 'Domain\adminuser'.

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.

The reason was, the Database seems to be not accessible.
The solution is to remove the content db to avoid errors.
For that, login to central admin site go to Application Management -> Content Databases. select the webapp having the content db and click on remove content db will solve the problem.

Tuesday, June 12, 2012

Publishing Cache Error, Event Id 5785

In one of my WFE event viewer, I received the following error continously

Event Type: Error
Event Source: Office SharePoint Server
Event Category: Publishing Cache
Event ID: 5785
Date:  
Time:  9:30:46 AM
User:  N/A
Computer: xxxxx
Description:
Unable to connect publishing custom string handler for output caching. IIS Instance Id is '1851498615', Url is 'http://url/_layouts/images/*.GIF'.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.

The reason for this error is that, the images inside the layouts folder was not able to be cached by sharepoint.

Following is the resolution for it.

Open the web.config file, look for <location path="_layouts/images">
just before the </System.Web> tag place the following code

<httpmodules>
<remove name="PublishingHttpModule">
</httpmodules>

 From now on, the error will not appear in the event viewer.
Thanks for the blog post which helped me in solving this issue.

Wednesday, May 16, 2012

Event ID : 6641, SSP Timer Job User Profile Change Job was not run

The following error appears in the event viewer of my share point server every minute 

/******************Error Description *****************/

Event Type: Error
Event Source: Office SharePoint Server
Event Category: Office Server Shared Services
Event ID: 6641
Date: 5/15/2012
Time: 2:00:
00 PM
User: N/A
Computer: <server name>

Description:
The SSP Timer Job User Profile Change Job was not run.

Reason: Logon failure: the user has not been granted the requested logon type at this computer

Technical Support Details:
System.ComponentModel.Win32Exception: Logon failure: the user has not been granted the requested logon type at this computer
at Microsoft.Office.Server.Utilities.WindowsSecurity.GetUserTokenFromCredentials(String userDomainName, String password, LogonType logonType)
at Microsoft.Office.Server.Utilities.WindowsSecurity.GetUserTokenFromCredentials(String userDomainName, String password)
at Microsoft.Office.Server.Administration.JobHandler.Execute(Object state, Boolean timedOut)

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.

/******************Error Description *****************/

 I googled it for the resolution and in many places I found that it has to do with the service account password change and not updated in the farm credentials. But the service account password was not locked nor it was changed. There fore the solutions I found didn't solve my issue.

After thorough search, I again found in some places where it was mentioned that, the account on which the windows share point timer services will run, has to be provided with log on as batch privileges.
Following is the process to modify the setting

Type gpedit.msc from run and under
Local Computer Policy -> Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment.


check for the policy "Logon as a batch Job"

check whether the service account on which the windows share point Timer services are running is present in the policy as a user. If not, add the account there.

Also make sure that, the account should also be a part of "Logon as service" policy.

Once this is added, the error stopped appearing in my event viewer.

Thursday, May 3, 2012

Exception from HRESULT: 0x80040E14

You may come across this error Exception from HRESULT: 0x80040E14 when ever you try to add content to the sharepoint site or execute any stsadm commands.

The sqldb server must have run out of space. clear space from the dbserver and you see the error is no longer displayed.

Tuesday, May 1, 2012

Event ID: 5553 and 7888


In Application logs in the SharePoint server, we may continuously find 2 different events occurring frequently.

//-----------------------------------------------------------------
Event Type: Error
Event Source: Office SharePoint Server
Event Category: User Profiles
Event ID: 5553
Date: 4/26/2012
Time: 7:09:02 PM
User: N/A
Computer: SERVER NAME
Description:
failure trying to synch site a7b12d90-7d1d-4341-8df3-a1d92eba7a47 for ContentDB db7fb0d6-7d0d-4230-8de5-e7cc00ca0db7 WebApp 55de867d-5a9d-4ac4-83d6-f3ee9ee98df2. Exception message was Cannot insert duplicate key row in object 'dbo.UserMemberships' with unique index 'CX_UserMemberships_RecordId_MemberGroupId_SID'.
The statement has been terminated..
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

//----------------------------------------------------------------
Event Type: Error
Event Source: Office SharePoint Server
Event Category: Office Server General
Event ID: 7888
Date: 4/26/2012
Time: 7:09:02 PM
User: N/A
Computer: SERVER NAME
Description:
A runtime exception was detected. Details follow.
Message: Cannot insert duplicate key row in object 'dbo.UserMemberships' with unique index 'CX_UserMemberships_RecordId_MemberGroupId_SID'.
The statement has been terminated.
Techinal Details:
System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'dbo.UserMemberships' with unique index 'CX_UserMemberships_RecordId_MemberGroupId_SID'.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError (SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError (SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning (TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader (SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds (CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader (CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery (DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Office.Server.Data.SqlSession.ExecuteNonQuery (SqlCommand command)
at Microsoft.Office.Server.UserProfiles.WSSSynchSqlSession.SynchExecuteNonQuery (SqlCommand cmd, Boolean throwOnFail)
at Microsoft.Office.Server.UserProfiles.WSSSynchSqlSession.SynchExecuteNonQuery (SqlCommand cmd)
at Microsoft.Office.Server.UserProfiles.SiteSynchronizer.WriteChangeLogConsumed()
at Microsoft.Office.Server.UserProfiles.SiteSynchronizer.Synch()
at Microsoft.Office.Server.Diagnostics.FirstChanceHandler.ExceptionFilter (Boolean fRethrowException, TryBlock tryBlock, FilterBlock filter, CatchBlock catchBlock, FinallyBlock finallyBlock)
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
//-----------------------------------------


Cause:
The likely reason for this issue is the content database which can be movied or copied or used without detaching properly.
As the synchronization runs every hour, this issue will appear frequently in the event viewer.

Resolution:
We have to track down which content db is giving the issue.
For that you can run the following queries in the config db to find out the contentdb 


SELECT *FROM [dbo].[Objects]

WHERE ID='db7fb0d6-7d0d-4230-8de5-e7cc00ca0db7'

The GUID of the content db can be obtained from the error message itself, (5553 error)

SELECT [FullUrl]
,[Id]
,[RootWebId]
FROM [WSS_Content_sitecontentdb].[dbo].[Sites]
WHERE ID='a7b12d90-7d1d-4341-8df3-a1d92eba7a47'


The site GUId also can be obtained from the error message. Which is used to execute the below command
The following command is to be executed to clear the errors.
stsadm -o preparetomove -Site "http://<sitename>/" -OldContentDB "<ContentDBGUID>"
stsadm -o sync -DeleteOldDatabases 0
Also remember that, before moving content database execute the preparetomove command.

Friday, February 17, 2012

Unable to modify table, time out expired error

When I tried to modify a table structure in  with more than 20 million records. It gave me the time out expired error. This occurred in MS SQL Server 2005
The modifications were not saved.


When I initially tried to save the modifications for the table, a warning message appeared for me. 




Later I clicked yes and again waited for the result. It finally gave me an error saying that, 'time out expired' after 30 seconds.






The error was "Errors were encountered during the save process. Some database objects were not saved" and "Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." The table structure was same as previous.


Reason for this is the time out setting in the designer option. It would be set to 30 seconds by default. If you changes take longer than that time then they would not be saved. We need to change the value of the setting.


For this, go to Tools -> Options -> Designers


There will be check box option of "Override connection time-out value for table designers updates:" and "Transaction time-out after:" change the default value of 30 secs to the a higher value. In my case I made it 1000 seconds. And then click on OK. Do remember that transaction time out setting value should be greater than 0 and it should be in the range between 1- 65535. 


Now after doing this change, I tried to modify the table and the changes got saved now. 
Note that while the table modifications are saved, the SQL Management studio would remain inaccessible till the changes to complete. 



Wednesday, January 25, 2012

Hide View All site content


to hide view all site content,

edit the page, add a content editor Webpart 

in source Editor, copy the below code, and save it. 


<style>
.ms-quicklaunchheader {
display:none;
}
</style>

Thursday, January 12, 2012

How to move/migrate SharePoint list items/documents and preserve metadata properties

Usually, when you want to move documents or list items from a source to destination list, we follow the following methods
  • Opening in windows explorer and copy pasting the documents
  • Saving the source list as template including content and then recreating the list in destination using template
But both above process when we move we have the below drawbacks
  • The metadata properties more importantly the Modified date stamp and created date stamp are not preserved
  • For issue tracking list, where we usually dont have the option to save as template including content cannot move items.

There is an alternate method to achieve moving the items from source and destination list, same time preserving the meta data properties.

1. Create a blank list of document library/custom list etc in the destination site. No need to create the custom columns etc, the move process will take care.

2. Access the Site and Content structure  from Site Actions button or from site settings ->Site administration ->content and strcuture


3. Select the list or library in the list and go to all items view and select all the items. In toolbar, actions , click on "Move".




4. You wil receive a pop up asking to select the destination list. Just select the list you have created and ok.

Done. Now you can see all the items are moved along preserving the time stamp. 

Note: If you get any errors while moving the items, the alternate way is save the source list as template excluding content and then recreate it in destination. This will preserve the structure in source.

Also do note that, if source has versioning enabled, then destination should also have versioning.
If it has look up columns do manage to make lookup work by created the dependency lists in destination also.

You can move the dependency list using the same process first and then the actual list.

Hope it helps you!!


Thursday, January 5, 2012

Avoiding Recursion in Event Receivers

While writing the Event Handlers, generally we will have the requirement as checking few of the field values and updating other fields accordingly.
But when you try updating the item again the event handler will fire again and if you observe the version history of the item, the change made by user which triggered the event receiver will be registered as one version and the one triggered by the code will be as another version and also if you see the modified person will be admin.

By adding few lines of code we can avoid the recursion of event firing and the redundant versions.

item[fieldname] = fieldvalue;
this.DisableEventFiring();
item.SystemUpdate(false);
this.EnableEventFiring();

By disabling event firing before update, the recursion will be avoided. And by SystemUpdate(false), ensures that no version is created by this update and also user can see the update as his update rather than Admin's.