SharePoint with Attitude…

The following error message is returned by Infopath when publishing a form to SharePoint 2010:

The problem occurs because the site does not have a root site collection. This problem also occurs when you try to publish the InfoPath form to a site collection other than the root site collection.

“This problem occurs because InfoPath makes a call to determine the version of SharePoint Server that is being run by the server. This call is performed as part of the publishing process, and the call is made to the root site collection. When no root site collection exists, the call cannot to determine the version of SharePoint Server that is being run by the server. Therefore, the publishing process fails.”

To resolve this problem, create a root site collection for the SharePoint 2010 site.

Never thought it would take me about an hour to delete a Record library…

I was trying to delete a Record library and the delete option is not part of the library site settings as with any other type of library or list.

This is the first time I work with Record libraries so I found it interesting. I tried using SharePoint Designer and got the error “List cannot be deleted”.  I was an administrator and the library had some documents so I thought maybe I had to delete first all documents and then try to delete it.

When trying to delete documents from the library and I kept getting this error: “The item cannot be deleted, moved, or renamed because it is either on hold or is a record which blocks deletion”

Turns out that when you create a Record library, by default it declares all items in the Library as records (which makes sense because it is a Record library right?), and a record cannot be deleted. So if you are planning to delete records from a Record library it is better to go to “Record declaration settings” (under the library site settings) and uncheck the “Automatically declare items as records when they are added to this list”.

If you however didn’t do this and want to delete an existing document record, go to “Record declaration settings” (under the library site settings), uncheck the “Automatically declare items as records when they are added to this list”, and check the “Always allow the manual declaration of records”.

By doing this you are now able to undeclared a record as a record, and then be able to delete it.

To undeclared a record:

Under the record context menu, select “Compliance Details”

You will see the status of the record, whether if it is on hold or not and wheter if it is a record or not. To be able to delete remove all any holds and “Undeclare record”.

Close the window and now try to delete the document; it will be deleted

Great! So the library is now empty and I still can’t delete it :S

Well, the job “Hold Processing and Reporting” takes care of scanning libraries to find records and to know what libraries what records or not. So we have to run this job for SharePoint to know that the library doesn’t have any records.

After running the job I go to the Library settings and the delete option is there!!! Finally!!!

If you’ve worked with the data form web part (or as some people call it: the data view web part, lol), you’ve probably noticed that if you enable “Sorting and Filtering on column headers” in the data view properties, it doesn’t actually behave as expected: Yes, it enables sorting and filtering in your columns, but if your columns are of a numeric type, then the sorting doesn’t actually work, it sorts all columns as if they were of a string type.

Let’s take for example the ID field of a list items, the ID is an integer field. Now let’s create a DVWP to display the items from a list, including the ID, and then enable sorting. Now let’s sort the ID column and we notice that it is sorted in a wrong way, it is sorted as a string and not as a number.

Well, this is a “bug” in the DVWP, by default it will always sort all columns as strings, if you want to fix this you have to do some extra work in the DVWP code. So this is what happens:
First let’s remember how sorting works in XSLT: to sort we have to use the element. This element has the property “data-type”, this property specifies if the output should be sorted as text or as a number. As an example if we want to sort the ID field as a number we have to write this xslt code:

Now, let’s go back to the DVWP sorting. When you enable sorting, the DVWP adds to the page some xslt code, including the following xsl parameter: text

If you look at your xslt parameters you will see it in there, this parameter will be used as the data-type property for the element. We can see that the default value is text, so it doesn’t matter what type of field you sort, it will always be sorted as a string.
This parameter is used Inside the “dvt_1.body” template :

The way to solve this issue is to add a condition inside the template and verify which field is being sorted and depending on the field you specify “text” or “number” to the data-type property. This way your “dvt_1.body” template should look like this:

Now the ID is sorted as a number:

K

Does SharePoint suck? No way!!, but with a client today, I got that comment from one of their developers.

I’ve heard that comment too many times, and every time I hear it I laugh way too much; mainly because it reminds me when I hated SharePoint too, yes I also thought that SharePoint sucked, but that was when I first started to work with it, long long time ago…

If you think about it, every time more and more companies are implementing SharePoint, why? because the business people see a value in it, they see what SharePoint can do to improve their businesses and operations.

Having more SharePoint implementations, requires more people working in SharePoint projects, and let’s be honest not too many people are really “SharePoint developers or experts”, most of the people working in SharePoint are .NET developers.

Which brings me to the question: who is usually the people complaining about SharePoint and saying that SharePoint sucks? the answer: the developers.

Interesting, why does this happen?, well it’s easy. SharePoint is based in .NET, and for many people is easy to think that if you know .NET then SharePoint will be easy for you.

The thing is, if you know SharePoint, then you know .NET; but if you know .NET it does not mean that you know SharePoint, yes it is an advantage, because it means you know the basics, but there is still a learning curve.

Usually developers are thrown into SharePoint projects just because they know .NET, without a previous training/knowledge of SharePoint. It means they will learn SharePoint on the road, and that learning curve can be painful (ask me! lol). It is usually because of the lack of knowledge in SharePoint that people say SharePoint sucks, because we wan to do things the way we are used to in .NET, and things that can be done in 5 min in .NET, take hours in SharePoint, so we get frustrated.Let’s not forget that SharePoint is a platform, it has its own way to work.

Now I’m not saying that doing a project in SharePoint is better than doing it in ASP.NET for example, or than doing it in ASP.NET is better than doing it in SharePoint, It just depends on the project, there will be projects where it is better to do it in SharePoint and other where it is not, SharePoint Architects play a key roll in here.

This, also brings a person to say that SharePoint sucks, when the sales people sale a project in SharePoint (because they just want to sale) without analyzing if it is convenient or not to do it in SharePoint, it will become frustrating for the developers to meet some requirements.  At the end the developers will be the ones working on the project that was not suitable for SharePoint and they will end hating SharePoint.

In conclusion, SharePoint is a very robust platform, and you have to know its architecture and go through that learning curve in order for you to be able to work in a SharePoint project and not get frustrated in the way.

After a couple of projects working with SharePoint, you’ll understand more and more about it, and I’m sure you’ll like it, and probably you’ll even love it! just like I love SharePoint.

Love not hate people!

One of the clients that I was working with needed some extensibility to the out of the box SharePoint Usage Reports.

The data displayed on the reports didn’t meet their requirements, first, they needed to match the data with the user data in SAP in order to get the reports by Region and other parameters, and second, they needed historical data, and not only the last 30 days of information.

The first requirement was not a problem, and at the beginning we didn’t think the second would be a problem either, but oh we were wrong.

Using the out of the box usage reports provided by SharePoint was of course not even an option, we needed a way to get the data and create our own reports.

I spent some time doing research and this is what I found: 

• Using the Object Model. The SPWeb object contains the method GetUsageData(). This is the method called by the Usage Repor pages in sharePoint. It Returns a table that contains information about the usage of a Web site, based on a time interval. Unfurtunately there are only two options for the time interval, either today or last month. So, no way to get more than 30 days of information using the Object model.
• Web Services. No web service exposes usage report data, so I thought about using owssvr.dll, but again, you can only get the last 30 days of data.

Usage Report data is stored in log files in the IIS and also in the Shared Services database, and not only for the last 30 days, but for all the time since the usage reports were activated, so how is it possible that SharePoint doesn’t have a way to expose this data? Well, that’s how the world works.

Working directly with the SharePoint Databases is not supported by Microsoft, so it seemed that my only option was to create a program to read the IIS files. Have you taken a look at those files? Well, they are quite a mess and I didn’t want to have to do anything with them. So I consulted one of my closest friends: “Reflector”, and once again he gave me the solution I was looking for

I found out that the PortalContext object contains a reference to the Shared Services Database through the property “AnalyticsSqlSession”, using this property you get a connection to the database without having to worry about server name, database name, etc.

Since all the usage data is stored in the Shared Services database this was definitely helpful, but let’s not forget that working directly with the databases is not supported, and although I was not accessing directly the database using SQL server, I was however connecting to it and making some queries (READ ONLY).

We presented our solution to Microsoft, and to our surprise, the solution was accepted because somehow we were using the Object Model to get the data, nice!

Having the solution and Microsoft approval, everything got easier. These are the tables in the database that are related to the usage reports:

As you can see, everything is in there! We can do the queries we want and get the data we are looking for.

Going back to the AnalyticsSqlSession property of the PortalContext object, this is how I got it:

Worked like a charm!

  A SharePoint Services implementation has: 

• One Configuration Database
• One or more Content Database

 SharePoint Portal uses the following databases: 

• Search Database
• Shared Services Databases
• Shares Services Search Database
• SSO Database

 In this post I’m going to focus in the configuration database and the content database. If you are interested in more detail about the other ones, click here.

  The configuration Database 

The information on this database can only be updated through the Administration site. It stores the following data: 

• Global Configuration: Information regarding the farm servers, for example which are the web servers or the database servers in the farm.
• Virtual Server: Information regarding each virtual server in the implementation, for example, which SMTP server is being used.
• Site Map: Information regarding the relationship between web applications and their respective content databases. When Windows SharePoint Services receives the URL of an application, the settings that determine which database is the database that contains the data content of the site.

Example: When a request is send to the server to http://MyServer/sites/mysite/Lists/AllItems.ASPX The relevant part of the URL, sites/mysite specifies the site collection of the application. Since, by default, sites offering wildcard inclusion of site collections created on the server, sites/mysite is a piece on the virtual server that specifies the site collection. Because the relevant part of the URL to exclude the name of the team, two virtual servers with the same or different addresses can serve the same content, site data may, for example, a site geared toward both the extranet and to the intranet site.

In the example above, the configuration database specifies that data for the site reside within a database server called ITG_STS_1 in database called STS_01. After collecting this information from the configuration database, the ID of the database (101 in Figure 6) is cached in the session cookie and Web server used to connect to the correct database in the following applications. Windows SharePoint Services uses optimistic caching scheme, ie, which implies that the site has not moved since the last visit by the user. If the URL is not cached, Windows SharePoint Services queries the configuration database to check whether it has moved the site and an automatic correction algorithm allows adjustment of the system.

The Content Database. 

SharePoint uses a  fixed database schema and a fixed number of databases per server to improve scalability.

The Sites table contains the configuration for each site collection represented in the database, it is a default setting that applies to all subsites created within each site collection. The table represents each of the top-level sites for each of the collections of sites as well as the root site and my site in the context of a portal site.

The Webs table contains the settings that apply to each of the sites within a site collection.

The Docs table. stores all documents of all sites in a site collection represented by the database, including, for example, documents in document libraries, attachments, and nodes for each list, and also pages and user default.aspx for all the lists if they are personalized. The contents of the table column DocFlags. contains the value NULL if no pages have been customized. When creating a site for the first time, the column contains the value NULL content for all pages of the site that are in the table because they are not customized,  the page definitions are extracted from schema files physically located on the Web server.

The table Alllists contains one row for each list of all sites in the database. This table contains the settings for each list, which specifies the lists and document libraries that are included in the sites and what scheme is instantiated with each list.

AllUserdata table contains all the list data to elements in user-created sites, each row contains data for each element.

The AllLinks table contains the links used in the repair of links to recalculate.

The WebParts table contains information about all web parts and views in existing Web sites.

 The personalization table stores information about Web Parts customization.

Conclusion: Everything (or almost everything) in SharePoint is stored in the databases, so having a Database back up can save you in a disaster case (One day I’ll tell you about my first disaster with SharePoint… DB saved my life)

Updates to the data done directly from the database is not supported by Microsoft.

People working with SharePoint come from a .NET background, and for most of them when first introduced to SharePoint, they have no idea of the monster they are about to confront.

Specially because they are put in SharePoint projects without a previous training, the result: they end hating SharePoint, and I don’t blame them (I hated SharePoint for a long time, but it’s a fine line between love and hate ).

So here comes a little explanation of the SharePoint Architecture and how it integrates with IIS and ASP.NET.

SharePoint Services are the heart of any SharePoint implementation. It has 3 components: 

• One and ONLY one configuration Database.
• One or more Content Databases
• One or more Web Servers. 

The configuration Database is the heart of any SharePoint farm, it contains the majority of the farm configuration, including web applications configuration, logging and reporting, farm topology, services on servers, antivirus, content databases, authentication and much more.

 The content database stores all the content of the site, including documents in libraries, data in lists, user names and privileges, etc. 

There is one content database per Web Application. The web server will be responsible to receive the user requests, the web server will connect to a database in order to get the data that will be returned and displayed in the client page.

SharePoint Integrates with Internet Information Services (IIS) and ASP.NET. It is designed and implemented on top of IIS and extends the ASP.NET framework:

  When SharePoint is implemented, two virtual servers are created in the IIS. On of them is for SharePoint Administration and the other one for the final user.

For every new Windows SharePoint Services Web application created, one new virtual server will be created in the IIS, it also involves some changes to the file system and the IIS metabase on each front-end Web server. In a Web farm environment, these changes are automatically mirrored across each front-end Web server in the farm by the Windows SharePoint Services runtime.

Once a Web application is created, it is no longer necessary to touch the file system or IIS metabase of the front-end Web server when creating, updating, and deleting sites or site collections. The Windows SharePoint Services architecture makes it possible to provision new sites and site collections simply by adding entries to the configuration database and a content database. It is this aspect of the Windows SharePoint Services architecture that gives it significant management and provisioning advantages over ASP.NET.

For an extended explanation of IIS and ASP.NET concepts related with SharePoint click here:

For an extended explanation of the SharePoint Databases click here.

Conclusion:  SharePoint Services expands and diversifies itself as a Web development platform through integration with. NET Framework on its own functionality. Understanding this architecture will help you determine how to develop applications on the SharePoint.

  I had a meeting today with a client that needed some guidance with the implementation of a solution in SharePoint.

My solution was very simple and it involved the use of SharePoint designer to add the DataFormWebPart and a very basic workflow.

Although it met their requirements, they added something else: “We can not use SharePoint Designer”, I asked why not, and the answer was: “We don’t want to customize pages because we want to avoid unghosting”.

 Ghosting and Unghosting were terms more used in SharePoint 2003.

When a page is added to a SharePoint site, a physical copy of the page is not immediately added to the SQL content DB. Instead, a process called ghosting (now named customization) is used. When a page is created, a record is added to the content DB that points to a file on the SharePoint server’s file system in the 12 hive.

In SharePoint 2007, these pages are called uncustomized pages. Pages from anywhere in a SharePoint Server 2007 farm can all point to a single page in the 12 hive. Changing that one page will change all of the uncustomized pages that point to it.

 When you change a page in a site with SharePoint Designer, the file is not saved to the 12 hive, instead, a full copy of the modified file is stored in the content database. In SharePoint 2003, these files were called unghosted files, but they are now referred as customized files.

 The problem in SharePoint 2003 was that excessive number of unghosted files could pose a performance problem because of the way ASP.NET 1.1 handled page rendering. Changes made to the process in ASP.NET 2.0, which SharePoint 2007 is based on, have removed this performance issue.

 In conclusion, don’t be afraid of unghosting! But do be careful with too much customization because it becomes difficult to maintain!

This is a good info that everybody working with SharePoint should keep in mind:

The following values are recommended for an acceptable performance with SharePoint: 

• Web Applications: 99 per SSP
• Site Collections: 50,000 per web application.
• Zones: 5 per farm.
• Content Databases: 100 per web application.
• Web Sites: 250,000 per site collection. The recommended is 125 sites with 2,000 subsites each.
• Sub Sites: 2,000 per web site.

• Documents: 5 million per document library, over 2,000 elements velocity is slower to present the data in the page.
• Document Size: The default value is 50MG, it can be changed to support until 2GB.
• Lists: 2,000 per web site.
• Metadata: 256 per list.
• WebParts: 50 per page.

• Users in groups: 2 million per web site (using AD)
• User Profiles: 5 million per farm.

• Index Documents: 50 million per index.
• SSP: 3 per farm.

• 8 web servers for each Database server.
• 3 web servers for each domain controller.
• One index server per SSP.
• Servers for excel services: no limit.
• Servers for Queries: no limit.

• Workflows at the same time: 15.

MOSS has a principal css file: core.css.

It is located in the 12 hive (of course!):

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\1033\STYLES\CORE.CSS

If you want to customize SharePoint styles or add your own this is the place, now, don’t plan to open this file and start working on it, remember always to make a copy of the file, rename it, and then work on the copy.

 Once you are done customizing the css file, you have to tell SharePoint to use your file; to do it, open your master page in SharePoint Designer and locate the tag:

 <SharePoint:CssLink runat=”server”/>

 Use the attribute DefaultUrl to specify the location of the file

 <SharePoint:CssLink DefaultUrl=”/_layouts/1033/styles/MyCSS.css” runat=”server”/>

 Done!

 You can also specify an Alternative css file to apply to the pages when you are selecting the master page to use (using the browser).

 The order of how the styles will be applied to the pages is: 

1. Alternative CSS
2. The CSS specified in the SP control
3. CORE.CSS