Nik Patel's SharePoint World

An adventure in SharePoint and Microsoft in general.

Archive for August, 2010

All you need to know about the Access Services 2010

Posted by nikspatel on August 13, 2010

Access Services 2010 is the new feature to host the entire Access databases within SharePoint 2010. It is only available in the SharePoint 2010 Enterprise Edition. This article not only covers the New Features in the Access 2010 and overview of the Access Services but also covers the benefits (both for business and IT), limitations, architectural details, typical Access Services Application Lifecycle (development, deployment, management process), and migration considerations for the Access 2007 (or earlier) databases to the Access Services 2010.

Access Services 2010 is the game changer in the Access 2010 space and may open new avenue for the departmental no-code database applications.  Hold tight and enjoy the reading.

Improvements in Access 2010

  • Integration of the Microsoft Office Backstage as part of the Microsoft Office Fluent User Interface
  • Web Database Format, Support for the Web Compatibility Checker, and Publish to Access Services
  • New Controls – Web Browser Control, Navigation Control ( Replaces the Switchboard View from previous version to navigate the Access objects)
  • New Data Type – Calculated Column Based on Expressions, much better improvements over the calculated values based on the queries
  • Support for the Office Themes for the Professional look and feel
  • Application Parts – Reusable forms, reports, or any other Access objects.
  • Data Type Parts – Custom Grouped Field Types, create your own group data type with the collection of fields to reuse in other tables.
  • Form and Report Designer – Live Data Preview , Improved Conditional Formatting on Reports & Forms (Conditional Formatting was added in the Access 2007)
  • Expression and Query Builder Intellisense
  • BDC Integration
  • Macros
    • Revamped Macro Designer
    • Form Macros/ UI Macros – Introduced in the Access 2007, Declarative Actions along with VBA Code for the UI logic, Additional Declarative Actions for the interactive forms programming in 2010
    • Data Macros – Works similar as SQL Server Triggers, Reusable Macro logic to create events at the table level (form level UI Macros are not reusable), Supports Before (Before Change, Before Delete) and After (After Insert, After Update, After Delete) events
    • Named Macros – Data level macros not attached to any events and can be called from the UI
    • Macros are XML based and easily portable from one environment to another
  • Ability to export to PDF and XPS files as a built-in feature (It was released in Access 2007 SP2) – Allows users to export forms, reports and datasheets to PDF and XML formats for easy distribution
  • Ability to connect to a web service as an external data source – Linked table to the Web Service Interface
  • Save as Template for creating access database templates, Download the Sample Templates from the Microsoft – http://office.microsoft.com/en-us/templates/CT010214400.aspx
  • Last but not least, Access 2010 (even 64-bit edition) has a limit of 2GB size.

  SharePoint Access Services 2010 – Benefits, Limitations, and Architectural Details

  • Access Services are nothing but Data driven web applications called web databases hosted in the SharePoint 2010 environment
  • Access Services Benefits and Usage
    • Balance between business agility and IT manageability
    • No Install Solution -  Web based Access
    • Improved Collaboration – Share and collaborate on the declarative RAD no-code web based team databases.
    • Centralized Data Storage – Single truth of the Application Logic and Data
    • Improved Reliability, Scalability, Security, and Manageability – Central IT management
    • Improved Backup/Restore – Access databases are part of the SharePoint Backup and Restore Process
    • Increased Concurrency – Locks the database at the object level, instead of database file level resulting in fewer conflicts
    • Access Applications Standardization using Templates – IT can configure/support services, start building out the web database standards, and let end-users manage it
    • RAD No-Code SharePoint Applications – Useful to build web based powerful RAD applications which supports query engine that can perform complex joins, filtering, aggregations, and parent-child relationships between lists.
    • RAD Reporting Tool – Useful as reporting tool to generate the customized reports (RDL files) based on SharePoint Lists hosted in the SharePoint.
  • Architecture Details
    • Access Service is a middle-tier service, which handles the  query processor and data access layer. It also manages communication between Access Web Application and SharePoint Content Databases.
    • Support for the Relational Database Data Integrity using SharePoint Lists Enhancements Infrastructure - Lists Relationships, Cascade Deletes, Unique Constraints, and Data level validations at the List and Item Level
    • Improved Performance/Scalability – Offers caching layer that addresses the limitations of the maximum number of list items that a query can return at one time by ignoring the List View Threshold. Improved Performance by allowing large record sets in the SharePoint List. Access DB supports more than 100K records. SharePoint Lists has filtering or sorting restrictions if you have more than 5K records in the list because content database blocks large calls. Access Services Data Sheets retrieves all the 100K SharePoint list items in the 2K records in chunk and later stitches them together in the ADO.NET record set in memory Layer as cached data for the performance. This allows sorting and filtering against cache instead of content database on the more than 50K records in the Access datasheet. Access datasheet View is rendered by the Project Datagrid object and it is smart enough to bring only 200 records at a time in the browser. With the smart navigation using AJAX experience, as you scroll down, it will bring additional records from the cache without affecting user experience. Access List Views are continuous forms supports paging, sorting, and filtering, and behaves same way as Datasheet View by retrieving data from the ADO.NET cache in the middle tier.
    • Concurrency Conflicts – Different users can make changes to different objects or different data items in a list without causing conflicts. When data conflicts do occur, a conflict resolution wizard enables the user to choose which version of data items to preserve. For object conflicts, Access provides the name of the user who made the saved changes and creates a renamed backup copy of the local object before downloading the other user’s changes.
    • Source Control – While downloading the database locally on file system,  Access client downloads the entire database only when a user doesn’t have local copy. Access fetches only objects or data items that have changed.
    • Only Web databases are supported in the Access Services. Web database supports two types of Access Objects – Web Objects that can run either in browser or Access Client and client objects (non-web objects) that can only run in the Access client. All design changes for both web and client objects must be made in Access Client. It is important to remember that client object definitions are published and stored in the SharePoint but they can accessed and executed during runtime only in the Access client.
      • All linked tables are client tables. Only client objects like reports, forms, macros can work with linked tables. Linked tables aren’t available to the web objects.
      • All the reports ,forms, and macros with VBA code makes these objects client objects.
      • All the web objects like reports, forms, and macros would work only with the web tables (Note web tables are Access DB tables fully compatible with SharePoint lists)
      • When working in the Access client and connected to the network where Access Service App is running, data and design changes to web tables automatically synchronize with the server.  When disconnected, Access client works with local copy and doesn’t allow changes to the tables. When reconnected, Access notifies users to synchronize and resolve any conflicts. Design changes to objects other than web tables synchronize only when users explicitly request sync by clicking Sync All button.
    • Forms and Reports gets rendered in the Data Form Web Part
    • Data Sheets rendered in the Project Datagrid JS Object to support large data sets
    • Reports are rendered in the Report Viewer Web Part installed with the SSRS Add-in
    • Access Service AJAX WS provides AJAX experience on the Access Web Applications
    • Better Administrative Control – Uses the OOB SharePoint Security for the Security Layer 
    • Managing and Fine Tuning the Access Services – From the Central Admin -> Manage Access Services Settings e.g. Max columns/rows per query, Max sources per query, Max calculated columns per query, Max order clauses per query, Max records per table in the join, Max sessions per users etc.
  • Limitations
    • Advanced SharePoint functionalities like Site Content Type, Metadata/Taxonomy, BCS are not supported in the Access Services 2010
    • Advanced Access functionalities like Linked Objects (Linked Tables, Linked SharePoint Lists, or BCS Links), VBA/Modules, Action Queries, Traditional Full UI Macros are not supported in the Access Services 2010
    • SharePoint Designer can’t open the Access Services Site. Any modifications in the Access Services Site requires Access 2010 client.
    • Access Services Application adopts look and feel from the Access Client. It  can’t be configured to inherit the master page branding or color scheme from the parent site collection/web application.
    • There is no site actions menu from the Access Services Web Application.

  Prepare the Environment to host the Access Services 2010

  • Access Services is installed part of the SharePoint 2010 Enterprise CAL
  • Make sure Service Infrastructure is enabled
    • Manage Services on Server – Access Database Service is started
    • Manage Services Applications – Review the Settings, No need to change any performance tuning settings
    • Manage Web Applications – Make sure application is associated with the Access Servic
  • Make sure all the features are activated
    • Central Admin -> System Settings -> Manage Farm Features -> Access Services Farm Feature
    • Central Admin -> Manage Web Applications -> Select Web Application hosting the Access Services Site -> Manage Web Application Features -> SharePoint Server Enterprise Web application features
    • Site Collection -> Site Settings -> SharePoint Server Enterprise Site Collection features
    • Site Collection -> Site Settings -> SharePoint Server Enterprise Site features
  • To the Access Reports on the web, Access Services requires

  Typical Access Services Application Lifecycle (Development, Deployment, and Management Process)

  • Development
    • Create Access Database using Access 2010 and Start with “Blank Web Database” or OOB/Custom Web Database Templates
    • Design Tables, Queries, Forms, Queries, Reports, Macros/Business Logic, Navigation, and Style/Theme
  • Deployment from the Microsoft Access Client using the Access Services “Publishing” Model
    • From the Backstage -> Publish to Access Services.  If there are errors during the Publishing Process, “Web Compatibility Issues” table will be created in the Access database for further review.
    • Publishing process creates a brand new SharePoint Site at the specified Path. Access Services Web Site is an isolated environment to host the Access database information.
    • Transformation Process to SharePoint (Client Objects  -> Web Objects, full fidelity between client and web objects)
      • ACCDB -> SharePoint Site
      • Access Tables and Data -> SharePoint Lists
      • Queries -> CAML Entries in the System Tables Rendered through Query Processor
      • Access Forms -> ASPX Pages using the Data Form Web Parts stored in SharePoint Document Libraries
      • UI Macros -> JavaScript Attached to SharePoint ASPX Pages
      • Popup Forms -> Floating Divs
      • Design Themes -> CSS Style Sheets
      • Access Reports -> RDL files (Requires SSRS 2008 R2 Add-in for the SharePoint 2010)
      • Data Macros -> SharePoint Workflow Actions
      • Modules, VBA Code Library -> It stored in the Access Services but not accessible/supported through browser interface. You can access them through the Access 2010 client.
      • Linked Tables, Linked SharePoint Lists, or BCS Links -> Linked Table Definitions or BCS Links Definitions are stored in the Access Services but not accessible/supported through browser interface. You can access them through client. Client objects in the Access Services will access to the linked table definitions but web objects in the Access Services can’t access the linked table definitions. Until Data in the SharePoint Lists (Natively as tables in the Microsoft Access), Web Objects in the Access Services can’t talk to data.
    • After the publish, what happens to the Accdb hosted on the server? Answer is during publishing process, Access 2010 Database Client stay as it is on the desktop or server in Accdb format. Access Services creates the backup copy of the Accdb on the server/desktop and makes the published Accdb as connected Access database to the Access Services Web Application. If you access the connected database after weeks or months from the desktop or server, Access DB client will sync/update the database with Access Services Site.
  • Deployment from the SharePoint without Access Client
    • Save the Access Database as a Template from the Backstage in Accdt format. 
    • Browse the SharePoint Site Collection’s Solutions Gallery and upload/Activate Accdt file in the SharePoint solution gallery, SharePoint is smart enough to treat it as a solution package file.
    • Visit the New Site Creation Section and Create a new Access Services site based on the custom Accdt web template database.
    • Use this approach if the person creating the new site based off the Access template need not have Microsoft Access installed on their machine or if you need to standardize the Access Services Web Database templates in your organization to create more than one sites based on same template.
  • Management  of Access Applications – To Modify Access DB After its been published using the Access Services “Sync” Model
    • Best Option: From the Options Menu, Click on the “Open In Access” to download the “Connected Microsoft Access” database for further modification
    • Any data changes on the client syncs to the web in real time. Any client changes of  the Table Schemas, Forms, Macros, Queries, reports doesn’t sync to web automatically. You have to use “Sync All” from the backstage to sync interface changes.
    • Individual objects in the Access Database on client can be modified independently and resynced to the SharePoint without affecting other objects. This allows better shared design and concurrency while updating the Access object design.
    • Please note that Access Services runtime environment is both browser and client but design environment is only Microsoft Access client. If you need to make changes in the reports or forms or schema, download the access db on the client, make the changes, and sync back to the SharePoint.

  Typical ways to integrate the Access database with the SharePoint 2010

  • MOSS 2007 supported sharing the Access DB on the SharePoint by uploading Access DB on the SharePoint Document Library just as any other documents and required Access 2007 on the user’s machine.
  • Three major options available
    • Good Solution – Basic Integration in the SharePoint Foundation 2010 (without Access Services 2010) 
      • Requires Access 2010 Client software must exists on the end-users machine.
      • Requires Access Database data must be compatible with the SharePoint.
      • Data and UI is centrally stored in the SharePoint and there is only one version of truth. Data is stored in the SharePoint Lists and UI is stored in the Document Library.
      • From the Access Client DB, visit the Database Tools Tab and Export the Access Tables to the SharePoint Site as a Lists using “SharePoint” ribbon button and Upload the Access DB to the SharePoint Document Library. 
      • Lock is per database. When multiple users are making design changes simultaneously, last person who upload the changes back to the SharePoint overwrite the other user changes. Make sure versioning and check out is required to avoid concurrent changes while making design changes.
      • Using this option, if you open the database read-only from the SharePoint, you can only change data in the linked tables from the Access Client. To make design changes, download the copy of the database to the hard drive, make changes, and upload it back to the document library replacing the previous version of the database. This option doesn’t provide the web UI on the SharePoint to view the data.
    • Better Solution – Supported as Access 2010 Services, Requires SharePoint Server 2010 Enterprise CAL
      • Doesn’t Require Access 2010 Client software exists on the end-users machine.
      • Requires Access Data and UI must be compatible with the SharePoint
      • Data and UI is centrally stored and there is only one version of truth. Access Database is published to SharePoint and hosted in the SharePoint as Access Services Web Application.
      • Lock is per object, as opposed to per whole database file. Using this option, while one user can make report changes, another user can create or modify the new form, and both changes can be synced back to the Access Services.
      • This will migrate the data as SharePoint lists and UI components as a client and web objects in the Access Services. Most of the Access Objects like web forms, reports will be transalated as web objects and can be accessed through the web UI. Many of the other objects like VBA code will be hosted in the Access Services as a client objects and will require Access Client software to view them.
      • Using this option, to make design or schema changes, save copy of the database to the hard drive.
    • Hybrid Solution
      • This scenario will cover most of all the Incompatible Access DBs in your organizations especially database applications created/maintained using the Access 2007 and prior versions.
      • What if you don’t have data compatible with the SharePoint? Can you share the Access database with SharePoint with above two solutions (good and better solutions)? Answer is No, Access DB must contain the SharePoint Compatible Data. Data must be compatible with SharePoint Lists. If any of the data can’t be moved to list, publishing can’t happen unless data is exported to separate database or changed to be compatible.
      • Instead use hybrid step by using the Linked Tables in the Access Services
        • Move any of the Incompatible Access Data to the SQL Server or another access db (from the database tools tab -> SQL Server or Access). This will move all the incompatible data to the access or SQL DB and keep the incompatible UI in the existing Access DB.
        • Next step is publishing the Access database with compatible data components and UI components to the Access Web Services. Client objects in the Access Web Services will reference to the external SQL Server for data and client objects can remain in the database without interfering with publishing process.
        • Later on, gradually, fix the incompatible data. Migrate the external data to the SharePoint lists and generate the web objects in the Access Web Services for full migration. To migrate the external data in the SharePoint lists, import the previously incompatible external SQL server or access db tables into the Access DB tables with valid UI and fix all the browser compatibility issues and republish it to the Access Web Services
  • Access Services feature comparisons supported in the SharePoint Foundation and SharePoint Enterprise CAL
    • Data in SharePoint Lists – SharePoint (included in the SharePoint Foundation)
    • Centrally Deployed Interface – SharePoint (included in the SharePoint Foundation)
    • Collaborative Design – Access Services only (Requires Enterprise CAL)
    • Web Forms in the Browser – Access Services only (Requires Enterprise CAL)
    • Web Reports in the Browser – Access Services only (Requires Enterprise CAL)
    • Server Macros – Access Services only (Requires Enterprise CAL)
  • Going forward, future direction should be Access 2010 and Access Services 2010
    • Start all the client Microsoft Access databases with the web database template to make sure its always compatible with SharePoint and Access Services.
  • How can you lock down the Access Services Publish Locations?
    • Client Level – Lockdown the registry key – HKCU\Software\Policies\Microsoft\Office\14.0\Access\Security\Allowed Publish Locations
    • Server Level – SharePoint security, define permission levels, Design, Contribute and Read only, Designers and above has permission to create the access web applications, contributors can read and write data, Users with read-only permission can only read data.

  Access 2007 to Access 2010 Migration Considerations for the Access Services 2010

  • Access 2010 will share the same native file format as Access 2007 – ACCDB format.
  • For Pre-Access 2007 version, follow the guide – “Transitioning Your Existing Access Applications to Access 2007″ to upgrade from MDB to the ACCDB version – http://msdn.microsoft.com/en-us/library/bb203849(office.12).aspx
  • Access 2007 Deprecated Features
    • Microsoft Calendar control (mscal.ocx) – Use Date Picker control as an alternative
    • Microsoft Replication Conflict Viewer
    • Option to export a report as a Snapshot file
    • Data Access Pages (DAPs
  • Rationalization Process – Analyze Access Databases in the Organization
  • Migration Process – Convert Access 2007 Databases in the Web Database Format
    • Look at Third-Party Web Conversion Service Offering:  http://www.access2010converter.com/index.html
    • Open the Access 2007 or Previous Versions of DB in the Access 2010 client
    • Fix the Access 2007 database objects web compatibility issues – Run the Web Compatibility Checker on the Access database to conforms the web compatible schema to meet the SharePoint requirements. This will create the “Web Compatibility Issues” table in the Access Database. The errors in table are explanatory with which table, which control you have and there is also a link for each error where you can also get info about what to do.
      • Analyze the Web Compatibility Issues Table , Fix the compatibility issues, and rerun the Web Compatibility Checker until Access database is web  compatible with Access Services and ready to be run on SharePoint.
        • Common Issues – Formatting, Data type, Validation Rules, and Structural Issues
        • Invalid Names -Tables and Columns Name should not conflict with reserved words or illegal characters. Changing the column names would not change all the Access objects. The Access Name AutoCorrect feature will propagate the changes to the queries and bound controls as long as objects are open at least once and saved. Please note that VBA Code and expressions are not automatically updated.
        • Compound Indexes – Indexes based on multiple columns are not supported over the web. One workaround for enforcing uniqueness on multiple columns are use the BeforeChange data macro.
        • Declarative Referential Integrity – Referential Integrity configured using the Relationships window that are not associated with a Web-compatible lookup are incompatible with the Web. You have to delete all relationships not based on lookups and create them using lookup wizard. Tables with relationships that aren’t implemented in lookup can’t be published. These lookups must be based on numeric data types.
        • Composite Keys – Composite keys are not supported in the Access Web Databases. If you have a database with composite indexes, you have to create a new primary index with AutoNumber type and create a data macro to preserve the uniqueness of your fields making up the composite key. Here is the article to write a data macros to resolve these issues – http://blogs.msdn.com/b/access/archive/2010/02/18/composite-keys-in-web-databases-through-data-macros.aspx
        • Primary and Foreign Text Keys -  Access supports primary and foreign keys on the text or dates.  SharePoint Supports relationship on numeric keys, not on the text based keys. Web database Primary key must be long integer. Easiest way to fix this add an auto number column to the parent table and add a corresponding long integer column in the child table as foreign key. You can then use the update query to sync the foreign key values in the child table. You would also need to delete the old relationship and create a new one using the lookup wizard. Make sure all other objects like queries, reports, forms are updated to use the new autonumber column before deleting the old text keys and relationships. You can also use data macros discussed in the Composite Keys section.
        • OLE data types needs converted into attachment data types. Update all the forms and reports where OLE data types were used for the pictures. Also, SharePoint supports only one attachment column per list so, make sure you have only attachment field per table in Access.
        • Tables with the recursive relationships to manage parents and child data in same table are not supported in SharePoint.
      • Please note that web compatibility checker doesn’t check all the database issues. Many of these issues may cause publishing fail. Any errors during the publishing process, Access logs the issues in the “Move to SharePoint Site Issues” table.
        • Incompatible data (e.g. invalid date, hyperlinks etc.) may cause publishing process fail.   Web compatibility checker checks data schema but  doesn’t check data values. Some of the Number/Currency/Date Time formats are not supported in Access Services and here is the guidelines around the supported format.
          • If you have a Number field, make sure it is formatted as General Number/Standard/Percent.
          • If you have a Currency field, make sure it is formatted as Currency/Euro.
          • If you have a Date/Time field, make sure it is formatted as General Date/Short Date. 
          • If you have hyperlink field, make sure it as fully qualified URL (relative URL doesn’t work) and URL length is less than 255 characters.
        • VBA code – It would require migration strategy to rewrite VBA code in the Data and UI macros. Please note that VBA code doesn’t cause publishing fail and require Access client to access them from the browser.
        • Invalid Expressions – Invalid Expressions entered manually without Expression Builder may cause publishing process fail. Invalid expressions in data schema definitions (e.g. validation rules or calculated column) will cause the publishing errors but Invalid Expressions in the web forms, reports, and queries can be found out only during runtime after publishing has been succeeded.  Access Services logs the compilation issues in the USysApplicationLog table.
    • Once Access 2007 objects are converted to the Access 2010 web legal format, Access 2007 (now converted to the Access 2010 web legal format) database is ready to publish to the Access Services. However, you can’t open your objects (Forms/Reports/Reports/Modules) other than tables in the browser if they are using the VBA code since they are still client objects. These client objects will reside in Access Services and Access s clients can open the forms and reports but they will not render in the browser.
    • For full web compatibility, plan for new web objects like navigation (tabbed interface), form and reports for the web interface with the new themes and look and feel, etc. You have to create new web objects if you want to open forms and reports in the SharePoint. You have to set the Web Display Form which will be displayed as splash page when you open your access application in the browser. To set your Web Display form go to the Backstage | Options | Current Database page and select a form from the Web Display Form dropdown menu.  Typically, it should be the main navigation form.

 Access Services 2010 Resources

Posted in Architecture, SP2010 General | Leave a Comment »

Restoring Site Collection on Same Web Application Requires Additional Content DB

Posted by nikspatel on August 11, 2010

If you have already restored the site collection (e.g. http://sp2010vm/) on the given web application and if you try to restore same site collection at the different path (e.g. http://sp2010vm/sites/projectinit) in the same web application and same content database, Restore-SPSite with throw error – “The operation that you are attempting to perform cannot be completed successfully.  No content databases in the web application were available to store your site collection.  The existing content databases may have reached the maximum number of site collections, or be set to read-only, or be offline, or may already contain a copy of this site collection.  Create another content database for the Web application and then try the operation again”.

Another scenario is if you have already restored the site collection (e.g. http://sp2010vm/sites/PI) on the given web application and for some reason, deleted the site collection, and try to restore same site collection (e.g. http://sp2010vm/sites/projectinit) in the same web application and same content database, Restore-SPSite with throw same error.

The reason for this is – If you have already restored the site collection on given web application once, you can’t restore the same site collection in same content database in the given web application as the different site collection location. Microsoft uses the source site collection GUIDs during the installation. If you delete the site collection from the web application, it leaves the GUIDs behind, and if you try to restore same site collection (same GUID) into same web application and content database, it would throw an error. To resolve the issue, new content database needs to be created in the web application to restore the site collection.

An article referencing the MOSS 2007, still applies to SharePoint 2010
http://vspug.com/jennyeverett/2008/03/06/restore-site-collection-to-same-server-sharepoint-2007/

Although above process adds one more content database in the backup/restore strategies, it would be cleaner solution. There is a work around to restore the site collection in the same content database which I haven’t validated for the SharePoint 2010.
http://vspug.com/akila/2006/07/11/no-content-databases-are-available-for-restoring-this-site-collection-create-a-new-content-database-and-then-try-the-restore-operation-again/

Posted in Admin General | Leave a Comment »

Delete Orphaned AD Users from the Site Collection

Posted by nikspatel on August 10, 2010

Recently I was working on the packaging up the site collection developed in my virtual machine and deploying to the client’s environment. In my virtual environment, I have created small sampling of client users in my active directory (e.g. Niks\tpatel) during the development and demo process. During the deployment, when I packaged up the site collection using the Backup-SPSite command and restored in the client environment using the Restore-SPSite command, everything looked great except people picker controls and security management pages showed the source active directory users (e.g. Niks\tpatel). What it means is there are duplicate users from my active directory and client’s active directory causing confusion among user identity (e.g. Niks\tpatel and ClientAD\tpatel). This is really interesting because as I am researching this issue, this has been known issue since WSS 2.0 era (see the links below) and I have never came across this situation in last 5 years. May be I have never noticed enough.

In my sample use case, I have a “Niks\tpatel” user added to the site membership in my virtual environment. After deploying the site collection in the client environment, I am able to browse the “Niks\tpatel” user from the target environment’s people picker control.

To gain more insight into where would people picker control show the user information from, I have launched extensive research on web and gladly found many resources but none of them captured in one article. This article summarizes what I have learned over the last couple of days and how to clean up the orphaned AD users from the site collection or cleanup the users from the site collection before getting ready for the packaging up for the deployment.

As we all know, SharePoint Foundation or WSS provides wonderful Active Directory integration from out of the box but many of us don’t know, behind the screen, WSS acquire user’s login name, display name, and email address whenever they are added to the site membership and saves them to the UserInfo table in the content database of the given site collection\web application. Whenever user creates or modifies the SharePoint list items, these users will linked to the list item and shows up in the “Created By” and “Modified By” indicators.

What it means is People Picker will show the combined users view from the authentication provider source (e.g. LDAP accounts from the Windows Authentication/Active Directory) and the accounts from the User Information List (UserInfo table in WSS_Content DB of the Site Collection/web application). This is important to understand as it means that accounts that may have been deleted or disabled or not exists in your authentication provider still show up because he may exists in the User Information List. In our scenario, this is where orphaned AD user (Niks\tpatel) shows up in the people picker control because even though she is not available in the target environment’s active directory but lingered in the UserInfo table from the source site collection.

To view the User Information List from the browser, log in to the site using the Administrator privileges, and navigate to the /_catalogs/users/simple.aspx. This page will show all the active users from the User Information List. Please note that you can’t delete the users from User Information List using the browser interface.

Additionally, you can browse the full User Information List by accessing the UserInfo table from the site collection\web application content database. As you can see from the folllowing screen, only three users are marked as active. Please note that you should not make any direct modification to the database or delete users from the UserInfo table. As you can see from the following screen, Niks\tpatel is still active user and exists in the UserInfo table even though she is either deleted from the active directory or not exists in the target active directory.

Now, since we know UserInfo table is culprit to show the user in the people picker, how would you clean up the orphaned AD users from the User Information List?

The best approach to delete the orphaned AD users from the User Information List, you should write a custom code and remove the user using the SPWeb.SiteUsers API. This will delete the users from the top level site collection. If you are inheriting the site membership in the sub sites, deleting users from the top level site will delete the users from the sub site making sure deleted users are not available in the sub site’s people picker. If you have broken the site membership inheritance, you have to manually write a extra code to delete the users using the SPWeb.Users API.

Step 1: To delete the specific user, Open the Visual Studio 2010 and create the Console Application, Target the project to Microsoft.NET 3.5 framework and “Any CPU” build. Please be careful with this step because Visual Studio 2010 defaults to Microsoft.NET Framework 4.0 and 32-bit runtime. Reference the Microsoft.SharePoint.dll in the console application, Import the Microsoft.SharePoint.dll in the class, and copy the following code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;

namespace DeleteSPUserInfoListUser
{
    class Program
    {
        static void Main(string[] args)
        {

            string loginName = @”Niks\tpatel”;  //@”Niks\Administrator“
            using (SPSite spSite = new SPSite(“http://sp2010vm“))
            {
                using (SPWeb spWeb = spSite.OpenWeb())
                {
                    SPUser spUser = spWeb.SiteUsers[loginName];
                    if (!spUser.IsDomainGroup &&
                        !spUser.IsSiteAdmin &&
                        !loginName.Equals(@”NT AUTHORITY\authenticated users”) &&
                        !loginName.Equals(@”NT AUTHORITY\LOCAL SERVICE”) &&
                        !loginName.Equals(@”SHAREPOINT\system”))
                    {
                        spWeb.SiteUsers.Remove(spUser.LoginName);
                        spWeb.Update();

                        Console.WriteLine(“User Deleted – ” + loginName);
                    }
                }
            }

            Console.WriteLine(“Press any key…”);
            Console.Read();
        }
    }
}

Step 2: Modify the code by replacing your Site URL and User Name. You can extend this code to delete all the orphaned users by looping through the collection.

Step 3: Run the console Application and verify that User is deleted and program runs successfully.

Step 4: Access the UserInfo table from the site collection\web application content database and verify that user is flagged as deleted and she is no longer active. Alternatively, you can access the /_catalogs/users/simple.aspx to verify that user isn’t exists in the User Information List.

Step 5: Verify that deleted user (e.g. Niks\tpatel) is not browsable in the People Picker control.

That’s it. Hopefully this will be helpful to someone who is looking for both background story and sample code to clean up the site collection users.

At last, here are some of the resources helped me to clean up the users from the site collection. Please review these resources for more details.

Andrew Connell’s thoughts on the UserInfo and Profile DBs and how they work in the WSS 2.0. Most of these are still valid in the SharePoint 2010.
http://www.andrewconnell.com/blog/archive/2005/08/18/1912.aspx

Sahil Malik and User Profiles in the MOSS 2007
http://blah.winsmarts.com/2007-7-SharePoint_2007__All_you_ever_wanted_to_know_about_User_Profiles.aspx

Tobias Zimmergren and User Information List
http://www.zimmergren.net/archive/2008/06/25/sharepoints-hidden-user-list-user-information-list.aspx

Step by Step Profile DB Cleanup
http://blogs.technet.com/b/seanearp/archive/2009/03/04/sharepoint-profile-cleanup.aspx

SharePoint Automation – Sample code to delete all the users from the site collection. There is nice enhancement in the comments section to lookup the deleted or disabled AD users.
http://stsadm.blogspot.com/2008/08/delete-all-users-from-site-collection.html

Posted in Admin General | Leave a Comment »

SharePoint Site Logo URL Limitations – Site Settings Page vs Web Part Pages

Posted by nikspatel on August 7, 2010

Have you ever tried to change the site logo for the SharePoint 2010 Team Site with lots of web part pages or have you ever tried to specify a Site URL tokens on the site logo URLs to easily migrate the site pages from one environment to another environment without getting broken? This is another item where it’s really hard to understand why Microsoft implemented one of the most obvious tasks such a confusing way.

Issue No.1 – Specify the Site Logo URL on both the Site Settings and each Web Part Pages.

Without any SharePoint Designer customizations or Custom Code, if you want to change the site logo through browser interface, you have to take different steps to change the site logo for the site pages and web part pages.

To change the site logo for the site pages or application pages hosted in the _layouts directory, visit the site settings -> Look and Feel Section -> Title, description, and icon link to change the site logo. This would apply to site logo to most of all the pages including lists, document libraries, wiki pages etc.

If you created a web part page through SPD or Browser Interface, one of the first things you would notice is site logo changed through the site settings page is not reflected on the web part pages. The reason for this is – every web part page includes the TitleBarWebPart control which overrides the standard ribbon interface with the web part ribbon bar. One way to change the site logo on the web part pages is editing the page and clicking “Title Bar Properties” on the Page tab. Once the properties bar loads on the right, change the URL to your site logo under Image Link.

Another way to apply the Site Logo page set by Site Settings Page to the web part pages is comment out the TitleBarWebPart control markup from the web part pages using the SharePoint Designer 2010. It would hide the TitleBarrWebPart control from the web part pages and show the site logo set by the site settings page. Downside of this approach is you will lose the page title on the web part pages. After exploring both options, I would rather change the site logo URL for each web part pages than changing each web part pages by hiding the TitleBarrWebPart control using the SPD 2010.

Bottom line is doesn’t matter what approach you take, please remember that using the browser and SharePoint designer no-code customization, you will have to apply the site logo on both site settings page and web part pages. This means, if you have 20 web part pages in your system, you have to change the site logo through Title Bar Properties 20 times.

Issue No. 2 – You can’t specify the Site URL token to the Site Logo URL

Another major issue with the site logo URL configured on the site settings page and web part pages is you can’t apply the site URL or site collection URL tokens in the logo path. Typically if you create custom LIM or Ribbon button, you can specify the Site URL token – {SiteUrl} so, whenever we migrate site to another environment, URLs won’t get broken.  e.g. {SiteUrl}/SitePages/Project%20Summary.aspx. One of the major limitation with the SharePoint 2010 is you can’t set the URL tokens – {SiteURL} or ~Site or ~SiteCollection on the site logo URL. At least, that’s my initial test says.

One way to resolve this issue is by deploying the Site Logo to the _layouts folder on all the WFE servers either manually or using feature and solutions framework. Typical path for the _layouts folder is <SharePoint Installation Folder>\TEMPLATE\IMAGES\CustomApplication where CustomApplication is custom folder name and typical SharePoint Installation Folder is C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14.

Once the Site Logo is deployed on the server, use the /_layouts/images/CustomApplication/Logo_small.jpg as Site Logo URL on the Site Settings and Web Part Pages. This would ensure that whenever you are moving your application from one environment to another, your site logo URL will always work as long as Site Logo image is deployed on the appropriate path on the server.

Posted in SP2010 General | Leave a Comment »

Delete Orphaned Workflow Status Columns from SharePoint List Views

Posted by nikspatel on August 7, 2010

Recently I been working on the SharePoint Designer Workflows associated with the site content type which used in the SharePoint list. During the development phase, as I have been republishing and reassociating multiple versions of workflow to the site content type, somehow I have ended up having multiple workflow status columns on the SharePoint List Views. As you can see from the following screen, there are two Project Approval Workflow Status columns.

As many of us knows, whenever you publish the SharePoint workflow to the list or content type, workflow status column automatically added as a SharePoint List Internal column and available to configure in the SharePoint Views. Just like many things in the SharePoint, if you ever delete the workflow association to the content type or list and reassociate later to the content type or list, workflow status columns created by the previous association gets left behind. These orphaned workflows status columns or many of the site columns created by workflows shows up in the configuration page of the SharePoint views causing headaches for the end user.

One of the most critical aspect of this issue is there is no easy way to delete these workflow status columns from the browser interface. You can’t manage (or delete) work flow status columns from the list settings page, site columns gallery, or site content type gallery.

Thanks to the wonderful free utility called SharePoint Manager 2010 (http://spm.codeplex.com/) available on the codeplex, you can view the internal details of the SharePoint List, Site Columns, and their GUIDs which we can use with the SharePoint Object Model to cleanup these orphaned columns. For MOSS 2007 environments, look for the link for the SharePoint Manager 2007 on the above link.

To Delete the Orphaned Site Columns, please follow following steps.

1 – In our scenario, I would like to delete the workflow status column “Project Approval Workflow (Previous Version..)” from the Modify SharePoint View Pages.

2 – Open the SharePoint Manager 2010 and browse to the SharePoint_Config -> Content Service -> Web Applications -> Your Web Application .> Site Collection -> Your Site Collection -> Your Site -> Lists -> Your List -> Fields, Look for the “Project Approval Workflow (Previous Version..)” field and note the Id and InternalName column.

3 – To delete this site column, unfortunately you have to write a custom code. Open the Visual Studio 2010 and create the Console Application, Target the project to Microsoft.NET 3.5 framework, and “Any CPU” build. Please be careful with this step because Visual Studio 2010 defaults to Microsoft.NET Framework 4.0 and 32-bit runtime. Reference the Microsoft.SharePoint.dll in the console application, Import the Microsoft.SharePoint.dll in the class, and copy the following code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;

namespace DeleteSPListColumns
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SPSite spSite = new SPSite("<a href="http://sp2010vm">http://sp2010vm</a>"))
            {
                using (SPWeb spWeb = spSite.RootWeb)
                {
                    SPList isoDocs = spWeb.Lists["Trainings"];
                    SPField field = isoDocs.Fields[new Guid("acf50d04-fa75-4917-a4f3-1bd6ea4f1810")];
                    Console.WriteLine("Field Name:" + field.InternalName);

                    field.Hidden = false;
                    field.ReadOnlyField = false;
                    field.Update();
                    isoDocs.Fields.Delete(field.InternalName);
                    Console.WriteLine("Field deleted - Done!");
                }
            }
            Console.WriteLine("Press any key...");
            Console.Read();
        }
    }
}

4 – Modify the code by replacing your Site URL, List Name, and Field GUID

5 – Run the console Application and verify that Field InternalName is valid and program runs successfully.

6 – If Console App ran successfully, Open the Site Manager 2010 and verify that “Project Approval Workflow (Previous Version..)” field is deleted.

7 – Refresh the browser interface to modify the SharePoint Views and deleted column shouldn’t be available as a configurable column.

Please note that you can use this method to delete any lingering site columns or list columns using this approach in the SharePoint 2010 and MOSS 2007.

Posted in Dev General | Leave a Comment »

InfoPath Form Services – 5566 Error – “exceeds the lookup column threshold”

Posted by nikspatel on August 6, 2010

I have recently came across the error – “5566  – Lookup columns exceeds the threshold enforced by the administrator” while browsing the InfoPath forms on the SharePoint Site using the users with the “Domain Users” active directory permission. My curiosity of this problem got increased as I was able to browse these InfoPath forms using the users logged in with the “Domain Admins” active directory privileges.

I had a SharePoint list with 1 lookup field and 10 person/group fields. As many of us are aware that by default, SharePoint installation configures each web application list view threshold to 8. You can access this setting from Central Administration -> Application Management -> Manage Web Applications. Select the web application hosting your site collection or site and select the General Settings -> Resource Throttling. From this page, you can maintain the list view thresholds and it specifies the maximum number of Lookup, Person/Group, or workflow status fields that a database query can involve at one time. Please note that this setting doesn’t apply to the SharePoint users with the “Domain Admins” active directory privileges. It applies to only general end-users with the “Domain Users” active directory privileges.

What I really didn’t know is what columns were considered as lookup columns.  All I knew until this moment is only Lookup fields were considered as a Lookup columns in the list view threshold limit.  Based on the error message, it was obvious that if I increase the lookup column threshold for the SharePoint web application through central admin site, users with “Domain Users” won’t have any issues accessing the InfoPath forms on the browser. The real issue here is should I increase the threshold or find the alternative solution.

 

As you create the SharePoint Lists, the list view threshold limits number of lookup, person/group, or workflow status columns can have in specific views. Please note that I have mentioned here views. SharePoint list doesn’t limit you to have 8 columns in the list. You can create more than 8 lookup and person/group columns as long as your view doesn’t show more than 8 columns. But, if you have more than 8 columns, list throttling takes in place behind the screen causing performance degradation.

The real Problem starts when you are consuming SharePoint list in the client applications like SharePoint Workspace, Microsoft Access, or InfoPath including InfoPath Form Services. Let’s say, If you have customized the InfoPath forms based on the SharePoint list and created a InfoPath Views based on the SharePoint fields and later on use in the InfoPath Web Part, InfoPath will throw an error if users with “Domain Users” AD permission accesses the InfoPath form views. The reason for this is even though InfoPath form view might won’t show all the fields, InfoPath form itself trying to synchronize all the InfoPath form fields with the underlying SharePoint list. it means in my scenario where I have 1 lookup field and 10 person/group fields, it tries to query more than 8 fields with lookup or people/person group columns. In this case, the above error of the “Lookup columns exceeds the threshold enforced by the administrator” shows up. For the office synchronization, SharePoint list threshold limits the number of lookup and person/group fields. Please note that workflow status columns used only in the SharePoint Views. Since workflow status columns are not considered as a List Column for the specific list, it gets excluded from the threshold limits for the offline clients or office apps synchronization.

To resolve this issue, basically there are really only two options. If you can find more, let me know.

1 – Increase the threshold on the Central Admin Site for the given web application. Downside of this approach is since you are changing these settings at the web application level. For performance reasons, SharePoint limits the number of lookups allowed when offline clients try to sync. If you change these settings, it will apply to all the site collections and sub sites underneath it causing potential performance issues.
http://planetmoss.blogspot.com/2010/07/sharepoint-workspace-2010-exceeds.html

2 – Try to limit the total number of workflows, lookup, and person/group fields less than or equal to number of threshold settings. Please remember that every list or document library has two in-built people/group fields called created by and modified by. So, in reality, if you would like to use the lookup, person/group columns in the SharePoint Views and Client applications like SharePoint workspace, InfoPath, and Microsoft access and if your threshold settings are 8, you can’t have more than 6 fields with lookup or person/group fields. Now, that’s what you call harsh reality.

Hopefully this post will give you balance views of two sides of 8 lookup column list threshold limit and you will able to make educated decision.

Posted in SP2010 & InfoPath | Leave a Comment »

Creating Custom List Item Menu using SharePoint Designer 2010

Posted by nikspatel on August 3, 2010

Recently I have been spending lots of time building custom no-code SharePoint 2010 solutions using InfoPath 2010, SharePoint Designer 2010, and browser customizations. As I had been enjoying the SPD 2010, I been surprised by how easily many tricky tasks can be accomplished in no time. I must recommend every SharePoint Developers to master the SharePoint Designer 2010 to avoid many custom time-consuming coding tasks which wasn’t possible in the MOSS 2007 and SPD 2007.

One of those tasks is creating a new LIM (List Item Menu) on the SharePoint ECB (Edit Control Block) menu. ECBs are list items specific context menu which are available in list views and list view web parts. With SPD 2010, creating the new list item menu on the SharePoint list and library are never easier than before. To create the custom list item menu using the SharePoint Designer 2010, follow these steps.

  • Open the Site using the SharePoint Designer 2010
  • Select the Lists and Libraries from the Navigation and select the list where you would like to create the list action menu. e.g.Projects
  • From the Ribbon bar, select the Custom Actions Tab -> Cutom Action Button -> ”List Item Menu” to create the custom list action item.

  • On the Create Custom Action window, specify the LIM name and LIM URL (Navigate to Form within List, Initiate the Workflow Associated with List, or Navigate to the Custom URL). Additionally you can also specify the button image, security mask, and sequence number as advanced configuration. 
    • Specify the URL in any of the following formats. I would suggest to use the URL formats with {SiteUrl} token to maintain the validity of the URLs during the import and export of the site collection.
      • /SitePages/Business Case Document.aspx?ItemId={ItemId}
      • /SitePages/Business Case Document.aspx?ItemURL={SiteUrl}/SitePages/Business Case Document.aspx&ItemId={ItemId}
      • ~site/SitePages/Business Case Document.aspx?ItemId={ItemId}
      • {SiteUrl}/SitePages/Business Case Document.aspx?ItemId={ItemId}
    • One limitation of this method is it doesn’t allow end-user to configure the Navigate to URL in the Popup or New browser window.

  • Click OK to create the Custom LIM action. SPD Lists settings page should show the newly created LIM menu.

  • Verify that new LIM is created on the Lists interface on the browser window.

Posted in SPD 2010 | Leave a Comment »

Configure ECB Menu on Any Column on SharePoint 2010 List or Library using SharePoint Designer 2010

Posted by nikspatel on August 2, 2010

In MOSS 2007 or SharePoint 2010, every list and document library has a ECB menu associated with the Title column.

In SharePoint 2010, you can configure ECB menu and link to the view item page with any columns with little customizations using the SharePoint Desinger 2010. Try following steps to add the ECB column on additional columns in addition to Title column.

  • Open the SharePoint List or Document library and open the SharePoint View you want to add the ECB Menu on the additional column.
  • From the SharePoint Browser Interface, select the “Modify in SharePoint Designer (Advanced)” from the Ribbon Bar

  • This will open the SharePoint List View in the SPD for further modification. Highlight the field you want to add the ECB Menu and look out for the little floating icon with “>”. Click on this icon and it will open the small popup window with “Show List Item Menu” option.

 

  • Check on ‘Show List Item Menu” checkbox and save the page to configure the ECB menu on the additional column.

Posted in SPD 2010 | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.