Archive

Archive for January, 2013

Fixing: One or more field types are not installed properly. Go to List settings page to delete these fields.

January 28, 2013 5 comments

Recently I ran into this error “One or more field types are not installed properly. Go to List settings page to delete these fields” when trying to access one of the migrated sites after having migrated from SharePoint 2007 to SharePoint 2010 doing a direct DB attachment (upgrade). 

This sometimes happens with a direct db attachment upgrade, and the SharePoint Server Publishing Infrastructure conflict with the internal list called Relationship List.  When the SharePoint Server Publishing Infrastructure is activated it creates this hidden list automatically.  This list is hidden and can be accessed by going to this URL: http://portalsite/relationships%20list/allitems.aspx.  What happens here since the SharePoint Server Publishing Infrastruture feature is already activated in the SP2007 enviornment and then moved over to SP2010 via DB attachment there is a conflict with a column in that list called GroupGuid.  In SharePoint 2007 the column name is GroupId and is type text, and is changed in SharePoint 2010 to GroupGuid with the same type text.  For whatever reason this column type is not updated when migrated over.  This column type needs to be set at GUID not text.  Since there is no way within SharePoint to create a column with a GUID type the best way to fix the problem is to delete this list and have it automatically re-created.  To successfully delete this list follow these steps.

1.  Go to Site Actions, Site Settings and Site Collection features under Site Collection Administration.
2.  Deactivate the SharePoint Server Publishing Infrastructure feature
3.  Go back to http://portalsite/relationships%20list/allitems.aspx
4.  Go to List, List Settings and Delete this list (If the SharePoint Server Publishing Infrastructure feature is not deactivated this option will not be visable)
5.  After deleting the list go back to Site Actions, Site Settings, and Site Collection features
6.  Activate SharePoint Server Publishing Infrastructure feature (Note: If you recieve this error:

Column Limit Exceeded.

There are too many columns of the specified data type. Please delete some other columns first. Note that some column types like numbers and currency use the same data type.

 when trying to reactivate this feature read this blog  posting –http://jshidell.com/2013/01/28/fixing-cannot-activate-sharepoint-2010-publishing-infrastructure-feature-column-limit-exceeded/ )

7.  The SharePoint Server Publishing Infrastructure feature will automatically recreate the list.

Go back to http://portalsite/relationship%20list/allitems.aspx and varify that the column GroupGuid now has a column of type GUID instead of text.

Now go back to the site you were trying to access and you should no loger get that error message “One or more field types are not installed properly. Go to List settings page to delete these fields”

Fixing: Cannot Activate SharePoint 2010 Publishing Infrastructure Feature – Column Limit Exceeded

January 28, 2013 6 comments

I ran into this problem: 

Column Limit Exceeded.

There are too many columns of the specified data type. Please delete some other columns first. Note that some column types like numbers and currency use the same data type.

This happened after migrating from SharePoint 2007 to SharePoint 2010 using the database attach (upgrade) approach and trying to activate the SharePoint Server Publishing Infrastructure feature.

There is a hidden list in SharePoint 2010 called Quick Deploy Items which is created when you activate the SharePoint Server Publishing Infrastructure feature.  You can access it through this URL:  http://portalsite/quick%20deploy%20items  If this list already exists prior to you trying to Activate this feature it will throw the error above.  For some strange reason there are 3 columns in this list that have been duplicated about 4 different times (JobId, ItemUrl, and ItemType) which causes the list to exceed its column limit.

To fix the problem you will have to delete the duplicate columns.  You could write a powershell script to iterate through the list columns and delete them or you can go into each column separately and delete them.  That is what I did.  

After deleting the extra columns go back to try and activate the SharePoint Server Publishing Infrastructure feature again.  This time you should be successful.

If you get another error try to active the feature using stsadm command with Farm Admin privilages (stsadm -o activatefeature -name PublishingSite -url http://portalsite)

I tried to active the feature using STSADM and I still recieved an error, but a different one this time.  This time the error was:

Provising did not succeed.  Details:  Failed to provision role definitions.  OrginalException:  Value does not fall within the expected range

To overcome this error just simply run the same STSADM command again, but his time with the –force switch.

Hopefully this helps thoughs that have been having problems.

 

Mirror Staging SharePoint 2010 Environment with SharePoint 2010 Production Environment by Moving Over Content Databases using PowerShell

January 22, 2013 2 comments

To those that maintain both a Production and a Staging SharePoint 2010 environment know the importance of trying to have both environments balanced (mirrored) to allow for a “true” production type environment in a staging environment.  This allows for “true” testing of new services/deployments/solutions, patching before  deploying to production.

Of course you can implement 3rd party solutions (Metalogix, Doc Ave, Syntergy) to allow for a more scheduled (automation) technique to maintain your staging enviornment to reflect production, if you choose to go that direction that is fine.  However I will be blogging on how you can accomplish the same thing with the use of PowerShell.

This will require four separate PowerShell scripts.

These scripts will *assume* that your SharePoint 2010 environment is using SQL Server 2008 (or) R2 and that your SharePoint 2010 production SQL databases have a scheduled backup job.

This also *assumes* that both your Production and Staging environment are separate and have their own farms and have different SQL Servers, but are configured exactly the same.

The 1st script will  copy the latest FULL Production SharePoint 2010 Content Databases backups,  and move these backup files to a destination on the SharePoint 2010 Staging SQL Server.

copy-dbbackups.ps1
__________________________________________________________________________________________________________________

1.  Log into your SharePoint 2010 Production SQL Server.

2.  Copy script below, and change the parameters in Red that correspond to your enviornment, and paste into notepad and save file as copy-dbbackups.ps1
3.  Open up SQL Server Managment Studio (SSMS) on your Production SQL server
4.  Expand SQL Server Agent
5.  Expand Jobs
6.  Right click Jobs and select “New Job”
7.  Under the General tab Give Job a name i.e. (“copy dbbackups”)
8.  Under Steps tab click “New..” button
9.  Give step name i.e. (“copy dbbackups”)
10.  Under Type: select “PowerShell” from dropdown
11.   Under Run As:  Select “SQL Server Agent Service Account”
12.  Under Command:  Click Open… button and browse to the location where you saved copy-dbbackups.ps1 
13.  Under Schedules Tab click the “New..” button
14.  Give Schedule Name:  i.e. (copy dbbackups)
15.  Schedule your job sometime after your FULL backups.  i.e. (If your SQL full backups occur on a Sunday night, schedule your job to happen sometime on Monday after all backups have been completed).
16.  Save your job.
17 .  When this job runs, this may take a long time depending on your database backup file sizes.  My suggestion is to schedule this for overnight in a time where there is less network traffic.

====================================================================

#Remove old backup files on the SQL Server Staging server
get-childitem
\\staging SQL server\m`$\restore\prod2stg | remove-item -force

#Grab backup files from backup location on Production SQL Server
$files = get-childitem
\\backup location\backup\sql_backups\SP2010\FULL | where {$_.FullName -like “*SPContent*_backup_*”  -and $_.CreationTime -ge (get-date).adddays(-4)}

#Copy backup files from backup location on Proction SQL Server to a location on the Staging SQL Server
$files | copy-item -dest
\\staging SQL server\m`$\restore\prod2stg

#Remove databases not needed i.e. Admin and Mysites from Staging SQL Server
get-childitem
\\staging SQL Server\m`$\restore\prod2stg | { if $_.Name -like “*_Admin_*” or $_.Name -like “*_MySites_*“) { $_.Delete() } }

=====================================================================

exec-query.ps1
restore-dbbackups.ps1

This will require two scripts.  1 script will execute a SQL query to establish SQL connection strings and create the datasets, the second script will actually restore the backup databases into SQL.

1.  Open up notepad and copy the following script below and save as exec-query.ps1.
Nothing should need to be changed in this script, just copy as is. 

exec-query.ps1

=====================================================================

function exec-query($sql,$parameters=@{},$conn,$timeout=0,[switch]$help)
{
if($help)
{

$msg = @” Execute a sql statement. Parameters are allowed.  Input parameters should be a dictionary of parameter names and values.  Return value will usually be a list of datarows.”@

Write-Host $msg
return

}
$cmd=new-object system.Data.SqlClient.SqlCommand($sql, $conn)
$cmd.CommandTimeOut = $timeout
foreach($p in $parameters.Keys)
{
[void] $cmd.Parameters.AddWithValue(“@$p”, $parameters[$p])
}

$ds=New-Object  System.Data.DataSet
$da=New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
$da.fill($ds) | Out-Null

return $ds

}
========================================================================

restore-dbbackups.ps1

This script will call the exec-query script, and then restore the backup of the content databases moved over from your Production SQL environment into the Staging SQL server.

Copy the script below and change the parameters in Red with your environment information.  Save file as restore-dbbackups.ps1

1.  Open up SQL Server Managment Studio (SSMS) on your Staging SQL Server
2.  Expand SQL Server Agent
3.  Expand Jobs
4.  Right click Jobs and select “New Job”
5.  Under the General tab Give Job a name i.e. (“restore dbbackups”)
6.  Under Steps tab click “New..” button
7.  Give step name i.e. (“restore dbbackups”)
8.  Under Type: select “PowerShell” from dropdown
9.   Under Run As:  Select “SQL Server Agent Service Account”
10.  Under Command:  Click Open… button and browse to the location where you saved restore-dbbackups.ps1 
11.  Under Schedules Tab click the “New..” button
12.  Give Schedule Name:  i.e. (restore dbbackups)
13.  Schedule your job sometime after your FULL backups from Production have been moved over to your Staging environment
14.  Save your job.
15.  When this job runs, this may take a long time depending on your database backup file sizes.  My suggestion is to schedule this for overnight in a time where there is less network traffic.  If this is staging are you are not concerned about network traffic schedule it when you like

=========================================================================

#Load the exec-query.ps1 script
. M:\restore\exec-query.ps1

#Grab the database backup files that were moved over from your Production SQL environment.
$backups = get-childitem M:\restore\prod2stg

#Loop through the backup files and trim/truncate the database name by removing unwanted characters in the backup dbname file name.  For example my backup database files names were in this format (SPContent_Site1_backup_2013_02_22_200009_6652162.bak).  I wanted to remove every character after _backup*.  You will have to play with the parameters here a little to get this to work for your database name by messing with the substring parameters ($_.name.substring(0,$_.name.lastindexof(“_”)-13), $dbname.substring(0,$dbname.lastindexof(“_”), and $dbname.trimend(“_backup”)).

$backups | % {

$dbname = $_.name.substring(0,$_.name.lastindexof(“_”)-13)  ; $dbname = $dbname.substring(0,$dbname.lastindexof(“_“)); $dbname = $dbname.trimend(“_backup“)

#Restore backup database files to Staging SQL Server
restore-SQLdatabase -SQLServer “SQL SERVER” -SQLDatabase $dbname -Path $($_.FullName) -TrustedConnection $true

#Establish a SQL connection
$conn = new-object data.sqlclient.sqlconnection “Server=SQL SERVER;Integrated Security=true”

$conn.open()

#Set database files to Simply Recovery and execute the exec-query.ps1 script
$sqlcmd1 = “ALTER DATABASE [$dbname] SET RECOVERY SIMPLY WITH NO_WAIT”
exec-query $sqlcmd1 -conn $conn

}

=============================================================================

This last script is not really necessary but I did run into some problems after doing restores where SharePoint did not recognize the databases in the farm.  To overcome this I just dismounted and the remounted the databases in my staging environment by running this script.

Copy script below and change the parameters in “Red” to reflect your environment.  Save file as dismount-mount-dbs.ps1

If you use Task Scheduler on your SharePoint Server (CA).  Then just add this script as a scheduled task on that server.   Schedule it to run after all databases have been restored to your Staging SQL environment.

dismount-mount-dbs.ps1

==============================================================================

#Get databases currently mounted in your SharePoint 2010 staging farm
$databaseName = Get-SPDatabase

#foreach to loop through the content databases in your staging farm dismount them, and then re-mount them.
foreach($database in $databaseName)
{
if ($database -like “*SPContent_*)
{
Write-Host “Dismounting database ” $database.Name -foreground “green”
Dismount-SPContentDatabase -Identity $database.Name -confirm:$false
Start-Sleep -s 10
Write-Host “Mounting database ” $database.Name -foreground “green”
Mount-SPContentDatabase -name $database.Name -DatabaseServer STAGING SQL SERVER -WebApplication STAGING WEB APPLICATION URL -confirm:$false
Start-Sleep -s 10
}
}
=================================================================================

Setting SharePoint 2010 Site Collection Search Settings using PowerShell

January 16, 2013 4 comments

I needed to modify the Site Collection Search Settings for all my Site Collections in my SharePoint 2010 web application to enable custom scopes by specifying a Search Center URL, and to also change the Site Collection Search Dropdown Mode to use “Show scopes dropdown”.  Instead of having to go to each Site Collection to check these settings I turned to PowerShell to iterate through all my Site Collections to set these parameters.  Below is the script I used to accomplish this.

This script assumes that the Search Center URL is null.

Different Parameters you can set for the Site Collection Search Dropdown Mode [SRCH_SITE_DROPDOWN_MODE]

Do Not Show Scopes Dropdown, and default to contextual scope – HideScopeDD_DefaultContextual
Do Not Show Scopes Dropdown, and default to target results page – HideScopeDD
Show scopes Dropdown – ShowDD
Show, and default to ‘s’ URL parameter – ShowDD_DefaultURL
Show and default to contextual scope – ShowDD_DefaultContextual
Show, do not include contextual scopes – ShowDD_NoContextual
Show, do not include contextual scopes, and default to ‘s’ URL parameter – ShowDD_NoContextual_DefaultURL

Parameter you can use to change the Site Collection Search Result Page

[“SRCH_TRAGET_RESULTS_PAGE] i.e $web.AllProperties[“SRCH_TRAGET_RESULTS_PAGE”] = “/_layouts/OSSSearchResults.aspx”

Since my Search Settings already had the [“SRCH_TRAGET_RESULTS_PAGE”] parameter set correctly I did not need to update it or add it in my script, but I doesn’t hurt.

==============================================================

$webApp = Get-SPWebApplication http://portal | Get-SPSite -Limit All

foreach ($site in $webApp)
{

$web = Get-SPWeb $site.Url

if ($web.AllProperties[“SRCH_ENH_FTR_URL”] -eq $null) #Checks to see if the Search Center URL is null, if Yes, set parameters, and update settings.
{
$web.AllProperties[“SRCH_ENH_FTR_URL”] = “/search/Pages/” #Sets the Search Center URL
$web.AllProperties[“SRCH_SITE_DROPDOWN_MODE”] = “ShowDD” #Sets the Site Collection Search Dropdown Mode
$web.Update #Update Search Settings
}

}

===============================================================