The following is a help guide I’ve personally compiled from much trial and error and dealing with the ever evolving OneDrive for Business sync process. For some, the sync process can work flawlessly, but for me I’ve experienced many issues over time, and found several resources and steps that I have compiled … Continue reading →

Share this:

Headed to SharePoint Conference 2014!

As it has been a little while since I have posted, it’s also been a while since I’ve made it to a SharePoint Conference, but happy to report I will be attending this next week, arriving in sunny Las Vegas, NV on Sunday, March 2nd, and returning on March 6th. … Continue reading →

Share this:

How To Change The ‘Created By’ Column in a SharePoint List Using SQL

Recently I ran into a list that contained many items that were created by various logon accounts. For sake of simple example, let’s say these were various regional office locations, all with their own regional Active Directory logon account. The company decided to rebrand their offices, and change the prefix of the logon account … Continue reading →

Share this:

I recently ran into an issue where the Active Directory Enterprise Resource Pool Synchronization was failing on my Project Server 2010 farm. On the Active Directory Enterprise Resource Pool Synchronization page it was showing the Synchronization Status as: Some user data was not updated because synchronization of the Enterprise Resource … Continue reading →

Share this:

Failed to read the acls for an item. Access is denied. (Exception from HRESULT: 0×80070005 (E_ACCESSDENIED))

Recently in one of my SharePoint 2010 PerformancePoint environments, I came across an event log with the following error: Failed to read the acls for an item. Access is denied. (Exception from HRESULT: 0×80070005 (E_ACCESSDENIED)) Although I didn’t notice any issues in the environment, I suspected that this was not normal … Continue reading →

The following is a help guide I’ve personally compiled from much trial and error and dealing with the ever evolving OneDrive for Business sync process. For some, the sync process can work flawlessly, but for me I’ve experienced many issues over time, and found several resources and steps that I have compiled to help others.

Please review the below steps to help determine causing issues or for help in using common guides on resolving typical OneDrive for Business sync issues. You can also shortcut to the Microsoft online online support guide that Microsoft published to identify issues. The initial steps below may identify or resolve common issues much quicker, and their guide will go through some in depth steps and require much more time start to finish.

Start by checking the OneDrive for Business sync icon in the system tray for possible issues

Issues may be evident if “OneDrive for Business – Syncing files, XXXXX remaining” appears for more than a business day.

If you right-click on the sync icon, and see a ‘View sync problems…’ click that and open.

The following should appear and may show many numerous issues, or at very least
(and not very helpful) the top one as showed in the screenshot below:

Sometimes you can click on the items and they will lead you to file location with issue, or even open the file and resolve a local/server save conflict issues…or, open a not very useful HELP dialogue box.

If all else fails, and alternate that I personally found to work is to completely uninstall Microsoft Office 365 ProPlus (or the if using the full Microsoft client – Microsoft Office Professional Plus 2013), and then reinstall Office 365 ProPlus and then setup the OneDrive for Business sync again.

A few caveats to be aware of and some preliminary steps to that should be taken before doing this.

Make sure you have attempted a Repair first. You should have gone through the guided steps by Microsoft and performed a repair already, before getting to this last ditch effort to fix. If not, please go back and do so now.

The repair process creates a copy of your local OneDrive for Business folder into a folder in the OneDrive for Business Archives folder under your local Profile. This is important, because if your sync has not been working, any local documents you saved to your OneDrive for Business folder may not be synced to the cloud.

After the repair, right-click on the OneDrive for Business system tray icon, and select the ‘Stop syncing a folder…’ option, and then select your ‘OneDrive – Manhattan Associates’ folder, and then click on ‘Stop syncing’. This will completely remove your ‘OneDrive @ Manhattan Associates’ folder. This is needed to clean up and remove the folder so that when you reinstall ProPlus and start a new sync, it doesn’t have any issues with it already existing.

After you have completed the above, go into your PC’s Control Panel > Programs and Features and locate your Microsoft Office 365 ProPlus installation, select it, and then select ‘Uninstall’ and follow the prompts. When completed, a reboot isn’t required, but is a recommended option to make sure files are cleaned up.

After completed, perform an new install of Office 2013 (this will either be from your Office 365 location for the Office 365 ProPlus or the client install of Office Professional Plus 2013).

Once your install is completed, go out to your Office 365 portal, and into your OneDrive for Business site. Perform a sync, and go through the prompts to set the local sync back up. Depending on the size of your online OneDrive this could take some considerable time to sync all your documents local.

After performing a Sync and once all your cloud files have synced back down locally, you will now need to do a compare with your OneDrive for Business Archives folder where the files were placed from the repair process in Step #1.There is no native way inside of windows to a compare between files, so the best alternate is to just copy and paste all your archive files into the new directory.To best accomplish this, open up a separate Windows Explorer window for your new OneDrive location and then open another window to the Archive location. Then, from the Archive location, highlight all the folders/files, and then do a copy and then paste them into your new OneDrive location. When prompted for copying over existing files/folders, makes sure to agree to replace ALL files and folders, as you want to be sure any local updates that occurred to files, replace those that may have already existed in the cloud but never got changes synced up to the cloud. If you only agree to pasting in files that didn’t exist, you risk losing file updates that never synced to the cloud.

NOTE: You can be assured that the reinstall will not have any major effect on how you had Outlook setup. One downside is that any ‘recent history’ of documents you used/edited, will not appear in Windows. Any shortcuts you had setup to the desktop or quick launch should appear, but you may likely will have to re-pin any short cuts to your start menu or task bar after reinstall.

Please be aware this is a compilation of resources and steps that have worked for my personal experience, and may not be inclusive of all options. Our hope, as a community, is that Microsoft will continue to work on OneDrive sync client and eliminate many of the issues that seem to pop up for many users in future releases.

As it has been a little while since I have posted, it’s also been a while since I’ve made it to a SharePoint Conference, but happy to report I will be attending this next week, arriving in sunny Las Vegas, NV on Sunday, March 2nd, and returning on March 6th. I missed SPC12, so SPC11 was the last I attended, and this will be my 3rd SharePoint Conference in my travels. It’s shaping up to be a tremendous event with SharePoint folks coming from all parts of the world. All the information on what this conference is about can be located at the SharePoint Conference 2014 site.

For those not able to attend, you can at least watch the Keynote address on Monday, March 3rd, at 8:30 A.M. PST (11:30 A.M. EST). This year, President Bill Clinton will be the main Keynote Speaker. Jeff Teper, Corporate Vice President of Microsoft and Jared Spataro, General Manager of Microsoft will also be featured presenters during the keynote.

A lot of the social collaboration for the conference is happening on the private SPC 2014 Yammer Network (only accessible to conference attendees), but there is an SPC14 public group via the SPYAM Yammer SharePoint Community (https://www.yammer.com/spyam).

I’ve been far behind on posting all the technical trials and ventures I’ve ran into in the past several months, and hope to catch back up again soon. I look to also do some more reflecting on the conference (hopefully during the next week) and then something post conference.

Looking forward to meeting as many of the 10,000+ attendees this year again!

Share this:

Recently I ran into a list that contained many items that were created by various logon accounts. For sake of simple example, let’s say these were various regional office locations, all with their own regional Active Directory logon account. The company decided to rebrand their offices, and change the prefix of the logon account to reflect this new branding. Again, for sake of example, let’s say one particular office account was DOMAIN\RED-REGION-1 and now they were being branded to DOMAIN\BLUE-REGION-1.

Without careful consideration of what this might mean for SharePoint, when these offices started logging in to SharePoint with their new ‘BLUE’ accounts to update/edit list items, they no longer could see any previous items, because they were not previously created by the ‘BLUE’ account.

This was a result of the Item-level permissions setup in the Advanced Settings of the SharePoint list that essentially only let users see and edit items that they originally created with the logon account that was used. Item-level Permissions set in the following way:

Read Access – Read items that were created by the user

Create and Edit Access – Create items and edit items that were created by the user

So, how to resolve, without having users recreate all their list items manually? Well, there is always more than one way to ‘skin a cat’ it seems with SharePoint. My first thought was to just export the list to Excel, then log in as the new BLUE account, and then paste the values back into the list using the Datasheet View. However, there was a calculated column that formulated the Month/Yr that the list item was created, and that essentially is a ‘Read-only’ field and won’t allow pasting a value into it when using Datasheet View. This original needed to be retained, so this was not a viable option.

Doing some research, I found that there were some PowerShell scripts that appeared to exist to resolve this problem, but I only could find ones that would work best if all the items in the list were created by the same user, and then could replace using ‘Foreach’. Unfortunately, I had a list with many items, and created by more than 30 different user accounts. Being under a time crunch, and not feeling my PowerShell mojo at the time, I felt that trying to figure out a way to manipulate the PowerShell scripts to look for only certain items that were created by specific users, and change the ‘Created by’ and ‘Modified by’ columns, was more involved than I wanted to get at the moment.

So, relying on my SQL Database knowledge and skills, I new that there must be a way to locate these fields in the database tables, and then update them via query commands in SQL Server Studio Manager. So after doing some research, and doing some testing in a development environment, I was able to come up with a solution. Again, this was easier for me, based on the scenario and the time crunch I was under.

WARNING: I do not recommend doing this in your production environment. Microsoft does not support directly updating SharePoint databases. Also, as a safety net, I recommend that you make database backups of the content databases that you will be making modifications to.

Steps to Complete:

First, make sure that the new DOMAIN\BLUE-REGION-1 account has logged into SharePoint. This insures that the new account registered in the UserInfo table of the content database

Open up SQL Studio Manager (either on the SQL server or remotely) and connect to the SharePoint SQL Server. The user account you connect with needs to have enough rights to write to databases, but I just use an account that is Sysadmin.

First, we have to find the 2 tp_ID‘s that have been assigned to the RED and the BLUE accounts in the UserInfo table. To do so, use the following query (the ‘CONTENTDB’ should be replaced with your content database name):use CONTENTDBNAMEselect * from UserInfowhere tp_login like ‘%REGION-1%’(using the % wildcard without RED or BLUE will locate both tp_ID‘s for the tp_Login accounts that contain ‘REGION-1′ with one query rather than finding each separately)
Then click on ‘Execute‘

In the results pane, look for the tp_ID number assigned for both, and then reference the tp_Login column to make sure which tp_ID belongs to each login. In this example, let’s say that RED is 1000, and Blue is 1001.

Now, list and library data is stored in the AllUserData table, each list or library item that is created, also has its own tp_ID, but it is not the same as the user account tp_ID in the UserInfo table. So, we now need to determine what the tp_Author (Created by) value is for the RED account that does match up with the tp_ID UserInfo value. From a new query window again, execute the following:use CONTENTDB
select * from AllUserDatawhere tp_author = 1000

The results of the query will be displayed, we now want to find the tp_GUID value which will be unique for each list item that needs to be modified. CAUTION, all user data, for lists and libraries in the content DB, will be displayed where this user was an author. Make sure to look at the tp_ListId in the results, and look only for the ones that apply to the list you are modifying. I had to scroll to the right in the results and identify data that existed in the list. Mine had a common nvarchar1 column that had an identifiable value unique to this list. Left-mouse-click on the value in the tp_GUID column to highlight, and then right-mouse-click on the value and select ‘copy’. We will paste this value in the next query.

The final SQL piece will be to ‘update’ and ‘set’ the tp_Author (Created by) to the new BLUE tp_Author value (1001 for this example) for the tp_GUID list item. To do so, execute the following:use CONTENTDBgoupdate dbo.AlluserDataset tp_Author = 1001where tp_GUID = ‘DDCCC764-BBF9-41D2-B294-95ED828ED63B’NOTE: Your tp_GUID will be unique – above is just for example

In the results pane, you should see a response of ‘(1 row(s) affected)’, which confirms the update has been made.

Test the modification, by now having the user login with the new BLUE account and they now should see the item originally created by the RED account. If for some reason you also want to replace the ‘Modified by’ column, you can use the same query in step 7, but replace the ‘tp_Author’ with ‘tp_Editor’ and leave the rest the same, and execute again.

Now, for a very large list with many user accounts, this could be a daunting task, but for me it fit the bill, and got the outcome I was in need of in a very short time. Again, use at your own risk!

I recently ran into an issue where the Active Directory Enterprise Resource Pool Synchronization was failing on my Project Server 2010 farm. On the Active Directory Enterprise Resource Pool Synchronization page it was showing the Synchronization Status as:

Some user data was not updated because synchronization of the Enterprise Resource Pool partially failed on <date> at <time>. To identify where the errors occurred, see the Windows event log on the server.

In looking at the server event logs the following event error was tied to the issue:

From this error I identified the ‘Resource Name’ person. To resolve, you will need to delete this enterprise resource, and then run the synchronization process to bring the user back in to the resource pool.

First, you should capture the current RBS information for the user so you can enter that back in after you complete all the steps below.

From Project Web app, go to Server Settings > Manage Users and then locate the affected user. Make note of the RBS settings for this user

After the synchronization job runs, the Synchronization Status should report:The Enterprise Resource Pool was successfully synchronized on [date] at [time].

Complete steps by verifying that the user has been imported back in by going back to Server Settings > Manage Users. Open the user and reenter the RBS information that was collected at the beginning of the steps.

Recently in one of my SharePoint 2010 PerformancePoint environments, I came across an event log with the following error:

Failed to read the acls for an item. Access is denied. (Exception from HRESULT: 0×80070005 (E_ACCESSDENIED))

Although I didn’t notice any issues in the environment, I suspected that this was not normal and proceeded to resolve. The following steps allowed me to eliminate the error from occurring any longer:

From Central Administration, go to Application Management > Manage Service Applications and then click on the Secure Store Service Application.

Put a check in the box next to the Target Application ID used for PerformancePoint Service and then click on Generate New Key from the ribbon.

Next, open the SharePoint 2010 Management Shell (PowerShell) and enter the following commands:PS> $w = Get-SPWebApplication -Identity [http://yourwebapplication]PS> $w.GrantAccessTo ProcessIdentity([domain\serviceaccount])
This PowerShell command does three things: It creates a new user (or makes sure the service account has permissions) for the contentdb for this web application, it makes the user a member of the db_owner role, and it creates a new schema for the user.

In my case the service account appeared to have the DB rights it needed, but something wasn’t 100% right, but the powershell commands resolved.