Updating Extended Properties of a Database using SQL Server SMO

June 23rd, 2010 Merill Fernando View Comments

Updating the extended properties on a database using SQL Server’s excellent Server Management Objects API is not as straightforward as setting the value and calling update.

The database.Alter() method needs to be called both before and after updating the value. I had to lookup the code of El Pluto‘s awesome SQL Server Extended Properties Quick Editor project on CodePlex to figure this out.

using Microsoft.SqlServer.Management.Smo;
 
/// <summary>
/// Set's the extended property of a database.
/// </summary>
/// <param name="serverName">The name of the SQL Server.</param>
/// <param name="databaseName">The name of the database.</param>
/// <param name="propertyName">The name of the extended property.</param>
/// <param name="value">The value of the extended property.</param>
private void SetExtendedProperty(string serverName, string 
    databaseName, string propertyName, string value)
{
    var server = new Server(serverName);
    var database = server.Databases[databaseName];
 
    database.Alter();
    if (!database.ExtendedProperties.Contains(propertyName))
    {
        database.ExtendedProperties.Add(
            new ExtendedProperty(database, propertyName, value));
    }
    else
    {
        database.ExtendedProperties[propertyName].Value = value;
    }
    database.Alter();
}
 
Categories: .NET Tags: ,

Dynamically setting multiple activity destinations in K2 with ASP .NET

June 17th, 2010 Merill Fernando View Comments

When building a typical workflow you usually know which user or group needs to perform an activity at design time. Sometimes though the workflow needs to be more dynamic.

The issue I had to resolve recently involved having to build a workflow where the end-user gets to individually pick the users who will be performing the next step. Here’s a view of  the workflow design.

The scenario involved an application being submitted for review. The application would go to an individual who is responsible for assigning a group of users (destination users) to review the application. The twist was that it was the individual picking users for each application, it wasn’t a fixed group or role. The screen mockup shows how they do it.

When the person hits the ‘Assign Reviewers’ button the form then needs to turn up as a work list item for each of the reviewers (destination users) who get to review the application in parallel.

Implementing this process using K2/InfoPath is quite straightforward and is well documented in many places including this post titled ‘Activity Destination Users based upon a Repeating XML element‘ in a K2 underground blog.

It’s not well documented though for ASP.NET. The post ‘How To: Use a web service for destinations in K2 blackpoint‘ is close to what we want but it’s targeted at using a web service.

K2 let’s you set multiple destination users in one of two ways

1. Using a SmartObject method in a role

2. Using Xml as a destination set

Going the SmartObject route was a lot of work for my simple requirement so I chose the Xml method. The idea here is to use the list of users in the Assign Reviewers form and store them in a Process xml field. The destination set will then be configured to read the xml field and create a slot for each user.

FYI: See page 4 of the Advanced Destinations whitepaper for a description of the two roles. <rant>Why the K2 KB portal needs a login is beyond me.</rant>

Step 1: Write the code to create the xml containing the list of users

The code block below accepts a ; delimited list of domain name\username (e.g. domain\johna) and creates an XML document containing the list of users. This is then assigned to the Process Instance XML field called Reviewers.

private static void AssignReviewers(WorklistItem item, string listOfUsers)
{
    var doc = new XmlDocument();
    var root = doc.CreateElement("UserList");
    doc.AppendChild(root);

    foreach (string user in listOfUsers.Split(';'))
    {
        var userNode = doc.CreateElement("Users");
        userNode.InnerText = user;
        root.AppendChild(userNode);
    }
    item.ProcessInstance.XmlFields["Reviewers"].Value = doc.OuterXml;
}

Step 2: Create an xml schema based on the user list xml

This proved to be the trickiest part for me. Using the xsd.exe as documented in this article didn’t work.  After a lot of anguish I worked out that K2 was happy with the schema generated by InfoPath. So I opened an empty form in InfoPath and added a repeating text field (screenshot).

Next I exported the form to get to the .xsd (in InfoPath 2010 it is File -> Publish -> Export Source Files). Cleaning out the my: namespace and a bit of tweaking should give you the following schema definition. This definition should work fine with the xml produced by the above code block.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="UserList">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Users" minOccurs="0" maxOccurs="unbounded"/>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Step 3: Create an xml field for the process

Armed with the xml schema, we’re now ready to configure the workflow.

Fire up the K2 process designer and add the process xml field name Reviewers which we referred to in our code in step 1.

To do this open the Process General Properties window, expand the right panel. From the list select the Process/Activity Data node, expand it to select the name of your process and right-click on it to click Add.. and get to the Add XML Field.

Name the field ‘Reviewers’.

Switch to the XML Schema tab and browse to pick the xsd file created in Step 2.

When you hit OK you should now be able to drill down and see the Users node.

The key is to make sure that the node’s icon has a green overlay which flags it as a repeating node. If it’s there you should be fine. If not you need to repeat the steps above until you get the green overlay icon. Without it, the worklist item is not going to be assigned to multiple users.

Step 4: Setup the destination to read from the xml field

Select the activity which needs to be executed in parallel and click on the Destination Users node. Switch to the Advanced Mode if you are not already in there (select the checkbox on the first page). In the Destination Rule Options select Plan per destination ->All at once. We do this to tell K2 that when it goes to multiple users they will be able to open and work on the item in parallel.

Select ‘Create a slot for each destination’, this way each destination user get’s their own slot.

Click on the Edit button to configure the Destination sets.

Click on the ellipsis to open the Context Browser, drill down to the Process Xml field that we setup earlier and drag the Users repeating node (the one with the green icon) onto Name column.

You should now be all set to test out your dynamic multiple destination users! Running through the workflow you will now see that the worklist item gets assigned to each of the reviewers in parallel.

Categories: .NET Tags: , , ,

Limit SQL Server memory usage on your workstation, laptop or VM

Here’s a neat tip I learnt over the weekend.

All SQL Server instances are by default set up to use all the memory available on your workstation.

This is ideal when you have SQL Server running on it’s own dedicated server, not so ideal when you have SQL Server installed on your laptop, workstation or even on a SharePoint VM.

Here’s what MSDN says

if SQL Server is one of several server applications running on a single computer, the system administrators may need to control the amount of memory allocated to SQL Server. In these cases, you can use the min server memory and max server memory options to control how much memory SQL Server can use.

In the Server Memory Options page they go on to say:

When you are running multiple instances of the Database Engine, there are three approaches you can use to manage memory

  • Use max server memory to control memory usage.
  • Use min server memory to control memory usage.
  • Do nothing (not recommended).

Which brings us to how we can set the maximum limit. Quite easy. Just connect to each SQL Server instance and set the maximum memory to a more palatable value.

Here’s a visual walk through to limit the maximum memory usage to 512MB for your SharePoint 2010 instance (if you installed it on Windows 7).

1. Start SQL Server Management Studio (or SSMS Express) and connect to your SQL Server instance (SharePoint in this case): localhost\SharePoint

2. Right-click on the instance node and select Properties.

3. Click on the Memory node you’ll notice that the Maximum Server Memory is set to 2,147,483,647MB change it to a lower limit like 256 or 512MB. Click OK and your all set.

If you prefer SQL the same can be done with the following commands.


Enable advanced options:

USE master

EXEC sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE


Set the maximum amount of memory to 512 MB:

USE master

EXEC sp_configure 'max server memory (MB)', 512

RECONFIGURE WITH OVERRIDE


Display the newly set configuration:

USE master

EXEC sp_configure 'max server memory (MB)'


Set ‘show advanced options’ back to default:

USE master

EXEC sp_configure 'show advanced options', 0

RECONFIGURE WITH OVERRIDE

Feature differences between SharePoint 2007 Enterprise and Standard for a Publishing Portal

I recently had to deploy a site template that was built using SharePoint Enterprise Edition 2007 on an instance of SharePoint Standard Edition 2007.

Obviously given that some features were not available in the Standard Edition I received the ‘The template you have chosen is invalid or cannot be found’. Unlike a MOSS to WSS conversion the problem here is that the features do exist on the server but are simply not available for the standard edition.

I basically resorted to manually comparing the differences between a site template created in the Standard edition vs one created in the Enterprise edition.

Here’s the list if anyone ever needs this.

Remove these features from a template created in the Enterprise edition if you want to deploy it on a Standard edition. Obviously you need to test to ensure that your template is not actually using any of the Enterprise features.

<Feature ID="065c78be-5231-477e-a972-14177cc5b3c7" />
<Feature ID="0806d127-06e6-447a-980e-2e90b03101b8" />
<Feature ID="2510d73f-7109-4ccc-8a1c-314894deeb3a" />
<Feature ID="e8734bb6-be8e-48a1-b036-5a40ff0b8a81" />
<Feature ID="00bfea71-dbd7-4f72-b8cb-da7ac0440130" />

Categories: SharePoint Tags: ,

SQL Server 2008 Service Weirdness

Two weird things I learnt about SQL Server while building the SharePoint 2010 Service Manager.

1. SQL Server Agent service for SQL Express is bogus

Whenever Service Manager started, the SQL Server Agent service for the SharePoint (SQL Express) instance  would immediately stop with either of the following errors logged in the Windows Event Log.

  • The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. [0x80070422]
  • SQLServerAgent could not be started (reason: Error creating a new session).

The self explanatory title of the bug filed on Connect says it all ‘SQL Express installs SQL Agent Service for no apparent reason‘. Apparently the team cutting down features for the Express edition forgot to tell the Agent team that they weren’t needed in Express.

2. SQL Server VSS Writer Service : Startup Type get’s reset to ‘Manual’

The Service Manager has a feature that let’s you set the startup type of all the SharePoint and related services to Manual. This way they wouldn’t automatically startup when Windows starts hence leaving the workstation to boot faster.

The Service Manager only shows the ‘Stop Automatic Startup’ button if the Startup Type of any of SharePoint services are set to Automatic. While testing the feature I released that after sometime the button automatically showed up even after I had set all the services to start manually.

That was when I figured out that even if I manually change the service (through Control Panel) to start manually, something would change the startup to Automatic after a while. I haven’t figured out what changes it’s startup type to automatic but I’m guessing that’s by design. My workaround for the Service Manager was to ignore the startup type of the VSS Writer service when checking if all the services were set to manual.

Categories: Service Manager Tags:

SharePoint 2010 Service Manager

April 29th, 2010 Merill Fernando View Comments

With the final release of SharePoint 2010, I finally had time to brush-up and release the Service Manager that I wrote sometime back when the 2010 betas was released.

This utility is basically akin to the SQL Server Service Manager of yore.

If you have SharePoint 2010 installed on your local Windows 7 workstation then you will definitely come across instances where your workstation suddenly freezes up and everything starts moving in slow motion. The most likely culprit is usually one of the SharePoint services. At other times the SharePoint services simply eat away at your RAM.

That’s where the SharePoint 2010 Service Manager comes into play. It lets you start and stop all the SharePoint Services running on your workstation with a single-click.

This release handles both the full version of SQL Server as well as SQL Express Edition (the SharePoint instance). There is also an option to permanently disable the SharePoint services from starting up when Windows starts up, hopefully leading to faster boot times.

Here are a couple of screenshots. Get the setup file from CodePlex at http://sharepointserviceman.codeplex.com/.

I’d like to thank my colleagues at UniqueWorld including Neil, Rehman and Dougie who tested the first version and gave valuable feedback.

Please do report any issues you find to merill at merill.net

Visual Studio 2010 Installation Issue – Setup Failed with HRESULT -2147467259

April 27th, 2010 Merill Fernando View Comments

If you Visual Studio 2010 setup keeps failing when it tries to install the Visual C++ runtime, here’s a quick fix for you.

Try installing one of the Visual Studio 2010 Express Editions (I did the Web Edition: http://www.microsoft.com/web/downloads/platform.aspx) and then run the VS2010 installation.

I think it has something to do with mounting the ISO as a drive.

Categories: Tips Tags: ,

Icons & Illustrations for SharePoint Architecture Diagrams

March 26th, 2010 Merill Fernando View Comments

A picture is worth a thousand words.

Like me if you spend a lot of time writing up functional specs and architecture diagrams and are looking for ways to convey your ideas through illustrations, here are a few pointers.

Search the Hive

The 12 (now 14) hive has a wealth of icons. The images in the png formats are the slightly larger, higher resolution ones. You can find them at 14\TEMPLATE\IMAGES. Here are a few samples.

14 Hive Icons

 

Visio 2010 SharePoint Workflows

These are new ones, the icons in this stencil are vector based and can be resized without blurring.

SharePoint Workflow Actions

 

Google / Bing Image Search

Set the filter type to icon and clipart and you do come across some good gems. Remember to check the copyright on the images before using them.

 

SmartArt in PowerPoint 2007 & 2010

The SmartArts are a powerful tool to illustrate your ideas and need to be used wisely.

SmartArt

Play around with the various styles to get the look you want. Here’s one I built for a functional spec recently.

SmartArt Picture

 

Here’s a diagram I built today to document a web part. The icons came from Google, the Hive and good old Visio.

Filter Web Part

 

Snipping Tool

My favourite tool to get screen grabs, and save them to files if necessary, is the Snipping tool. This has been built into Windows since XP SP2 and is a really handy utility. To get to it just type snip in the Start menu and you should see the Snipping Tool.

One of the first things I do on a new installation is to disable the Red outline from the Option menu. FYI: All of the images above were snipped and saved to disk using the Snipping Tool before being inserted into Live Writer.

SnippingTool

Categories: Tips Tags: ,

.NET 4 Free Beta Exam Vouchers

March 24th, 2010 Merill Fernando View Comments

Looks like I somehow missed this. Free vouchers for the .NET 4 (Visual Studio 2010 Beta Exams) are available online in the Born to Learn blog.

I couldn’t register for the 71-515 (TS: Web Application Development with .NET 4) exam as it was booked up but there were seats still available for the other two exams that I registered for today.

You need to take the exams before 30-Apr, so you have almost a month to get prepared. You can also make use for the pilot beta preparation resources that other candidates are sharing, see the ‘Preparing for the Visual Studion 2010 beta exams’ post by Krista.

Categories: Certification Tags: , ,

SharePoint Client on Windows 7 Phone

March 19th, 2010 Merill Fernando View Comments

Yesterday I was excited when I could download the Win 7 dev kit, but that turned to disappointment when I realised that I could only run the application that I deployed and not try out the other stuff on the phone.

Today, thanks to Dan Ardelean I was able to unlock the Windows 7 Phone emulator. So here I was poking around only to be pleasantly surprised to see the SharePoint client built in to the phone.

Here are the first screenshots you’ll see of the Win7Phone SharePoint client.

SharePointWorkspace

MySiteLinks

SharePoint Client