Clayton's SharePoint Madness

All About SharePoint, InfoPath, and SharePoint Designer!

Archive for August, 2009

SharePoint 2010 Update 1 – Infrastructure Setup: Single Server Farm

Posted by Clayton Cobb on August 17, 2009


Here are my puzzle pieces:

  • ASUS G2S-A1 Laptop with 4GB RAM and dual 2.4 Ghz processors (Fig 1).
  • Windows Server 2008 R2 Enterprise RTM (Fig 1)

Fig 1 – System Info

  • SQL Server 2008 Standard SP1 (Fig 2)

Fig 2 – SQL 2008

  • Office 2010 64-bit (entire suite – Fig 3)

Fig 3 – Office 2010

  • SharePoint Server 2010 (Fig 4)

Fig 4 – SharePoint 2010

Fig 5 – MailEnable

  • Client Computers
    • Windows 7 Enterprise 64-Bit RTM desktop with IE8 and Office 2010
    • Windows Vista Home Edition 32-bit laptop with IE7, Firefox 3.0, and Office 2007

ASUS G2S-A1 Laptop with 4GB RAM and dual 2.4 Ghz processors (Fig 1):

  • The entire single server farm is built on one physical laptop with no virtualization
  • Originally, with only 3GB RAM (due to previous 32-bit OS), my setup was a dog.  It was getting killed with RAM usage sometimes maxing out
  • Thanks to 64-bit, I was able to bump to 4GB and fully-utilize it.  This made a huge difference as my peak RAM to date has been 3.1 GB and only when SQL had ballooned a bit.  Even then, performance was good.  Most of the time, even with 4-5 W3WP IIS worker processes running, I’m only eating up 2.5-2.8GB RAM.  This seems to leave plenty of room for the operations I’m performing, including viewing browser-enabled forms and running workflows.
  • My dual processors are constantly sitting in the 40-60% utilizatin range.  Performance Monitor shows me that the bulk of it is being used by the Timer Service (OWSTIMER), but remember that I’m running all of the server and client software you see in this blog post plus multiple browsers and Outlook 2010 at all times.
  • Interestingly, at this very moment, I’m only seeing <15% CPU but 3.1 GB RAM usage.  That’s with everything running and open that I have mentioned and while just running a workflow that sent an email to Outlook 2010.
  • Overall, I can see why the recommendations for RAM are much higher than in 2007.  The WFEs will be more powerful but will require more power to sustain…
  • I definitely plan to go to 6 or 8 GB, but my laptop can only handle 4, so my next build will be on an actual server. In fact, if things work out, I’ll do a server with 16GB RAM and then use Hyper-V to create a multi-server farm, although that is not technicaly supported in this version of Beta.

Windows Server 2008 R2 Enterprise RTM (W2K8R2)

After my 3rd time building my laptop into a complete, single server SharePoint farm, I finally feel comfortable with how W2K8R2 works in terms of roles and features. I’m not a sys admin by trade, so this is not my sweet spot, but I do feel pretty good after install #3. After playing with various roles/features and trying both a Workgroup with Local Users/Groups and a Domain with Directory Services, I think I have a decent setup to share with others. I realize there are many ways to skin this cat, but these are my experiences.

Roles and Role Services (Fig 6):

  • Active Directory Domain Services
    • Active Directory Domain Controller
  • Application Server – .NET Framework 3.5.1
    • All Role Services running
  • DNS
  • File Services
    • File Server
    • Distributed File System (Optional)
      • DFS Namespaces
      • DFS Replication
  • Web Server (IIS)
    • All Role Services (there are a lot)

Fig 6 – W2K8R2 Roles

Features (Fig 7):

Most features get installed automatically as pre-requisites for the roles above or during installation of SharePoint 2010. This is a great thing about the new products – things don’t just fail because you forgot to activate a random feature:

  • Desktop Experience (absolutely need this one for the cool Aero effects, hah – Fig 8)
    • This also enables many desktop features that are turned off by default on W2K8R2. You may not do this in a production environment, but for my single server testing, I want everything at my disposal
    • For the Aero themes to work, you must download and update your graphics drivers
    • After enabling this feature, you still must start the Themes service for desktop themes to work (not SharePoint themes)
  • Group Policy Management
  • Ink and Handwriting Services
  • Message Queuing
  • Remote Server Administration Tools
  • SMTP Server
  • Telnet Client
  • Telnet Server
  • Windows Process Activation Service
  • .NET Framework 3.5.1 Features
  • Wireless LAN Service (required if you want to use your wireless card)
  • XPS Viewer

Fig 7 – W2K8R2 Features

Fig 8 – W2K8R2 Aero Theme

SQL Server 2008 Standard SP1 (SQL08)

  • Upon starting the setup wizard, you are immediately notified that SP1 must be installed for SQL to work on this version of Windows. I followed suit.
  • Install ran straight through without issue and without
  • Didn’t bother with collation settings, but I normally choose Latin1_General_CI_AS_KS_WS. Collation settings are no longer part of the normal installation wizard.
  • All of my checks passed at each stage
  • Used a domain account specifically for SQL as the service account for all SQL services
  • Used a domain account as the SharePoint farm account and setup account in one, which required setting dbcreator and securityadmin rights on the DB instance (Fig 10)
  • I modified the Protocols in the Configuration Manager to enable Named Pipes (Fig 11)


Fig 9 – SQL 2008 Management Studio

Fig 10 – SQL 2008 permissions for SharePoint Farm Account

Fig 11 – SQL 2008 Configuration Manager

Office 2010 64-bit

  • Very simple install of Office Professional Plus 2010 64-bit plus Visio 2010, SharePoint Designer 2010, and SharePoint Workspace 2010
  • InfoPath and SharePoint Designer are my bread and butter. I also leverage Outlook heavily for workflow email routing.
  • A new tool to the mix is Visio, which can now allow business analyst to construct process diagrams into workflow templates that then get imported to SharePoint Designer. I’ll be showing that in a later update
  • Although InfoPath has two separate products listed, it is still just one license and no chance of providing the Editor as a free download like Adobe PDF – bummer.

SharePoint Server 2010 (SP2010)

Wow, very easy install. The install wizard has a Preparation Tool that checks all your pre-requisites and installs them as necessary (Fig 12&13).

Fig 12 – SP2010 Installation Wizard Home Screen


Fig 13 – Preparation Tool Wizard

  • Performed a complete install
  • Ran the Products and Technologies wizard where I specified my DB instance and used sp-farm from above as the Database Access Account.
  • Completed without error, and then Central Admin fired right up!!

MailEnable Standard

As of Windows Server 2008, Microsoft is no longer supporting POP3 as that has shifted completely to the Exchange team. So, we no longer have the ability to create mailboxes and such. You can still enable SMTP for relaying, but I need the ability to create mailboxes for my isolated domain users so that I could start doing workflows. After reading and learning why I couldn’t do this natively with W2K8R2, I found MailEnable, which is free for what I needed to do. This allowed me to create mailboxes for all of my users, assign email addresses, and associate those email addresses with each user’s Active Directory profile. MailEnable actually made it very easy by allowing me to import all of my AD users while simultaneously creating email addresses on the fly – simple and quick. Due to this, I was able to configure Outlook to use those mail accounts via POP3 and even got the address book to sync with AD for auto-resolution of names. This is now allowing me to do the end-to-end solution testing with SP2010, InfoPath 2010, SharePoint Designer 2010, Visio 2010, and Outlook 2010. Times are good… =)

**Here in Denver, my house is being pelted by a torrential hailstorm in the middle of August…mmmmk. Now I know why my insurance company just raised the hail deductible a few weeks ago…

**Blog created in and published by Word 2010 with help from OneNote 2010

Advertisements

Posted in Office 2010, SharePoint 2010, Windows Server 2008 R2 | Tagged: , , , , , , , , , , , , , , | 25 Comments »

Having Trouble Activating Windows 7 RTM?

Posted by Clayton Cobb on August 9, 2009


After downloading and installing the new Windows 7 64-Bit Enterprise RTM and getting all of my apps installed along with the proper drivers and such, I wanted to check and see how much machine was doing with the new OS and all the extra stuff I have installed (Office 2010 Pro Plus, SharePoint Designer 2010, SharePoint Workspace 2010, Visio 2010, Skype, Live Messenger, etc.).  The result was wonderful as my fairly weak desktop (cheap-o from 2 years ago) was moving very quickly and not using much RAM or processor.  However, while checking my System info – Control Panel > System and Security > System (same as right-clicking on Computer and selecting Properties) – I noticed that I still needed to activate my fresh Windows 7 installation (Fig 1). 

Fig 1 – System Info (post-activation)

However, when I clicked “Activate,” I got a strange DNS error. I fixed this by simply clicking “Change Product Key,” which you can see above, and I put in my product key again (same one I used during installation). This worked, and now I’m activated, but I figured more than one person will encounter the same thing, so…

**How about that beastly processor and RAM? Doing this screenshot just now showed me that I have a 1G RAM stick not seated, so I’ll go do that now. =D

**RAM fixed – 2.5G RAM is way better.

**This blog post was created and published using Word 2010

Posted in Office 2010, Windows 7 | Tagged: , , , , , | 3 Comments »

InfoPath – Copy SharePoint List Data to Main Data Source

Posted by Clayton Cobb on August 3, 2009


qRules Series #1 – CopyTable


One thing that you can’t easily do in InfoPath is to copy repeating data from a secondary data source – like a SharePoint list – to the main data source.  This is an important necessity, because the data from secondary data connections does not get saved in the resulting XML data file of a submitted/saved InfoPath form.  So, when you look at the form initially, you see the nice pretty data from your SharePoint list enumerated within the repeating table you placed on the canvas, but if you were to open the raw XML file, none of that data would be there.  This is because an InfoPath form only keeps whatever data is saved to its main data source.  A data connection to a SharePoint list is just a secondary data connection, which in essence becomes a window into the data as it currently exists.  Yes, you can re-open your XML file in InfoPath and see the data from your SharePoint list, but it’s the current instance of that data, not the data that existed at the time you previously submitted the form.  Sometimes, this is ok, but what if you needed to get that SharePoint data into your form and keep it there?  To do this, you must copy the data into the main data source.  How can you do that?  Well, you can custom code something, or you can use the great set of special commands known as qRules that is provided to all of us by Qdabra (qRules 1.7 is currently a free download).  qRules allows you to do many things, but the one command we will focus on is named CopyTable.

Caveats:

  • qRules introduces code to your form, so if your forms are browser-enabled, then they will require the Administrative-Approval publishing type. As of qRules 1.8, which will release this week (first week of September 2009), we can now use CopyTable in browser forms. I have tested it and confirmed that it does work. If you need information on how to properly publish a form template in this manner, refer to Janice Thorn’s blog post on the topic.
  • The CopyTable command will only work if the nodes match, which also means each column has to have data in it (no blank fields). The reason your fields can’t be blank is because when there is a blank field, no placeholder for that field is brought down in the XML, so it’s as if that node doesn’t exist. Qdabra is looking at supporting this in a future version.

The example I will use for this blog is a Weekly Status form template (based off the Meeting Agenda sample) within a Meeting Workspace where you pull in Active Tasks from a SharePoint Task List on demand so that a snapshot of the tasks is saved within the form.  The purpose of this is so that at a later time, the Weekly Status for that week’s meeting can be opened, and you will see the status of the team’s tasks when the meeting occurred rather than the current point in time.  In essence, it becomes an historical record of the team’s tasks just like meeting minutes are intended to be.  For the purposes of this blog, I will not go into great detail about meeting workspaces and how they work, but I will briefly explain how InfoPath forms need to be used in order to get the recurring meeting functionality. Here are the steps to accomplish the goal:

  1. Create Tasks List (covered in minimal detail)
  2. Create Meeting Workspace with a Form Library (covered in minimal detail)
  3. Create Form Template
  4. Install qRules and Inject Form
  5. Apply CopyTable Command Rule
  6. Publish the Form and Verify Success

Create Tasks List

  • Create a simple tasks list.
  • Add some tasks with a variety of statuses, priorities, and due dates (Fig 1).

 

Fig 1 – Basic tasks list with several tasks added

  • Take note that the Active Tasks built-in view only shows tasks that are not completed (Fig 2).

 

Fig 2 – Active Tasks view only shows incomplete tasks

  • Set Active Tasks view as Default View.

Create Meeting Workspace

  • Create a Basic Meeting Workspace with Recurrence (I prefer to use Outlook 2007 to create my recurring meeting, and then I use the integrated Meeting Workspace feature button to provision my site in SharePoint – Fig 3).


Fig 3 – Creation of Meeting Workspace in Outlook 2007

  • Edit the default page of your new Meeting Workspace and close the default web parts (optional).
  • Create Form Library and be sure to NOT click Yes for “Change items into series items” (Fig 4).  If we do that, then all forms show on every meeting page (each recurring date).  If we leave this as No, then only the relevant form for that meeting will display each week.

 

Fig 4 – Form library within a Meeting Workspace – not a series item

  • This will put your new Weekly Status form library on the default page for each recurring meeting within this workspace (Fig 5).


Fig 5 – View of default Meeting Workspace page showing the current week Create Form Template

  • Create new template based on built-in sample Meeting Agenda template and modify to your preference (Fig 6).

 

Fig 6 – Weekly Status form template

  • Create a Receive data connection to the Team Tasks SharePoint list, but set it NOT to “Automatically retrieve data when form is opened” (Fig 7).  Be sure to choose only the fields you want to ultimately see in the form, because this will play a big part in our CopyTable command.

 

Fig 7 – Tasks data connection with automatic retrieval check box de-selected

  • Create data structure in form template that exactly matches the data structure of the SharePoint data connection.  Your SharePoint Tasks list data connection will include whatever fields you selected during the creation of the data connection from the previous step.  Simply go to the Data Source pane in InfoPath and choose your “Tasks (Secondary)” data source.  Drill down until you get to the nodes.  You will notice that the fields within the Tasks repeating group are attributes and not elements (this is important).  *Note: You can also see the raw data structure by clicking Save As Source Files and opening your Tasks.xsd file in notepad. Now, go back to your main data source and add a new non-repeating group (aka Table) that includes the Tasks repeating group (aka Row) and all the attribute fields (Columns).  By default new fields will be added as elements, so be sure to add them as attributes so that they exactly match the attribute fields from the SharePoint list (Fig 8 ). The field names don’t have to exactly match, but you may find it helpful.

 

Fig 8 – Tasks node structure in main data source matching SharePoint secondary data source

  • Drag the newly added repeating Tasks group from your form’s Main data source onto the canvas as a repeating table and configure the columns/fields (Fig 9).
    • Stretch and shrink the columns so that the data will display properly
    • Change the Complete column header to %
    • Change the % field control format to show Percentage with no decimal places
    • Change the Due Date field control to a Date Picker (optional)
    • You’ll also probably want all of these to be read-only fields. *Note: To make a Date Picker control Read-Only, you set conditional formatting on it that says, “If Due_Date is present, then make control Read-Only.”

 

Fig 9 – Tasks Main data source repeating table with formatting Install qRules and Inject Form

  • Download and install qRules 1.7 from InfoPathDev.com (Qdabra’s community site).  You can see here that qRules 1.7 does far more than just CopyTable, but those other commands will be the subject of future blogs.  You can also get previously-written explanations and discussions related to qRules here.
  • After a successful install, Inject qRules 1.7 into the form template (Fig 10)

 

Fig 10 – Injecting form template with qRules 1.7 Apply CopyTable Command Rule

  • Create button for retrieving Active Tasks on demand and for performing the CopyTable qRule (Fig 11)
    • Simply drag a Button to the canvas and change the label to something like Show Active Tasks
    • Click the Rules button and Add a rule named something like Query Tasks and Copy
    • Add an Action that Queries using the Tasks data connection
    • Add an Action that Set’s a Field’s Value, choose the Command node from the QdabraRules (Secondary) data source, and set the value to this command string: CopyTable /dsnamesrc=Tasks /tablesrc=/dfs:myFields/dfs:dataFields /rowsrc=dfs:Tasks /tabledest=my:meetingAgenda/my:Task /rowdest=my:Tasks /empty=yes
      • KEY NOTE!! Do not try to paste your command string into the function builder (fx button). Just paste it directly into the Value field.
      • dsnamesrc: This is the name of your source data connection, which is named Tasks in our example.  Remember that this could be different if you apply this elsewhere, so be sure to use the proper name for this attribute
      • tablesrc: This is the table within your data source that provides the data.  You need to properly type in the hierarchy from the Tasks secondary data connection using the information you found in Figure 8.  Notice that the namespace for a SharePoint list is dfs and not my.
      • rowsrc: This is the repeating group that includes the nodes from your SharePoint data connection.  Mine is Team_Tasks, but if you use the default Tasks list from a Team Site, this would just be dfs:Tasks.
      • dsnamedest: This parameter is not used in the above command because it defaults to the main data source, but if you needed to copy your data to another secondary data source, you would provide the name of that data connection here.
      • tabledest: Like tablesrc, this is the table that will receive the data.  This uses the structure we created in our main data source in Figure 8.  Notice the default namespace for an InfoPath form is my, and then my data source root is meetingAgenda due to using the Meeting Agenda sample template.
      • rowdest: This is the repeating group where we want to send the data
      • empty: This is a field that accepts a yes/no flag based on whether you want to first erase all existing data first from the destination table.

 

Fig 11 – Custom button that queries the SharePoint list and performs the CopyTable command

  • Preview the form and click the button to verify that there are no errors and that your repeating table populates with the Active Tasks only (Fig 12).


Fig 12 – Repeating table in main data source showing Active Tasks Publish the Form and Verify Success

  • Publish the form, go to your Meeting Workspace, and click New in the form library
  • Fill out as much of the form as you want
  • Click the Show Active Tasks button, ensure it populates the table, and then click Save to save the form back to the library
  • When you see the XML form in the library, click on it and verify that you still see the data
  • Close the form, then go change an active task to mark it as completed so that it doesn’t show up in our Active Tasks view (Fig 13/14)



Fig 13/14 – CopyTable task complete and no longer active

  • Re-open your existing form (Fig 15) and notice that you still see the previous data (3 tasks, not 2).  This is the desired behavior, because we want to know the status of our Active Tasks at the time of the meeting, not later after the meeting when we re-open the form.  If we were only showing the secondary data source, then we would always see the current Active Tasks, which is not desired.  If you were to click the button again, then it would update with the new tasks, but that is not the intent here (feel free to apply conditional formatting to hide the button after it is saved).  You would only hit the button on new forms when conducting future meetings. In the upcoming qRules 2.0, Qdabra will provide SharePoint list diffing capabilities, so you will be able to see what has changed when you re-open the form, because qRules would automatically compute the diff.


Fig 15 – Final view of completed form

  • Another point of note is that we aren’t using my Auto-Generating Filenames for InfoPath Forms concept, because if you use submit in a Meeting Workspace, the XML form saves to the root of the form library, making it invisible to any of the recurring meeting dates.  Using save allows it to save to its respective meeting (Fig 16) date and thus only see one form per meeting and ensuring that you only see the snapshot of active tasks at that given date.


Fig 16 – Saved form shows up in proper meeting date  

Fig 17 – Final Product

As an overview of what we’ve accomplished, here is a final set of pictures showing that our form is published and saved to one site while the Task list resides on a different site, but both are interacting within the same form. What you can’t necessarily “see” is that the data is not being presented from the SharePoint list as a Secondary data source, but rather it is being added into and shown from the form’s Main data source for later use and reference.

Upcoming Blogs in the qRules series

  • Save images to SharePoint and convert to links in your form
  • Calculate difference between two dates
  • Get Error Count
  • Generate GUID

Posted in Office 2007 | Tagged: , , , , , | 21 Comments »