Move-SPProfileManagedMetadataProperty : UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’

it is some time ago, I was working on a case with a problem on using the Move-SPProfileManagedMetadataProperty cmdlet.

As per TechNet:

“Perform post-upgrade steps for an in-place upgrade (SharePoint Server 2010)” – http://technet.microsoft.com/en-us/library/cc262889.aspx

<excerpts>

Multi-value profile properties data from Microsoft Office SharePoint Server 2007 was stored in the Shared Services Provider (SSP) database as part of the Profile Services data. For SharePoint Server 2010, this data must now be converted to taxonomy data and be stored in the managed metadata database. To move and upgrade the data, you must have created a service application for the Managed Metadata service. After that is complete, you can use a Windows PowerShell cmdlet to upgrade the profile and taxonomy data and move it to the Taxonomy database. This cmdlet reconnects the data to the Managed Metadata and User Profile service applications. You must run this cmdlet for each Managed Metadata property that you want to upgrade; there is no option to specify all properties. Note that the Managed Metadata service does not accept certain characters (such as semicolons, angle brackets, pipes, quotation marks, or tab characters) that were allowable in user profile properties based on choice lists in Office SharePoint Server 2007. If a term or term set uses a character that is no longer accepted, the character is removed and replaced with a space when it is upgraded.

</exerpts>

 

So my customer had an in-place upgrade from MOSS 2007 to SharePoint 2010 and was now following the given advise to update its properties accordingly to work in the new SPS2010 environment.

Customer ran this command on the “SharePoint 2010 Management shell” (powershell):

ISSUE:

Move-SPProfileManagedMetadataProperty -Identity SPS-Interests -AvailableForTagging -ProfileServiceApplicationProxy 62b82935-1b38-4134-893e-8cf0b3d15417

Error: Move-SPProfileManagedMetadataProperty :
UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’.
Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/o r query notifications and/or xml data type methods.
At line:1 char:38
+ Move-SPProfileManagedMetadataProperty <<<< -Identity SPS-Interests -Availabl eForTagging -ProfileServiceApplicationProxy 06bcf7e1-9423-4d69-b445-b7ae62286d4 1
+ CategoryInfo : InvalidData: (Microsoft.Offic…ProfileProperty: SPCmdletMigrate…
ProfileProperty) [Move-SPProfileManagedMetadataProperty] , SqlException
+ FullyQualifiedErrorId : Microsoft.Office.Server.UserProfiles.PowerShell. SPCmdletMigrateDataForOneTaxonomicProfileProperty

 

So it seems to happen, when you have already installed SP1 AND additionally the June 11 cumulative update! on a SharePoint farm. This applies no matter if it is an in-place upgrade or by database attach. In bith cases you might need to run the cmdlet which then will fail as described.

With SP1 only applied, this issue does not occur! – You can use the cmdlet as described and it will work properly!  (I have this verified in my labs with DB attach)

 

Cause:

Most probably some kind of regression, coming with the June 11 CU packages. Also on any later build, this is still not fixed.

 

Workaround:

So when you consider or plan an in-place upgrade of an existing MOSS 2007 farm and you already know that you need to run tis cmdlet, you can just update until SP1 and run the cmdlet to transform your properties as desired.

After checking, that everything is working and your properties and metadata stores are healthy, you may continue to update to the most recent patch level or just wait until the next available CU that will address this issue.

So if this is applicable for you, this would work for now but if you have already updated up to June 11 or later CU, then you should open a service request at Microsoft’s support center!

Note!

On a certain post in TechNet forums, it was stated to “modify a stored procedure” to resolve this issue  –>  very bad idea!!  This is completely not supported!

This Issue needs to be raised as a hotfix via a regular support incident! There is currently no other resolution or workaround as written above!

 

+++ Update +++

For another otpion/Workaround to this issue, pelase find this post here:

Post June 2011 CU Move-SPProfileManagedMetadataProperty BUG – and the workaround

+++ End update +++