Connect to Microsoft Embedded SQL Server ##SSEE

I have not been working on Sharepoint for a few weeks (spending time doing some C# coding for server monitoring etc) so the joys of Sharepoint have not been in the front of my mind.

Anyway, I had a phone call today from a contract developer who works for the Local Government organization that we work for asking if I could tell him how to connect to the ##SSEE database that the basic install of Sharepoint uses (Yes, this guy is the well paid (I guess) professional Sharepoint Contractor, and he is asking me (a Sharepoint novice) who to do this.

It took me about 30 minutes to figure out that you can connect using Microsoft SQL Server Management Studio by giving the Server Name in the File->Connect Object Explorer->”Connect to Server” as:

\\.\pipe\mssql$microsoft##ssee\sql\query

and, hey presto, connected immediately and I can see all the WSS_Content database and more. Solves his problem and something that I must remember.

Workflow – Linked Table solution

The other day I was struggling to get my Sharepoint Workflow working in the way I wanted. All the examples that I was able to find in books were all simple – one list, perhaps two lists, and a simple lookup. Easy stuff. But my scenario was slightly (I mean only slightly) more complicated – my second list had a reference to itself so needed a second lookup.

Well, Good News!

I read an interesting blog at http://www.sharepointassist.com/2009/05/09/handling-lookup-columns-in-a-sharepoint-designer-workflow/ by Ulysses Ludwig in which he gives an example of doing something similar. In fact, he is not doing the double lookup but his explanation is so clear and concise that it opened the doors of understanding, and I was able to take that and use it to develop my solution.

The key to the solution was in understanding how WSS handles lookups and connections to other lists. The key finding being that the first part of the Define Workflow Lookup (the bit titled “Lookup Details”) tells the system where the data finally comes from, and the second part of the DWL (titled “Find the List Item”) is the bit where you link the two lists together. And you do that by naming the list and the column for both lists.

So in the HolidayRequest list I take the “Name” field and do a lookup on that in the “Staff” list. That gives me the Staff record for the person making the request. In that record in the name of their manager – so I take that and store it in a Workflow Variable (called Variable:ManagerName.

So far so good – but that only gets us the manager name.

The next stage is to use the manager name from the Variable:ManagerName to do a lookup in the Staff list again but this time we get the manager’s Staff record. And, of course, in that record is the email address for that person. I store the email address in another Workflow Variable (called Variable:ManagerEmail) and hey presto! Job done – I now have the original HolidayRequest, and the Manager’s Name, and the Manager’s email address.

So I can now send an email to the manager asking them to authorise, or decline, the Holiday Request.

Magic.

Workflow

Workflow is the method by which a change to an item causes further actions to occur in an ordered and controlled fashion. For example, inserting a new order for a customer might result in an email being sent to the packing department – that kind of thing. Workflows can be simple and involve just one or two steps, or can involve many steps.

I have just been developing a staff holiday booking system to be part of our Sharepoint site. I have two lists (or ‘tables’ as I think of them). One called ‘Staff’ that has fields for Name and Manager – both of which are populated from the Active Directory for that team. The other list is called ‘Holidays’ and has fields for Name, StartDate, EndDate, and Duration (which is calculated). The two lists are joined via the Name fields. The point of all that is to provide the manager name for authorisation purposes – and I don’t want to have to include the manager name in the holiday list when it is already available via the staff list; in other words, I don’t want to duplicate data.

But the problem that I have right now is in creating the workflow email destination address. I have created a workflow that is triggered when a new holiday is created and the first step of the workflow is to send an email to that persons manager. But trying to figure out how to get the manager name within workflow has turned out to be a bigger challenge than I thought it would be. So far I have not been able to get the workflow to lookup the manager name through the joined table. There must be a way to do this.

I will add my various discoveries and failures as comments to this post.

Whats it all about

The first entry is just going to be a quick overview of what I am doing and what I have done.
I have been asked by my manager to create a company intranet. And there are some parameters: the software must be free, it should be flexible and extensible. It should also be easy to use and something that people will want to use.

The last point, of course, is a matter of content. If it provides something that people want to use then they are likely to use it. If it has nothing for them then they won't bother. And who can blame them.

The other parameters can be use to help select a solution. Well, if you read the name of this blog then you already know that the chosen solution is SharePoint from Microsoft.

Why?

• The WSS (Windows SharePoint Services) is FREE. So that ticks the first box.
• It looks to be flexible - new features 'WebParts' can be added.
• It looks to be extensible (see above) but it is also possible to create new parts for it.

The current state of play is that WSS 3.0 is installed in a virtual machine running Windows Server 2003 (the pre-requisite platform for WSS 3.0) and a initial intranet created.

Now my task is to read up, experiment, and generally 'play around' with it so that I learn all the in's, out's and odd things.