Dirk Van den Berghe SharePoint Admin Blog

The fix for: Method Post Error in Excel 2007 Import Spreadsheet to SharePoint

I was recently asked to investigate an issue with importing an Excel sheet into a new SharePoint List. After trying to duplicate the problem on my lab environment I got a different error caused by my Excel 2007. After searching the web for a solution, I came across a blog post, describing the issue and giving a valid solution. The solution works and I advise you to use it when you encounter the problem J

For your convenience, I will copy the complete article in this post (you never know when blogs disappear rightJ )

Source: http://www.sharepointblogs.com/akalli/archive/2007/03/07/the-fix-for-method-post-error-in-excel-2007-import-spreadsheet-to-sharepoint.aspx

Have you tried importing a Excel 2007 spreadsheet into SharePoint Service v3 and are getting a Method Post Error? Thanks to an open SharePoint forum, someone posted the fix. 

I take no credit for this fix. My SharePoint hosting company is www.frontpages-web-hosting.com and on their forum is a post by Bradley Elder (Thanks Bradley!) I was able to easily apply the change mentioned in his post and can now import a spreadsheet from Excel 2007 to SharePoint Services v3.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

Here is his post below:

Importing lists from Excel 2007 returns a Method 'Post' of object 'IOWSPostData' failed dialog. Again, not really a problem with WSS 3.0 but rather the result of a failed Application.SharePointVersion() call in the Excel Add-In which results in Excel attempting to use the IOWSPostData.Post() method to publish the Excel range which is used with SharePoint Team Services 1.0. By forcing the version lookup result variable to 2 or greater, Excel will use SOAP to communicate with WSS 3.0 and the publish request will be successful. To make this change, open the Excel Add-In EXPTOOWS.XLA locate in C:\Program Files\Microsoft Office\Office12\1033 by default. Press Alt+F11 to display the Visual Basic code editor and search (Ctrl+F) for the line lVer = Application.SharePointVersion(URL). Comment out that line with a single quote and add the line lVer=2 so your Intialize() method should now look like this:

Sub Initialize(List, Title, URL, QuickLaunch)

strQuickLaunch = QuickLaunch

aTarget(iPublishURL) = URL

aTarget(iPublishListName) = List

aTarget(iPublishListDesc) = Title

'lVer = Application.SharePointVersion(URL)

lVer = 2

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~ 

Andrea Kalli

Andrea Kalli Virtual Trainer and Assistant, LLC

www.virtualassist.net

Upgrade SQL Express to Standard or Enterprise

I mentionned this before in one of my previous posts: you can upgrade SQL express to Standard or Enterprise using the commandline setup.exe SKUUPGRADE=1.

Now I was glad to see that Todd Klindt has published an article of the complete upgrade setup process. Apparently you need to be aware of some additional steps that are easily looked over.

So go check out the complete article on Todd's blog @ http://www.toddklindt.com/blog/Lists/Posts/ViewPost.aspx?ID=55

Site Template identifiers for use with STSADM

For those CLI geeks out there like me I really enjoy using stsadm for creating new site collections, etc. One of the things I always keep looking for are those identifiers for specifying the site template for the new site collection. I have gathered a list now of available ones and give them to you with this post.

GLOBAL#0 = Global template
STS#0 = Team Site
STS#1 = Blank Site
STS#2 = Document Workspace
MPS#0 = Basic Meeting Workspace
MPS#1 = Blank Meeting Workspace
MPS#2 = Decision Meeting Workspace
MPS#3 = Social Meeting Workspace
MPS#4 = Multipage Meeting Workspace
CENTRALADMIN#0 = Central Admin Site
WIKI#0 = Wiki Site
BLOG#0 = Blog
BDR#0 = Document Center
OFFILE#0 = Records Center
OFFILE#1 = Records Center
OSRV#0 = Shared Services Administration Site
SPS#0 = SharePoint Portal Server Site
SPSPERS#0 = SharePoint Portal Server Personal Space
SPSMSITE#0 = Personalization Site
SPSTOC#0 = Contents area Template
SPSTOPIC#0 = Topic area template
SPSNEWS#0 = News Site
CMSPUBLISHING#0 = Publishing Site
BLANKINTERNET#0 = Publishing Site
BLANKINTERNET#1 = Press Releases Site
BLANKINTERNET#2 = Publishing Site with Workflow
SPSNHOME#0 = News Site SPSSITES#0 = Site Directory
SPSCOMMU#0 = Community area template
SPSREPORTCENTER#0 = Report Center
SPSPORTAL#0 = Collaboration Portal
SRCHCEN#0 = Search Center with Tabs
PROFILES#0 = Profiles
BLANKINTERNETCONTAINER#0 = Publishing Portal
SPSMSITEHOST#0 = My Site Host
SRCHCENTERLITE#0 = Search Center
SRCHCENTERLITE#1 = Search Center
SPSBWEB#0 = SharePoint Portal Server BucketWeb Template  

SharePoint and SQL Server: some tips

I was attending the TechEd session OFF310_Understanding SQL Server Database Options for Microsoft Office SharePoint Server 2007 presented by Todd Klindt where I picked up these tips regarding SQL Server used by SharePoint (MOSS).

• Set ContentDB's offline: despite what many people believe, this does not take your database offline as in not available anymore. Instead this tells SharePoint not to make this database available for new Site Collections. Using this feature you can force new Site Collections to be created in specific databases by setiing all other databases offline. As a reminder, you can set database offline in Central Administration -> Application Management -> Content Databases. Select the database you wish to set offline and then change the Database Status dropdown box to Offline
• There is currently no support for SQL 2008 to use it with SharePoint.
• If using SQL Express, don't forget to download the Management Studio and the Express Edition Toolkit
• SQL Express can be upgraded. Install Standard or Enterprise with the following syntax: setup.exe SKUUPGRADE=1.
• Never shrink your databases! This operation fragments your indexes and data. Furthermore you will suffer some performance degradation when the database needs to grow again.
• Don't use autogrow for your contentdatabases, unless you let it grow with big chunks instead of the default 10% (e.g. 10GB at a time).
• Defragmenting file system: SQL will be faster if the database files are contiguous in the file system. Using the built in defrag tool will have performance ramifications. Consider using something like Diskeeper and its intelligent defrag. Consider stopping SQL if possible.
  Diskeeper White paper on SQL file fragmentation: http://files.diskeeper.com/pdf/SQLdefragmented.pdf
• Properly configure SQL Surface Area for SharePoint
• Use SP2 if possible, it has improvements for SharePoint
• Do not change SharePoint databases via SQL Queries.  Microsoft hates that.
• Can use SharePoint farm backups to back up SQL
• Don’t forget to include your System databases in your maintenance plans.

Attached to this post, you can find the slides of the Teched session

Outlook Tip: natural language dating

Found this oddly enough on a SharePoint blog: 

One Outlook tip that a lot of people don't know about Outlook – natural language dating – try typing "third Tuesday of August" into an Outlook date field and you'll see what I mean.

One document, multiple Document Libraries (by Todd Klindt)

This post was grabbed from the blog of Todd Klindt that is worth looking at.

Here is the excerpt:

Here's another question I see a lot, "Is there a way to have one document in multiple libraries, but have there be a master?" The answer is "sort of." In SharePoint 2007 there is a new concept of "Content types." A content type is kind of like a template for document types you store in libraries. In this case we're going to leverage the "Document Link" content type. You can use it to create a link to a master document in another library or to any URL in general. In this post I'll walk you through how to do it.

and here is the actual link: http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?ID=49

Quickly deploy Application solutions in your WSS/MOSS environment

Tired of always being asked to install all those solutions from the Microsoft Download application Templates Pack onto your WSS or MOSS box?

Here's how I do it:

First off all, if you have not yet installed the application core, you need to deploy this one first. For this I have a batch file with the following instructions

stsadm -o addsolution -filename ApplicationTemplateCore.wsp
stsadm -o deploysolution -name ApplicationTemplateCore.wsp -allowgacdeployment -immediate
stsadm -o copyappbincontent 

For deploying all the solutions quickly, I use 2 batch files:

• Deploysolution.bat

stsadm -o addsolution -filename %1
stsadm -o deploysolution -name %1 -allowgacdeployment -immediate

• Deploy.bat

for /F %%i in ('dir /b *.wsp') do deploysolution.bat %%i

Now put these batch files in the folder containing all your solution files (.wsp) files and run deploy.bat. That's it

Moving site collections between Content databases with a single command

When I first heard about it, I had to test the simplicity to do this with the new stsadm option that was inserted in the latest security hotfix for WSS of October 9th. This command is not very well documented anywhere. I herad about it by Todd Klindt in one of his TechEd sessions last week and tested it today. I must say it works like a charm.

This is the command: 

stsadm.exe -o mergecontentdbs
           -url <url>
           -sourcedatabasename <source database name>
           -destinationdatabasename <destination datbabase name>
           [-operation <1-3>
                1 - Analyze (default)
                2 - Full Database Merge
                3 - Read from file]
           [-filename <file generated from stsadm -o enumsites>]

See also:
     stsadm -o enumcontentdbs -url <url>
     stsadm -o enumsites -url <url> -databasename <database>

The way this is to be used as suggested by Todd is, to use a file with the site collections you want to move. As you can see above this file can be generated by the stsadm -o enumsites command. All you need to do then, is open up the file and remove the lines with the site collections you do not want to move. Save the rest and feed it to the new command.

e.g. C:\stsadm -o mergecontentdbs -url http://wss -sourcedatabasename WSS_Content_7fc30dd3c1514cdda6b68bd8d10a0463 -destinationdatabasename WSS_Content -operation 3 -ilename sites.txt

So, I hope you enjoy this cool new command and use it !

Update 20 Nov '07: Todd has finally found the time to publish his article about this as he promised at TechEd. Go check it out @ http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?ID=53

MOSS Capacity planning -> some usefull tips

These bits and pieces come right out of the OFF305_Capacity and Performance Planning for Microsoft SharePoint Products and Technologies 2007 session at TechEd.

slides are attached. 

• Farms: do not split up WebFE servers across WAN's. Keep all servers part of the same farm together
• concurrency planning: on average take about 10% of your total number of users for calculating the concurrency planning. However, plan for peak concurrency as well, which is usually about 5 times the normal usage
• 64-bit Hardware:
• MOSS 2007 is tha last 32-bit SharePoint product. So if you are planning you renvironment, choose to install the 64-bit version now
• 64-bit Hardware prioritization: SQL -> Index -> Excel -> Search -> WebFE
• you cannot mix 64-bit / 32-bit Hardware in the same role.
• Typical HW/role scenario:  (will support easily 25K users)
• 2 WebFE servers with Query server role
• 1 application server with index role
• 1 clustered SQL Server (2 HW server)
• Network: always use Gbit NIC's between the MOSS servers !
• Disk Space:
• plan 1.2 to 1.5 times the File System size for your SQL database size for files that you plan to store in MOSS
• index: ~ 10% of total content size
• query: 2 times the index size
• Performance testing: Use Visual Studio Team System Test Edition (VSTT)
• use BLOB CACHING and OUTPUT CACHING

Become a MOSS Guru

another one of the great sessions at TechEd presented by Joel Oleson, Shane Young and Todd Klindt.

Again, the slides are attached to this post.

What I remembered of this session and what you also should remember:

1. Installation: never, never, never, really never choose Basic installation. Go for Advanced and select complete installation.
2. Think about SQL mirroring.
• high performance (great for geographically separated SQL boxes over a WAN)
• high availibility: replication between 2 SQL boxes and 1 witness server ( -> this can be a free SQL Express box!)
3. Find the grade of customisation to your environment by looking at the foreign DLL file references in the web.config file
4. App Pool Settings:
• increase the startup limit
• disable auto rapid fail.

Administering Microsoft SharePoint Products and Technologies 2007 Using STSADM

Finally, the last session of the day with Todd Klindt showing his magic with stsadm, the command-line administration tool for SharePoint.

The slides?  You guessed it, attached to this post as usual

My notes during the session:

• Template picker: ever wanted to have your users select the website template themselves the first time they connect to the site collection? Well you can. All you need to do is create the site collection using stsadm without specifying a template. This way the first time you connect to the site collection you will get the same dialog you get for creating a new site, allowing you to specify a site template.
• Site Delete Capture tool: captures sites to disk before they are deleted from the Content Database
• Always restore sites into a empty site. The restore operation does not overwrite content already present in the site, causing duplicate entries, etc.
• when exporting sites, the files are actually CAB files. So, rename them to cab files and you can simply double-click the file to open it and view / export data directly

And now for Todd's famous 'Grand Finale': move a site collection from one contentdb to another contentdb in a single command:

stsadm -o mergecontentdbs  -> this new command was snuk in in one of the last hotfixes by Microsoft and allows you move a site collection to a different contentdb.

Explore it and test - test  - test !

There is another great article out there: powerful command line administration for SharePoint @ http://www.microsoft.com/technet/technetmag/issues/2007/01/CommandPrompt/

HP ProLiant Sizer for Microsoft Office SharePoint Server 2007

HP has made a tool available to do some sizing for a MOSS environment. I have seen this tool in action on TechEd and I must say that it is a pretty neat piece of software. It asks you some questions about your environment (number of users, expected load, do you want high availability, etc.) and it comes up with a MOSS configuration that can be saved and updated later on. In addtition to that, being a HP tool, it gives you an overview of the necessary hardware you will need with current HP prices. This way you can easily show any manager what their requested configuration will cost very quickly, when they come to you with their big dreams of SharePoint, slamming them right back to reality .

HP's migration to MOSS

It's always good to learn from others and that's exactly what I was thinking when attending this TechEd session. The reason I am posting it on my blog is that the slides of this session show the migration cycle and the different steps that HP took. This information can help you if you are facing a migration towards MOSS in the near future.

This session also mentionned a sizing tool for Moss, which I will put on a separate blog post.

So, download the slides and review them. They are attached to this post 

How can I make my web site faster with caching ?

I was told in one of the session at TechEd that one of the most important things to configure in MOSS is enabling the output blob cache and set it to 24 hours. Not exactly knowing what the sepaker was referring to, I went looking for this and came across a post on th Microsoft Enterprise Content Management Team Blog.

This article goes through the various caching setting you can enable and configure to improve performance.

Go read it at http://blogs.msdn.com/ecm/archive/2006/11/08/how-to-make-your-moss-2007-web-site-faster-with-caching.aspx

Simple SharePoint Warm Up Script (by Kirk Hofer)

This handy litlle powershell script has been grabbed from Kirk Hofer's blog @ http://blogs.inetium.com/blogs/khofer/

############################################################################
#Assumptions:
#-Running on machine with WSS/MOSS
#-C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN in path
############################################################################

$output=stsadm -o enumzoneurls
$x=[xml]$output
$x.ZoneUrls.Collection|
foreach-object -process {$y=stsadm -o enumsites -url $_.Default;$sites=[xml]$y;$sites.Sites.Site}|
foreach-object -process {write-host $_.Url; $html=[System.Net.WebRequest]::Create($_.Url);}