Howto: convert a Excel workbook to an xml file

This post is a bit off topic for this blog but I’ll post it anyway as a reminder for myself and hopefully to help others who want to do the same. At a client someone had to convert a very simple, plain, flat text excel 2007 workbook to an xml file format.

I have made an example to let you see how to do this. Below you see the workbook that has to be converted to xml.

I thought that just saving the file with “save as” to xml format would do it but I thought wrong. I got an error that there were no XML mappings found in the workbook. For Excel 2007 you need a add-in that is called OfficeExcel2003XMLToolsAddin.exe and which can be downloaded here. On the download page you can also read how to install and open the add-in in Excel 2007.

After you have installed the add-in and you click the Add-Ins menu the “XML tools” menu have to added (see below).

Now click on the XML tools drop down and choose the first option “Convert a Range to an XML List”. In the little window that appears you have to fill in the range of cells you want to convert and indicate that the first row contains the column headers (see below).

Click OK and the workbook will be converted (see below)

When you open the Developer menu and click “Source” you have to see the “XML maps in this workbook” on the right of the screen (see below). Now the workbook is ready to be converted to an XML file.

To convert this workbook you have to click “Export” on the Developer menu. Choose a filename to save the xml file.  The result will look like:

Tags: Howto

Pricing for MOSS can be complex, especially when calculating the cost of optional products.  For your convenience and information, Bamboo Solutions developed the SharePoint Price Calculator.  This tool can help you develop a good ballpark estimate of your total licensing expenditures. At the moment it is only for SharePoint 2007

Find here the calculator site.

Microsoft also have their own calculator here and with more recent server software.

Tags: license

Resources.

This is the final post in these series about scripting a SP2010 installation with PowerShell. In this post you will find all resources/websites I used for making the PS scripts.

SP2010 Site Definitions:
http://www.toddbaginski.com/blog/archive/2009/11/20/which-sharepoint-2010-site-template-is-right-for-me.aspx

PowerShell script editor:
http://powergui.org/servlet/KbServlet/download/1983-102-2817/PowerGUI.1.9.6.1027.msi

PowerShell script execute policy:
http://www.itexperience.net/2008/07/18/file-cannot-be-loaded-because-the-execution-of-scripts-is-disabled-on-this-system-error-in-powershell/

PowerShell SharePoint cmdlets list:
http://cglessner.blogspot.com/2009/10/early-look-at-sharepoint-2010.html

SP2010 Server installation script:
http://blog.fpweb.net/powershell-script-to-install-sharepoint-2010/
http://technet.microsoft.com/en-us/library/ee805951(office.14).aspx (Technet script)

SP2010 prereqs download list:
http://blogs.msdn.com/opal/archive/2009/10/25/sharepoint-2010-pre-requisites-download-links.aspx

Database attach mode:
http://sharepointgeorge.com/2009/upgrading-content-database-sharepoint-2010-database-attach-method/

Configure service applications:
http://technet.microsoft.com/en-us/library/ee704544(office.14).aspx
http://stsadm.blogspot.com/2009/12/creating-sharepoint-2010-enterprise.html (search service)

Misc:
http://blog.fpweb.net/powershell-script-to-install-sharepoint-2010/
http://stsadm.blogspot.com/ ; http://stsadm.blogspot.com/2009/10/sharepoint-2010-psconfig-and-powershell.html
http://blog.rafelo.com/2009/10/upgrading-2007-wcm-sites-to-2010-wcm.html
http://blog.rafelo.com/2009/10/sharepoint-upgrade-fundamentals-part-1.html
http://blogs.msdn.com/ekraus/archive/2009/11/06/sharepoint-2010-provisioning-a-new-farm-with-powershell.aspx

Tags: beta, installation, powershell, SP2010

Web application and site collection

The SP2010 installation that I have used for this series of posts about scripting SP2010 with PowerShell was intended to host a SP2010 internet site. That is why the next to scripts are made to configure a web application and site collection for an internet site. The parameters can also be used to configure web applications and site collections for other purposes.

The script for configuring a web application looks like this:

#Include the SharePoint cmdlets
#Add-PsSnapin Microsoft.SharePoint.PowerShell

#Set the farm variables
$sp_webapp_name = “Internetsite”
$sp_webapp_port = 80
$sp_webapp_hostheader = “www2010″
$sp_webapp_url = “http://www2010”
$sp_webapp_apppool = “www2010AppPool”
$sp_webapp_apppoolaccount = “devnet\xxxxxxx”
$sp_webapp_databasename = “www2010_DB”
$sp_webapp_databaseserver = “DEVNET-R2″

#Create a new Web Application
new-spwebapplication -name $sp_webapp_name -Port $sp_webapp_port -HostHeader $sp_webapp_hostheader -URL $sp_webapp_url -ApplicationPool $sp_webapp_apppool -ApplicationPoolAccount (Get-SPManagedAccount $sp_webapp_apppoolaccount) -DatabaseName $sp_webapp_databasename -DatabaseServer $sp_webapp_databaseserver

Write-Host “Web application is configured”

After you have configured the web application you can take the next step by configuring the site collection:

# Add-PsSnapin Microsoft.SharePoint.PowerShell
# base template values
# Name                 Title                                    LocaleId   Custom
# —-                 —–                                    ——–   ——
# GLOBAL#0             Global template                          1033       False
# STS#0                Team Site                                1033       False
# STS#1                Blank Site                               1033       False
# STS#2                Document Workspace                       1033       False
# MPS#0                Basic Meeting Workspace                  1033       False
# MPS#1                Blank Meeting Workspace                  1033       False
# MPS#2                Decision Meeting Workspace               1033       False
# MPS#3                Social Meeting Workspace                 1033       False
# MPS#4                Multipage Meeting Workspace              1033       False
# CENTRALADMIN#0       Central Admin Site                       1033       False
# WIKI#0               Wiki Site                                1033       False
# BLOG#0               Blog                                     1033       False
# SGS#0                Group Work Site                          1033       False
# TENANTADMIN#0        Tenant Admin Site                        1033       False

$sp_sc_template = “BLANKINTERNET#2″
$sp_sc_webappurl = “http://www2010”
$sp_sc_name = “Home”
$sp_sc_language = “1033″
$sp_sc_owner = “devnet\xxxxxxxxx”
new-SPSite -url $sp_sc_webappurl -OwnerAlias $sp_sc_owner -Language $sp_sc_language -Template $sp_sc_template -Name $sp_sc_name

As you can see I used template “BLANKINTERNET#2″ which is not in the base template list shown above. This template is for a Publishing site with workflow. I found this in a blogpost of Todd Baginsky. There you can find a complete list of site templates in SP2010.

After setting up the site collection I have migrated the database of the old website to this new SP2010 farm. Before mounting the database to the new web application I have first tested the database with the following command (see also screenshot below):

Test-SPContentDatabase -Name WSS_Internet_Content -WebApplication Internetsite

The results of the testcommand (shown above) show that there are some features in the old site that where not found on the new SP2010 farm. Because we did not know exactly which features where missing we just tried to mount the database because it was only a test.

Mount the database with the following command:

Mount-SPContentDatabase -Name WSS_Internet_Content -WebApplication Internetsite

At the bottom of the screenshot you can see that mounting the database is in progress.

Tags: beta, installation, powershell, SP2010

SharePoint 2010 server installation

It took a while but here is part 3 of these series about a complete PowerShell scripted SharePoint 2010 installation.

I used the script you see below. It maybe is not the nicest PS script to do this installation. On the internet I have found scripts with error handling and checking every step of the script but I wanted to find out myself and this is the result.

# Execute setup.exe with the setupfarmsilent xml to install SharePoint
Write-Host “Installing SharePoint 2010 Quietly”
& ‘C:\install\sp2010i\setup.exe’ ‘/config’ ‘C:\install\config.xml’ | out-null

# Include the SharePoint cmdlets
Write-Host “Loading SharePoint 2010 PowerShell cmdlets”
Add-PsSnapin Microsoft.SharePoint.PowerShell

# Set the farm variables
Write-Host “Setting SharePoint 2010 Farm variables”
$sp_cfdatabasename = “SP2010_Config”
$sp_cadatabasename = “SP2010_Admin_Content”
$sp_databaseserver = “DEVNET-R2″
$sp_passphrase = (ConvertTo-SecureString “ThisIsThePassphrase!” -AsPlainText -force)
$sp_password = (ConvertTo-SecureString “xxxxxxxx” -AsPlainText -force)
$sp_username = “DEVNET\xxxxxxx”

# Clean up the credentials
$sp_credentials = New-Object System.Management.Automation.PsCredential $sp_username,$sp_password

# Execute the config wizard
Write-Host “Add configuration and administration databases”
New-SPConfigurationDatabase -DatabaseName $sp_cfdatabasename -DatabaseServer $sp_databaseserver –AdministrationContentDatabaseName $sp_cadatabasename -Passphrase $sp_passphrase -FarmCredentials $sp_credentials

# Provision a Central Administration Site
Write-Host “Add Central Administration”
New-SPCentralAdministration -Port 11111 -WindowsAuthProvider “NTLM”

# Install all of the help files within Central Admin
Write-Host “Installation of Help files”
Install-SPApplicationContent

# Secure the files and registry entries on the server 
Write-Host “Resource security”
Initialize-SPResourceSecurity

# to install the features on the server. 
Write-Host “Installation of features”
Install-SPFeature –AllExistingFeatures

# to install and and then provision the services onto the farm.
Install-SPService

# Disable the loopback check, run this command from PowerShell.
Write-Host “DisableLoopbackCheck registry key”
New-ItemProperty HKLM:\System\CurrentControlSet\Control\Lsa -Name “DisableLoopbackCheck”  -value “1″ -PropertyType dword

Write-Host “Installation script is ready”

If you have any questions feel free to ask. My next post will have scripts for configuring a web application and a site collection.

Tags: beta, installation, powershell, SP2010

At a client I am investigating a SharePoint farm which has been connected to another AD domain in the past and several accounts from that domain can still be found in SharePoint. For example if one adds a user to a group through a people picker, for some people two or more accounts are shown. One Olddomain\account and one Newdomain\account. (see picture)

I thought that running a Full Profile import 3 times solved the issue. But I think this will only work for accounts that are no longer in use in the Newdomain. I searched for a solution to remove the users from SharePoint and found that I can remove users from a list that can be found by typing this url: http://<portalname>/_catalogs/users/detail.aspx

After deleting the unused accounts, they no longer appear in the people picker (see below).

Tags: administration

In the past I encountered this problem myself and a colleague of mine had the same problem this week. Uploading large files to SharePoint 2007 that is installed on a Windows Server 2008. By accident I have found the following post through the “Windows Server 2008 Resource Center for SharePoint Products and Technologies” which can be found here.

Tags: error

I’m doing a lot of migrations nowadays. SharePoint 2007 32 bit farms to 64 bit farms or between test and production farms. In the past I have tried to migrate SSP’s once but found it difficult. Fortunately the SSP’s I migrated did not have to many settings so manually changing the SSP of the target farm was not a problem.

But now I have found a tool on Codeplex that can migrate a lot of settings for you. It is a command line tool that exports settings from the source SSP to xml files and you can import those settings in the target SSP.

Good tool that save me a lot of time! Download it here.

Output when importing settings in SSP:

Tags: migration, solution, SSP, stsadm

The application log of one of the sharepoint farm servers filled with the event id’s 6482, 7076 and 6398. On the Technet forum I found a link to KB article 946517. The hotfix descripted in this article solved the errors on the SharePoint farm.

The hotfix is for Windows 2003 and 2008.

The contents of the errors was:

Event Type: Error
Event Source: Office SharePoint Server
Event Category: Office Server Shared Services
Event ID: 6482
Date:  2/11/2010
Time:  9:34:40 AM
User:  N/A
Computer: XXXXX
Description:
Application Server Administration job failed for service instance Microsoft.Office.Excel.Server.ExcelServerSharedWebServiceInstance (5910f58e-29bf-4c97-8ae4-53d234751819).

Reason: Exception from HRESULT: 0×80005006

Techinal Support Details:
System.Runtime.InteropServices.COMException (0×80005006): Exception from HRESULT: 0×80005006

Server stack trace:
   at System.DirectoryServices.Interop.UnsafeNativeMethods.IAds.PutEx(Int32 lnControlCode, String bstrName, Object vProp)
   at System.DirectoryServices.PropertyValueCollection.OnClearComplete()
   at System.DirectoryServices.PropertyValueCollection.set_Value(Object value)
   at Microsoft.SharePoint.AdministrationOperation.Metabase.ApplicationPool.set_IdentityType(ApplicationPoolIdentityType value)
   at Microsoft.SharePoint.AdministrationOperation.SPProvisioningAssistant.ProvisionIisApplicationPool(String name, ApplicationPoolIdentityType identityType, String userName, SecureString password, TimeSpan idleTimeout, TimeSpan periodicRestartTime)
   at Microsoft.SharePoint.AdministrationOperation.SPAdministrationOperation.DoProvisionIisApplicationPool(String name, Int32 identityType, String userName, String password, TimeSpan idleTimeout, TimeSpan periodicRestartTime)
   at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Microsoft.SharePoint.AdministrationOperation.SPAdministrationOperation.DoProvisionIisApplicationPool(String name, Int32 identityType, String userName, String password, TimeSpan idleTimeout, TimeSpan periodicRestartTime)
   at Microsoft.SharePoint.Administration.SPMetabaseManager.ProvisionIisApplicationPool(String name, Int32 identityType, String userName, SecureString password, TimeSpan idleTimeout, TimeSpan periodicRestartTime)
   at Microsoft.Office.Server.Administration.SharedWebServiceInstance.Synchronize()
   at Microsoft.Office.Excel.Server.ExcelServerSharedWebServiceInstance.Synchronize()
   at Microsoft.Office.Server.Administration.ApplicationServerJob.ProvisionLocalSharedServiceInstances(Boolean isAdministrationServiceJob)

Event Type: Error
Event Source: Office SharePoint Server
Event Category: Office Server Shared Services
Event ID: 7076
Date:  2/11/2010
Time:  9:34:40 AM
User:  N/A
Computer: XXXXX
Description:
An exception occurred while executing the Application Server Administration job.

Message: Exception from HRESULT: 0×80005006

Techinal Support Details:
System.Runtime.InteropServices.COMException (0×80005006): Exception from HRESULT: 0×80005006

Server stack trace:
   at System.DirectoryServices.Interop.UnsafeNativeMethods.IAds.PutEx(Int32 lnControlCode, String bstrName, Object vProp)
   at System.DirectoryServices.PropertyValueCollection.OnClearComplete()
   at System.DirectoryServices.PropertyValueCollection.set_Value(Object value)
   at Microsoft.SharePoint.AdministrationOperation.Metabase.ApplicationPool.set_UserName(String value)
   at Microsoft.SharePoint.AdministrationOperation.SPProvisioningAssistant.ProvisionIisApplicationPool(String name, ApplicationPoolIdentityType identityType, String userName, SecureString password, TimeSpan idleTimeout, TimeSpan periodicRestartTime)
   at Microsoft.SharePoint.AdministrationOperation.SPAdministrationOperation.DoProvisionIisApplicationPool(String name, Int32 identityType, String userName, String password, TimeSpan idleTimeout, TimeSpan periodicRestartTime)
   at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Microsoft.SharePoint.AdministrationOperation.SPAdministrationOperation.DoProvisionIisApplicationPool(String name, Int32 identityType, String userName, String password, TimeSpan idleTimeout, TimeSpan periodicRestartTime)
   at Microsoft.SharePoint.Administration.SPMetabaseManager.ProvisionIisApplicationPool(String name, Int32 identityType, String userName, SecureString password, TimeSpan idleTimeout, TimeSpan periodicRestartTime)
   at Microsoft.Office.Server.Administration.SharedWebServiceInstance.CreateSharedWebServiceApplicationPool(SharedResourceProvider srp)
   at Microsoft.Office.Server.Administration.ApplicationServerJob.ProvisionLocalSharedServiceInstances(Boolean isAdministrationServiceJob)

Event Type: Error
Event Source: Windows SharePoint Services 3
Event Category: Timer
Event ID: 6398
Date:  2/11/2010
Time:  9:34:40 AM
User:  N/A
Computer: XXXXX
Description:
The Execute method of job definition Microsoft.Office.Server.Administration.ApplicationServerAdministrationServiceJob (ID 1d847949-a0b9-483c-8fa4-abafa4208483) threw an exception. More information is included below.

Exception from HRESULT: 0×80005006

Tags: error, event id, hotfix

If you have been installing SharePoint you have probably also seen and fixed the DCOM 10016 error. This error occurs in the event log when the SharePoint service accounts doesn’t have the necessary permissions (Local Activation to the IIS WAMREG admin service).

On a Windows Server 2003 or Windows Server 2008 machine you would just fire up the dcomcnfg utility and enable Local Activation for your domain accounts. But for Windows Server 2008 R2 you cannot do this, the property dialog is all disabled due to permission restrictions.

Read here how to solve this. Fortunately I found this blogpost quickly. Thanks Wictor!

Tags: error, installation, security