How to move Microsoft SharePoint databases from one SQL server to another

I have been using Ahmed Ammar's excellent instructions for moving MOSS databases. However, I noticed I could only find them in the Google cache, so I have taken the liberty of repeating them in total here, with a couple of notes from me. Ahmed, let me know if you want me to take this down, but folks, in the interests of preserving a very useful resource, here are Ahmed's instructions.

 

Step 1 – Move
SharePoint_AdminContent

Step 2 – Move Sharepoint_Config database

Step 3 – Move all other site collection databases

Step 4 – Restore Indexers and SSP (Shared Service Provider)

Step 5 – Correct configuration database.

Shortcuts:

SSP: SharePoint Service Provider
CA: Central Administration
RDP: Remote Desktop
MOSSSRV: Web Front server hosting SharePoint central administration and web service.
OLDSQL: SQL server you want to move DBs from.
NEWSQL: SQL server you want to move DBs to.

Prerequisite:

Write down version of your SharePoint farm.

New SQL server needs to have mixed security mode set and TCPIP access enabled.

Local SQL user is required for DB access and creation, together with domain search account, that will perform crawling.

Topology

MOSS 2007 SP2 – ver. 12.0.0.6421 with two servers in a farm.  (Win2k3 and W2k8 32 bit systems)

One web front end hosting CA and second SQL Server 2005 Standard Edition.

Step 1 – Move Sharepoint_AdminContent database

Central Administration website is stored in  Sharepoint_AdminContent
Database, thus it cannot be moved from administration interface, however you can see it there under its web application.

0, Login to OLDSQL and make backups of all databases related to SharePoint farm to shared folder.

1, Put DB offline using CA - Application Management – Content Databases – select proper web application "SharePoint Central Administration V3" and change database status offline.

DO NOT CHECK REMOVE!!!

2, Login to MOSSSRV server using RDP

(I recommend you to create BAT files with commands, in case you mistype character and close CMD.EXE window by mistake.)

Launch prepare to move command from CMD.EXE or BAT file:

STSADM –o preparetomove –contentdb OLDMOSS:SharePoint_AdminConfig_0245-87547-a547f" –site http://mosssrv:4235

 After Operation completed successfully, disconnect DB using:

STSADM –o deletecontentdb –url "http://mosssrv:4235" –databasename "SharePoint_AdminConfig_0245-5698-485-asd21"

3, Switch to OLDSQL server and in SQL Management studio backup SharePoint_AdminContent_2254-sdsa454-45454
database.

After backup, copy this DB to NEWSQL and restore database. Now it's the best time to rename database just to "SharePoint_AdminContent" and get rid of GUID.

4, Go back to MOSSSRV and launch attach DB command :

STSADM –o addcontentdb –url http://mosssrv:4235 –databasename "SharePoint_AdminConfig" -databaseserver "NEWSQL"

5, After operation completed appear, launch command iisreset /noforce.

6, Check http://mosssrv:4235 – e.g. CA website if it is functioning.

 Now you moved Sharepoint_AdminContent
database and renamed it to more understandable name.

Step 2 - Move Sharepoint_Config database

Most difficult part from SharePoint structure view is moving Sharepoint_Content database. This is the first database created and holds settings of your Sharepoint structures.

1,Go to OLDSQL and backup Sharepoint_Content database with suffix "before_move" for example "SharePoint_Config_before_move.bak"

2, Go to MOSSSRV and launch command to disconnect database from server  :

Psconfig.exe –cmd configdb –disconnect

3, Go to OLDSQL server and in SQL Management studio backup SharePoint_Config
database.

After backup, copy this DB to NEWSQL and restore database.

4, Connect database back to sharepoint farm using :

psconfig.exe -cmd configdb  -connect -server "newsql" -database "SharePoint_Config" -dbuser "mossdbcreate" -dbpassword "Aaa123456" -user "DOMAIN\USER" -password "user_password"

Notes:

·         mossdbcreate user is local NEWSQL user with permission to create DB.

·         DOMAIN\USER is user that was set as Sharepoint Administrator. ( I used domain admin account for all these operations.)

·         Your new "SharePoint_AdminContent" database will be used automatically.

Naaman's Life Note:

This didn't work for me, so in the end I ran the following command, which worked:

psconfig.exe -cmd configdb  -connect -server "newsql" -database "SharePoint_Config" -user "DOMAIN\USER" -password "user_password"

5, Launch Sharepoint Technology wizard and provision new CA :

·         Choose "No do not disconnect" and click NEXT,

·         Change the port to the same you had before e.g.  4235 in our case !! and leave NTLM and click NEXT

·         Wizard will now display summary page (already filled with NEWSQL name) and will start creating CA website.After finish it will open you CA website.

Browse around to check everything is functioning…try SSP Admin page for example.

Now we almost finished move of Sharepoin_Config DB, but this step needs to be finished as described in last step. ( You may notice, that in Operations – Server in Farm is written OLDSQL as home of configuration database.)

  
 

Step 3 - Move all SharePoint Site Collection Content Databases

Now move all production "normal" databases.

1, restore your production DBs from step 0 to NEWSQL or backup them all again at OLDSQL and restore in NEWSQL.

2, Go to CA – Application Management – Content DB

3, Change the web application from the drop down list appropriately.

4, open content DB, write down its name, select "offline" and check "Remove Content Database". (If database was created using special account use that account to delete it).

5, After removal, click "Add Content Database" and add the same database but from NEWSQL.

  
 

Repeat these steps for all your production databases.

Note: Some sources are recommending to use chain : preparetoremove > disconnect > connect (same as in Sharepoint_AdminContent) to move all content databases. This is probably needed, if you are moving databases to different farm environment and not to the same Sharepoint_Config database. It has something to do with site and DB GUID.

  
 

Step 4 – Restore SSP

Shared Service Provider – is a feature of Sharepoint Farm and you will not found it by WSS. As name is saying it goes about services, that could be shared in a farm. For example crawling (or indexing or searching) could be configured here and one server can perform crawling and its searchDB could be then used be then used by other servers in a farm.

1, Go to OLDSQL and backup SharedServiceProved_DB (in fact I do not know default name, but there are two databases for SSP – one is content and second is search.)

Then go to NEWSQL and restore this DB. There is no reason to restore search DB, because it will be created anyway.

2, Open CA -  In order to restore SSP from a database you need to enable indexers. This is done in Operations – Servers on Farm – Office SharePoint Server Search.

You need to provide content access account – see prerequisites. You can then leave default name of search DB – WSS_SEARCH_MOSSSRV.

3, In the same menu enable also second Office search service.

4, Go back to SSP Administration page – now choose "Restore SSP"

     Fill the form with existing DB name you restored to NEWSQL and click OK.

Note : Restoration and provisioning process of new SSP could take long time.

You can also get into trouble here, especially if SharePoint's versions are different. You need to be sure, you are restoring SSP from same version of SharePoint system.

5, After successful finish you should see two providers. Now, by clicking "Change associations" change association of old SSP's web applications to new one.

6, By clicking on "Change Default SSP" you change default SSP to restored one.

7, Delete old SSP with option "and delete the associated databases". After clicking OK, un-provisioning of old SSP will occur and it takes a lot of time. May be you will need to restart IIS with /noforce if system will stop responding.

Now you are done with moving all the databases. Before continuing, please double-check all content databases are transferred. You can stop SQL Server service on OLDSQL and check all sites and CA.

 
 

Step 5 – Correct configuration database

As you may noticed, no one told Sharepoint_Config, that configuration database is not on old server, but someplace else. In fact this should be done by disconnecting and connecting operations done by SharePoint Wizard, but system writes, that config DB is still on OLDSQL in CA.

Fact is, system is now using NEWSQL for SharepointConfig, but some sub-systems may not work, because somewhere is still written OLDSQL.

In my case, creating site collection from stsadm in new contentdb was issuing error, that DB is not accessible. Unfortunately, system did not write which DB, so I naturally thought it was NEWSQL, that is not accessible and I have bad credentials, but content DB was created, so what was wrong??

I then started OLDSQL resp. SQL service there is tried again.

So I looked at NEWSQL – directly into SharePoint_Config database and find out you have to change Name in first row in dbo.Objects table, where OLDSQL still resides.

I did it using "New Query" button in SQL Management studio and executing this SQL command.

update dbo.Objects set Name = replace(Name, 'oldsql','newsql.ccc.com')

Note : You cannot use newsql name only, because then system reports an error. My workaround was to use FQDN e.g. with dns name like MS's favorite contoso.local  - it is unique in table, and still valid for the system. Probably you can create DNS A record and use this one instead. It will anyway point to same location.

Sharepoint Migration

Currently working on migrating the databases for a MOSS 2007 install from one SQL Server to another. To be more precise, I am moving the database from one instance on one node of a clustered SQL Server to the clustered instance of the SQL Server itself. Anyway, have been working though Ammed Ammar's excellent instructions (I found them here). Had moved my Admin_Content db, and was in the process of moving the Config db when I hit a roadblock. Was using the psconfig.exe command to reconnect to my now migrated Config database and kept on getting the dreaded "The server parameter specified with the configdb command is invalid. Failed to connect to the database server or the database name does not exist. Ensure the database server exists, is a Sql server, and that you have the appropriate permissions to access the database server. To diagnose the problem, review the extended error information located at C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS\PSCDiagnostics_9_24_2009_11_5_7_53_419108824.log. Please consult the SharePoint Products and Technologies Configuration Wizard help for additional information regarding database server security configuration and network access." error ...

Couldn't work out the appropriate username and password to connect to the database for this command ... I finally found a solution by fluke. Ammed suggest the following command to reconnect the config database:

psconfig.exe -cmd configdb -connect -server "newsql" -database "SharePoint_Config" -dbuser "mossdbcreate" -dbpassword "Aaa123456" -user "DOMAIN\USER" -password "user_password"

If that doesn't work, try running the cmd exe from your mossdbcreate account using "runas /user:Doman\mossdbcreate /noprofile cmd.exe" and then use the following simplification of the psconfig command:

psconfig.exe -cmd configdb -connect -server "newsql" -database "SharePoint_Config" -user "DOMAIN\USER" -password "user_password"

Worked for me, anyway. Let me know if it helps you out.

Monopoly City Streets Housing

Just done some analysis on the rates of return from houses in the new Monopoly City Streets. It seems to me that the City Centre Cottage is the best building to build if you are not running out of space.

Building	Price (1000s)	Return (1000s)	Rate of Return
Green House	50	9	18.000%
City Centre Cottage	75	14	18.667%
Cane Top Multiplex	150	23	15.333%
The Sentinel	175	28	16.000%
High Reach Place	200	33	16.500%
Nova Tower Block	300	51	17.000%
PolyHedron Plaza	400	66	16.500%
The Grid Building	500	85	17.000%
Four Sided Fortress	600	95	15.833%
Nori Place	750	104	13.867%
Honeycomb Complex	900	123	13.667%
Blanco Bastion	1100	142	12.909%
The Photat Building	1500	180	12.000%
Cubix Quarters	2000	180	9.000%
Opaque Overlook	2800	209	7.464%
Tri-Rectangle Tower	3900	228	5.846%
Spear End Summit	5000	247	4.940%
Unbounded Megaplex	6000	266	4.433%

Update: OK, just made the discovery that the amount you make for properties has an effect on the return. It appears that the higher the value of the street the greater the return on the building. So rental returns will depend on having high quality streets. Even so, the City Centre Cottage still remains the building with the best return. I am making 60% on mine!

Sharepoint Regional Settings or the Saga of the Non US Date Format

Actually, this is a rather short saga. Today I've been struggling with the fact that WSS 3.0 (this post does not apply to MOSS), by default uses American date formats (MM.dd.YYYY), and that there seemed to be no way to change this. However, I just happened to be wandering through the LAYOUTS folder in the 12 hive (don't ask me what I was doing there) and found a file called regionalsetng.aspx. Turns out, if you open your Central Administration site and alter the path to point to this file (eg http://myserver:12345/_layouts/regionalsetng.aspx), bingo, you get Regional Settings. It is rather bizarre that there appears to be no link to this page from anywhere. Kind of like the mysterious 13 floor on lots of buildings. Or not.

Update: Not sure why this happens, but a regional change made at the top of a Site Collection will not necessarily populate right through the sites below. Sometimes each site must be opened and the regional settings changed there.

Site Collections in MOSS 2007

Been setting up MOSS lately, primarily to serve as a file system replacement. Due to the large volume of files going in, we felt that it just might be a good idea to break the volume of files up into separate site collections. One of the big advantages of creating a separate site collection for each block of files or part of the business is that each site collection, unlike a site, can have its own database on SQL. Site collections can also be recycled and restored independently of each other. The way to tell MOSS to create a site collection with its own database is to use STADM and use a command similar to the following:

stsadm.exe -o createsiteinnewdb -url "http://myMOSSsite/sitecollectionname" -owneremail "owners.email@mycompany.com" -ownerlogin "mydomain\my.superuserlogin" -sitetemplate "thesitetemplate_I_want_to_use#1" -title "MyCollectionName" -databaseserver "mySQLserver\myMOSSinstance" -databasename "WSS_Content_sitecollectionname"

ASUS U/UX Notebook Series Announced

As some of you may know, I am on the hunt for a new notebook to replace my geriatric Sony Vaio SZ (it's had a hard life). Engadget is reporting that ASUS has announced 2 new notebook series, one of which (the U series) looks pretty good to me. There is little information available on these machines, although from the picture here (note the numberpad) it seems that the UX series will include a 15 inch, and be very sleek and glossy. It also appears that at a minimum it will have:

• Slot loading optical drive (according to Engadget)
• At least 2 USB ports
• HDMI port
• Network port
• Headphone and line in jacks
• Backlit chicklet keyboard with numberpad
• Ambient light sensor
• Altec Lansing speakers with SRS true surround sound

The U series on the other hand looks like having the following specs:

• A 12 inch (U20), a 14 inch (U80) and a 15 inch (U50) options to be available
• Backlit chicklet keyboard
• Normal tray loading optical drive
• Inbuilt webcam
• Ambient light sensor

Updated: The 12 inch U20 (pictured here), will include:

• Intel Core 2 Solo SU3300
• Mobile Intel GS45 Chipset
• Up to 4Gb RAM
• 2.5 inch HDD up to 500Gb
• DVD Super Multi Drive
• Built in 1.2 M web cam
• Less than 2 kg

The 15 inch U50 will, according to Engadget include:

• An Intel Core 2 Duo processor
• NVIDIA GeForce G105M
• Up to 500GB HDD
• Altec Lansing speakers with SRS true surround sound

Both series include what is described as "a frame inspired by the curvature of a butterfly's wings", which sounds pretty lame, but looks like this! So far so good. Let's hope they come up with goods ... 12 inch, internal optical drive, GPU and less than 1.8kg.

Update - Late April: Looks like these may, emphasize, may have been released somewhere. AVING is reporting that the ASUS U/UX Series have been released in Korea. Meanwhile, NordicHardware thinks the U series, the UZ series and the Edge series will be "presented" (whatever that means) at Computex in July. NordicHardware also suggests that the UX series are high end notebooks, while the U will be midrange. Too little information, too slowly,

Update 2 - May 4: ASUS in the Philippines has apparently released a news update on the U Series, with a very entertaining photo. I say apparently because Hardware Zone sites the Philippino Asus site, but I can't find the information on it.

Update 3 - May 5: Sigh! Doesn't look like either the U Series or the UX Series will make the grade. ASUS have just released specs and those that are light enough to meet my criteria (sub 2Kg) lack a discreet GPU. What do we need to do to get a non-Sony, GPU and optical drive equipped sub 2Kg notebook?

Two Big Lies

Back in 2006, I posted about the Anglican church selling out on evolution. Today, the SMH reports the Catholic church has done likewise. Two big lies really.

Firstly, that evolution is compatible with the bible ... despite Jesus apparently endorsing Moses' literal view of creation.

Secondly, that the church cares about what the Bible or God think. After all, why concern yourself over God's word when you consider yourself God On Earth (see the start of the 5th paragraph of the Encyclical Letter Præclara Gratulationis Publicæ of Pope Leo XIII).

Groove and Sharepoint Integration in Office 14

I was talking to someone in the know about the development path that Microsoft's Office suite is heading down, and they let drop a very interesting piece of information. Apparently, in Office 14, the Sharepoint client will be Groove. This makes great sense, and means that document storage and collaboration is starting to get similar tools to mail. I envisage a Outlook style interface for documents that allows for seamless collaboration, dissemination and backup.

The World's Best Coffee Makers

OK, so the title is a little misleading. You may have got here thinking you were going to be regaled with tales of daring baristas who snatched victory from the jaws of defeat in the java brewing contests of yore. Sadly, I am going to tell you about what I believe is the best coffee making machine in the world ... and even then I have caveats on that. The best coffee making machine in the world under $1000 dollars.

Cutting to the chase, I believe that the Rancilio Silvia fills the role of world's best coffee maker admirably. The reasons for my bold statement are as follows:

1. It sells for under $1000 dollars ($750 Aussie dollars in approx in these parts)
   
2. Despite the price, it will give a shot almost as good as a full commercial unit worth 5 times the price
3. It's not one of these automated, all bells and whistles type machines, that flawlessly grinds, magically steams and produces terrible coffee - The coffee that comes out is totally up to the skill of the person driving
4. It's a coffee machine that really teaches you how to make coffee. Refer Point 3
   
5. It's a rubust little thing. I've had mine for over 3 years, and it just had it's first service today ... and all it required was a washer replaced and a descale. To quote CoffeeGeek, it's robust and built like a tank
   
6. You can make steamed milk properly with it. None of this frustrating nerk-nerk-nerk of the nasty little units on most department store shelves. This thing really gets the milk spinning
7. Point 6 is possible because unlike most cheaper machines, the Rancilio has a little boiler that it uses for heating the water and producing the steam, not a thermablock
8. Despite all that has been mentioned above, it still looks pretty good in a square kind of silvery way
   

If you want some more information, Greg Pullman of Pullman's Tampers fame, reviews the Rancilio Silvia here and compares it with the top of the range Sunbeam. Worth a read. If you've got the time, read my post on steaming milk (with a Rancilio of course), and an old Boing Boing post on Miss Silvia.

The (Internet) Toast of Town

Those of you who have such empty and vapid lives that you have read some of my older posts will realise that I have an unhealthy interest in high tech toasters and in particular, "the internet toaster". In fact I have been posting on this very subject since 2005. So of course, my interest was piqued by a post on the appropriately named (for this topic) CrunchGear and other places about a toaster design concept that can print toast whatever you want on your thick, crisp and heavily buttered. I say make it happen, Electrolux. But make it wireless with a app that allows you to pick an RSS feed or weather report or something similar to be printed on said crisp and warmed.

Other posts on this stomach warming subject can be found here and here.

Incidentally, Hobbes Internet Timeline refers to an Internet enabled toaster as far back as 1990.

New Largest House in the World

Back in March 08, I addressed the subject of the World's Largest House. Bizarrely, this post became the most popular on my blog, and has stayed in the top 3 most frequently hit pages every day since then. At the time, I noted that the Biltmore House built by the Vanderbilt family was most likely the largest private home in the world.

However, it appears that an India business man has taken umbrage at this post and decided to claim the crown of the owner of the world's biggest home. He is none other than Mukesh Ambani, and he is building a 400,000 square foot home in Mumbai, India. Now, I did a quick conversion and that works out at over 37,000 square metres of home sweet home (conversion tool online here).

Apparently, this is to be spread across 27 stories and will include 3 helicopter pads, the hanging gardens of Mumbai, a yoga studio and a private ice palace. It will also have a staff of over 600. Mukesh Ambani is listed on Wikipedia as being the world's 5th richest man, and he made his money (US$43 billion and counting) in petro-chemicals, according to Forbes.

So there you have it. The new biggest house in the world. Videos of the biggest house here.

Steaming Trick for Awesome Milk

I haven't written many posts on coffee or recently, so thought I would rectify that with this brief post. Given I am still developing my "milk steaming skillz" (I'm a level 6 Milk Steamer with a +3 Jug of Steaming ;), I need all the help I can to get that velvety smooth texture that one wants on ones milk. One snag I often find is that I am still mucking about trying to stretch and texture the milk by the point it gets hot, and figuring that burnt milk is worse than poorly textured milk, I usually give up at that point. I know I have already mentioned this, but its worth mentioning again ... to give myself a bit more time, not only do I make sure that the milk is cold to start with, but I keep the jug in the freezer till needed. I reckon that using a frozen jug gives me an extra 5 seconds or so texturing the milk. Give it a try and let me know if it helps.

Sharepoint Workflow to Make Items Context Aware

One of the things I have wanted since I first started working with Sharepoint 2007 is to have the documents and items in Sharepoint/MOSS be context aware. What I mean by that (and people may understand different things by "context aware") is that when an item is created or moved, the item itself knows where it now is. I want a custom column or metadata element that is automagically updated to reflect the context. For me, this mostly means having a custom column that keeps up to date with the folder a document or item is in.

I did the normal stuff people do to get this happening. I mucked around for ages with Microsoft Office Sharepoint Designer (MOSD), trying to get the workflow wizard to build a workflow to do this. No such luck. In the end I created a custom workflow in Visual Studio 2005 to do this for me, and thought I would share the code with my vast readership (yes, you know who you are, all 3 of you). This code may seem a bit cludgey to some of you. If you can see a better way to do this, let me know. Pre-empting one comment though, every time I tried to reference WorkflowProperties.Item.File.ParentFolder, or any variation on this, VS kindly told me that this was null. I suspect that if I try this for a document it will work, but as I use this code to make tasks context aware, there is no file object?

This code is particularly useful, because if you copule this with my previous Sharepoint related post about displaying Sharepoint custom columns in Outlook, it means that you can show in Outlook the Sharepoint context of a task ... Handy!

private void logWorkflowStarted_MethodInvoking(object sender, EventArgs e)
{

SPSecurity.RunWithElevatedPrivileges(delegate()
{
//Default value
FolderName = "Unknown";
//Get the current item
SPListItem listItem = WorkflowProperties.Item;
//Get web reference
SPWeb web = listItem.Web;
//Get list reference
SPList list = web.Lists[listItem.ParentList.ID];
//Get URL for item
string FullURL = web.Url + listItem.Url;
//Get last slash
int index = FullURL.LastIndexOf("/");
//Set parent folder URL
string parentFolderUrl = string.Empty;
//If slash found
if (index > -1)
{
//Get url of parent
parentFolderUrl = FullURL.Substring(0, index);
//Get folder
SPFolder folder = list.RootFolder.SubFolders[parentFolderUrl];

if (folder.Exists)
{
FolderName = folder.Name;
}
}


//Update folder name
WorkflowProperties.Item["Project"] = FolderName;
//Update document meta data
try
{
WorkflowProperties.Item.SystemUpdate();
}
catch (SPException ex)
{
throw ex;
}

});
}

Display Sharepoint Custom Columns in Outlook

This little problem has been troubling me for a few days and it appears I am not alone in this. I have a number of Project Task folders, each with very similar or identical tasks within them. For example, multiple folders may have the task "Update spec", assigned to the same person. So when these tasks are synched to Outlook 2007 using the "Connect to Outlook" function in Sharepoint, these identical tasks become indistinguishable. The only way around this appeared to be to expose a custom column that gave the task some context. However, Sharepoint custom columns cannot, it appears be exposed to Outlook, as they do not match the default schema for the object in Outlook (see here for Microsoft's lame explanation).

Found a way around this in the end. This solution has now been in testing for over 5 minutes and so I can conclude it is rock solid (not). Anyway ... Out of the box, Outlook will expose default columns from its own schema. So the answer is to put the context information for the task in a task column in Sharepoint that matches a default one in the Outlook task schema. Pick one that you aren't using for anything else important, perhaps like the Company column. This is called Related Company in Sharepoint. Put the context information in this, and this will be exposed as Company in Outlook. It appears you can then change the name in MOSS and still expose the information in Outlook ... although the name change itself will not be reflected in Outlook. Hope that helps.

More Sharepoint Struggles

Struggling with some other Sharepoint stuff at the moment. Here are a few MOSS issues that I want to answer. Drop me a note if you have the answer or find a post that does.

1. How to expose custom columns from Sharepoint in Outlook 2007?
2. Why do some folders from a synchronised Sharepoint document repository get duplicated in Outlook?
3. How can I add a dynamic title for a custom action in the drop down for an item? (I want to be able to progress a task's status from "In Progress" to "Complete" and so on, from the right click menu on the task's drop down menu, in Sharepoint. This means I have to be able to change the title in the drop down menu after the user clicks.)

No answers in sight for these questions so far but I am guessing that somewhere out there in google-space, someone has the answer.

iPhone 3G and Microsoft Office Documents

OK, so lots of hype and excitement out on the Interwebs today, as Steve Jobs unveils the 3G iPhone. For people living in this Great Southern Land (Australia) such as myself, this is a slightly big deal in a geeky kind of way, as iPhones have not been available here up till now. OK, so yada yada and lots of blog posts and technorista foaming at the mouth and so on.

The iPhone looks blandly nice (or maybe it was having an "average hair day"), black for the 8Gb model and a choice of white or black for the 16Gb model. GPS now built in as predicted but other that and the whole 3G thing, not really very different from the original. Yawn.

The only thing I want to know is whether it opens Office documents nicely. I want to be able to, at the least, view Word, Excel and Powerpoint docs. Ideally, I want to be able to edit them. I don't want to have to be web connected to do so. So far, no one seems to have any clue as to how well it will meet this need. For me, this is the key thing that would turn the iPhone from a phone with an unique interface, but little else to recommend it, into a business capable smart phone.

So if you find and answer to the question, drop me a comment.

Update: Found references to "Office support" here and here. But what does that actually mean? Opens documents or does it "Google style"?

Update 2: Noticed in the Keynote that Steve clicked on an attachment in an email and opened it. However, this looked to me like an image he was opening and not a document.

Update 3: This article about native Office applications on the iPhone appeared on the webs today (15th of July). Quoting the final paragraph, "Dataviz told us yesterday that unspecified technical issues were holding up the development of office suites for the iPhone. Joswiak said he didn't know of any such hold-ups, but then added that there might be issues with applications each having their own file space. "There's no cross-application file structure," he said."

Migrating data from WSS to SQL Server

I've been struggling with a Sharepoint related problem today. I've built up a Windows Sharepoint Services (WSS) server. By default, WSS uses SQL Server 2005 Embedded Edition to store its data. That's all good, however, when you do what I did you tend to hit a few snags. I installed WSS on a virtual server which is hosted on a physical server. Having used the WSS server for a while now, it has grown and is now the digital equivalent of a real world small elephant. However, the physical server has the digital capacity of a real world ladies hand bag. Problem ... little bits of elephant poking out everywhere. Not the most graceful of analogies, I know, but you get the gist.

So cutting a boring story short, I need to get the data off SQL Server 2005 Embedded Edition, and onto SQL Server NOT Embedded Edition somewhere else other than my virtual server. But for what ever reason, Microsoft have chosen to make it really hard for anything but Sharepoint to talk to SQL Server 2005 Embedded Edition. or SSEE. Imagine if you will, an INVISIBLE elephant in a ladies handbag. Weird.

SQL Server 2005 Embedded Edition goes by the name of MICROSOFT##SSEE. I installed SQL Server Management Studio Express to see if it would help but kept getting the following error:

Error: Cannot Connect to Server\MICROSOFT##SSEE.

An error occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings, SQL Server does not allow remote connections.

However, after much Googling, I discovered some genius has come up with a way to connect to this database. Simply use SQL Server Management Studio Express but call the database server name "\\.\pipe\mssql$microsoft##ssee\sql\query" when connecting. Work's like a charm.

Update: Some information on next steps from here can be found in this blog post on migrating WSS to SQL.

The City Most Distant from Snow in the World

Now how's that for a tortured title!

Anyway .... as some of you know, I like to think of myself as a snowboarder. In my case, this means I might, if I am very lucky, get to the snow for 5 days once a year. A long time ago, I purchased a snowboard (and posted about that snowboard) for my occasional snow trips, and although it has been used a few times, it now reclines lazily on a shelf in my laundry, doing very little to earn its keep.

It occurred to me the other day while meditating on the laziness of my snowboard, that my snowboard, which currently lives in Perth, Western Australia, could well be the snowboard most distant from snow, anywhere in the world. So I tried to work out where was the most snow deprived place on earth.

As it turns out, this is not as easy as it sounds. I am around 2900km from the nearest snow (the Alpine National Park in Victoria, Australia). So I was trying to find a location more than 2900km from snow.

Anywhere in Europe is in spitting distance from snow, so I've ignored Europe in this post. Africa, one would think, is pretty snow deprived, but using the National Geographic's very cool Map Machine to work out where snow fell on an average year, I worked out that there is nowhere in Africa more distant from snow than I am. This is largely due to small area of snow fall in Kenya, Morocco and South Africa that mean that all of Africa is close to snow than me.

Some spots in Asia would seem a bit snowless, but the side is let down by Vietnam with it's occasional snowfall, Myanmar's Himalaya, and Indonesia's glaciers.

At the end of the day, the only spots I could work out that would be further from snow might be some areas in Brazil on the Eastern coast and maybe somewhere like the Cook Islands in the middle of the Pacific. Even Fiji is nearer to NZ's snow fields than I am to Australias. So there it is. Unless someone in Brazil has a snowboard in their shed, my snowboard in Perth is the world's most distant from snow. Now there's a sorry distinction.

Note: The final map on this page shows Perth (the blue flag) and range circles of 2900km around areas of snow fall in red. If a city falls inside a red circle then it is less than 2900km from snow and therefore closer to snow than Perth. Just bear in mind that from Turkey to China is range of mountains continually capped in snow, most of Europe has snow fall and nowhere in the US is more than 2000km from snow.

Attribution: I used Free Map tools with Google Maps and the National Geographic's Map Machine to decide if my snowboard was the most snow deprived.

Problems Uploading Documents with Workflow in WSS

I've been working on my first Sharepoint 2007 site. Actually, it's a WSS site as I am not using Sharepoint. The site I have created has very limited functionality, and is intended to allow the company I am working for centrally maintain a register of a certain type of action they are qualified to perform and provide a link to all of the documents related to this action, and WSS is sufficient for this.

So what I have is the Sharepoint equivalent of an Excel spreadsheet (a custom list), which lists the actions and all the associated attributes, and I have a Document Repository, broken into folders. Each document in the Document Repository has two custom attributes - a action type flag and a business unit flag. Each action in the custom list has an action type attribute and a business unit attribute, and when a user selects "Related Documents" from the customised Actions drop down for a list item, Sharepoint does a search in the Document Repository for documents where the business unit and action type flags match the data in the selected action in the custom list.

So far so good. However, to make the experience within Sharepoint similar to what the users are used to and easier, I have made a custom workflow that fires when a new document is created and works out what folder within the Document Repository the new document is being uploaded to. In other words, I am trying to make the document itself explicitly aware of its location, and specifically the folder it is in. Each of the folders in the document repository are named for one of the business units. So the idea is that the document metadata will automatically contain the business unit name that owns the document.

This is where the problem emerges. A user enters the Document Repository, and begins to upload a document. At this point, the workflow fires off. Before the workflow has completed, the screen refreshes to allow the user to edit the metadata for the document, in this case the title, name, action type flag and business unit flag. The metadata displayed is the metadata that the document was uploaded with, but meanwhile, in the background, the workflow has updated the document's business unit flag attribute. The user clicks the check in button, and because the metadata that the document had when the page was rendered and the metadata it has now the workflow is finished is different (irrespective of whether the user entered any changes for these fields), the process errors.

Server Error in '/' Application.
--------------------------------------------------------------------------------

The file Document Repository/BusinessUnit/Document.JPG has been modified by SHAREPOINT\system on 22 May 2008 11:36:50 +0800.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Runtime.InteropServices.COMException: The file Document Repository/BusinessUnit/Document.JPG has been modified by SHAREPOINT\system on 22 May 2008 11:36:50 +0800.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[COMException (0x81020037): The file Document Repository/BusinessUnit/Document.JPG has been modified by SHAREPOINT\system on 22 May 2008 11:36:50 +0800.]
Microsoft.SharePoint.Library.SPRequestInternalClass.AddOrUpdateItem(String bstrUrl, String bstrListName, Boolean bAdd, Boolean bSystemUpdate, Boolean bPreserveItemVersion, Boolean bUpdateNoVersion, Int32& plID, String& pbstrGuid, Guid pbstrNewDocId, Boolean bHasNewDocId, String bstrVersion, Object& pvarAttachmentNames, Object& pvarAttachmentContents, Object& pvarProperties, Boolean bCheckOut, Boolean bCheckin, Boolean bMigration, Boolean bPublish) +0
Microsoft.SharePoint.Library.SPRequest.AddOrUpdateItem(String bstrUrl, String bstrListName, Boolean bAdd, Boolean bSystemUpdate, Boolean bPreserveItemVersion, Boolean bUpdateNoVersion, Int32& plID, String& pbstrGuid, Guid pbstrNewDocId, Boolean bHasNewDocId, String bstrVersion, Object& pvarAttachmentNames, Object& pvarAttachmentContents, Object& pvarProperties, Boolean bCheckOut, Boolean bCheckin, Boolean bMigration, Boolean bPublish) +199

[SPException: The file Document Repository/BusinessUnit/Document.JPG has been modified by SHAREPOINT\system on 22 May 2008 11:36:50 +0800.]
Microsoft.SharePoint.Library.SPRequest.AddOrUpdateItem(String bstrUrl, String bstrListName, Boolean bAdd, Boolean bSystemUpdate, Boolean bPreserveItemVersion, Boolean bUpdateNoVersion, Int32& plID, String& pbstrGuid, Guid pbstrNewDocId, Boolean bHasNewDocId, String bstrVersion, Object& pvarAttachmentNames, Object& pvarAttachmentContents, Object& pvarProperties, Boolean bCheckOut, Boolean bCheckin, Boolean bMigration, Boolean bPublish) +240
Microsoft.SharePoint.SPListItem.AddOrUpdateItem(Boolean bAdd, Boolean bSystem, Boolean bPreserveItemVersion, Boolean bNoVersion, Boolean bMigration, Boolean bPublish, Boolean bCheckOut, Boolean bCheckin, Guid newGuidOnAdd, Int32& ulID, Object& objAttachmentNames, Object& objAttachmentContents, Boolean suppressAfterEvents) +933
Microsoft.SharePoint.SPListItem.UpdateInternal(Boolean bSystem, Boolean bPreserveItemVersion, Guid newGuidOnAdd, Boolean bMigration, Boolean bPublish, Boolean bNoVersion, Boolean bCheckOut, Boolean bCheckin, Boolean suppressAfterEvents) +182
Microsoft.SharePoint.SPListItem.UpdateOverwriteVersion() +88
Microsoft.SharePoint.WebControls.SaveButton.SaveItem(SPContext itemContext, Boolean uploadMode, String checkInComment) +178
Microsoft.SharePoint.WebControls.SaveButton.SaveItem() +58
Microsoft.SharePoint.WebControls.SaveButton.OnBubbleEvent(Object source, EventArgs e) +249
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +177
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746

Not good, huh!

Anyway, it turns out this is a fairly well known problem (even though Googling the error doesn't come up with much). Two good posts that started to lead me in the right direction are here and here. It turns out, that for me, the solution was as easy as making sure I had a mandatory or required field/column with no default value entered for the uploaded documents. The workflow would then have to wait until the user entered the requisite information and checked the document in, before firing. Problem solved.

Cheap Glasses for the Yearning Australian Masses

Me, I am determined to never need glasses, no matter how blind I become. I'm not quite sure how I am going to manage this, but I am confident that sometime soon someone smart will invent a "Driving By Braille" system for people like me.

One recent development that is chipping away at my steely resolve to remain sans spectacles is the growing availability of cheap glasses. More and more sites pop up that allow you to merely enter the details of your prescription and order a fresh yet affordable set of optics to suit your mood at crazy prices.

Actually, a friend of mine has recently started just such a optometrist bankrupting venture, called GetFramed. He is selling prescription specs to the discerning Australian clientele, starting for the frivolous sum of $30, climbing to the dizzying heights of $70 for the "flash ones". I'm not entirely sure how he is managing this (I am visualising hundreds of feverish gnomes hard at work in his shed), but hey, why look a gift horse in the mouth (unless of course, you like me are so blind you thought it was a drinking fountain).

Anyway, if you are in the market for some reasonably priced spectacles, give him a try at www.GetFramed.com.au.

BTW - Weird aside - I've noticed this post is showing up on a site called ikox dot net. Not sure whats happening there but looks like whoever is behind this site is ripping off my post. Weird.