SharePoint 2007 and InfoPath Form List

Strange thing happened the other day. A user phones and in a panic ask's "All our data is GONE on our list!" Stop the drama!

Anyway, here is the problem:

A Form Library lost all of its custom columns. Nobody deleted it. Opening the form in Infopath showed that the fields were still in the form. But the view did not show it. Not even in the library settings could you see the fields.

By luck, I think, I replicated the problem on our Application Support server.

Saving the List as a template, removed all the form columns in the list. The columns may have been removed from the view and list, but the DATA was still there.

So, here is the solution: Republish the form to the library. Open each item and view the properties. Then that will repopulate the fields .

If you experienced this, please leave a comment.

Who deleted that Web site?

Sometimes Web sites get deleted. If you want to track down who did it, you can check the IIS logs or check the Content DB.

The query to run on the SQL DB is:

SELECT [Id]
      ,[SiteId]
      ,[EventTime]
      ,[ListId]
      ,[ItemId]
      ,[ItemName]
      ,[ItemFullUrl]
      ,[EventType]
      ,[ModifiedBy]
      ,[TimeLastModified]
      ,[EventData]
      ,[FormattedFragment]
  FROM [].[dbo].[EventLog]
where itemfullurl like '%//%' and EventType = '4'

In the LAST row (by Date), the Column ModifiedBy will be the person that deleted the site. Just double check that the ItemFullUrl has ...'Lists/Site Owner/' ... in it. EventType 4 means 'deleted'.

I do not take responsibility for unfair dismissal. ;-)

It is however likely the person that deleted the site.

Copy/Move a Web Site to another Site Collection

Now I am sure all of us in the SharePoint community had to move or copy a site's library and/or lists to another site collection. There are various reasons for this, in our case it was because of governance.

Long story short:
- One content database
- One site collection
- Multitude of web sites.
- Limit on content database size of 100GB (now there is a whole discussion about what size a content database should be and could be, but let's not go there now).

Mission:
- Move a web site (subsite) out of the current site collection to a new site collection in it's own content database.

The easy part is to create the Content Database, add it to the Web Application, create the new blank Site Collection in the Content Database, set the properties of the Content Database to only 1 Site Collection.

If you are lucky, the owners of the Web Site did not implement Workflow, Lookup lists or something else(Features) that can complicate the move.

Then you would probably use STSADM command line and do your stuff.

In my case the users implemented Lookup lists/fields. While this is nice to use in SharePoint, and you should try do design your site with this in mind. It reallllly makes it difficult to move. So that is why governance/design at a higher level is important. Then you *don't* have to move the site.

Anyway, no use crying over spilled milk.

A lookup field is a field that gets a value from another field in another list. You could have multiple lookup fields that uses multiple other lists.

Say you have a MAIN List:

MAIN List
Field A, Field B, Lookup Field C (Lookup Value from LIST 1, Field Z)

LIST 1
ID Field A, Field B, Field Z
1: Value 1, Value 2, Value FFFF
2: Value 2, Value 5, Value AAAA
3: Value 6, Value 1, Value LLLL

You add a record to MAIN:
Value A, Value B, Value LLLL

The lookup field's value in the MAIN List is stored as such: "RowId;#Value", i.e "3;#Value LLLL".

So to move this is not simple. You have to create the Lookup Lists in the Target Site. Import the Values from the Source site Lookup List. Do this for every lookup field. Try to resolve circular Look Ups.

When your Lookup Lists are created and populated then you can start to copy a list with the lookup FIELDS to the target site.

1. Open Site
2. Open Web Site
3. Open List
4. Iterate over List Items
5. Iterate over Item Versions
5. Iterate over Item Fields and copy across to -> TargetSite/WebSite/List/Item/Field
6. If the Field is a Lookup,
6.1 Try to get the Row ID from the Target Lookup List by matching the Source field Value to the arget List Field
6.2 Update the Item[FIELD] to the New ID, and Same Value.

Easy huh? Not by a long shot. It is complicated and it is easy to get confused. Maybe if I have time I will develop my program to be user friendly and so that it can be used for any site.

Don't hold your breath though.

Change .Net Framework for specific Apps IIS 6

Whenever I need to change the .NET settings for a site.

1. Run aspnet_regiis -lk from any .NET framework folder to list your current settings to help you determine which sites should remain using .NET 1.1. If you know there is a .NET 1.1 site, but it is not explicitly listed by this command, then it is inheriting from the root W3SVC/.

2. For all .NET 1.1 sites not explicitly listed by the previous command, you will need to force them to use .NET 1.1:

- Determine the Identifier ID of the site(s) which you want to force to use .NET 1.1. (Through the IIS 6 Manager, you can determine the Identifier of a site by clicking the "Web Sites" folder on the left side of the tool. On the right side, all your sites will be listed, and the Identifier column shows the ID.)
- From the .NET 1.1 framework folder, run aspnet_regiis -sn W3SVC//ROOT/ where is the ID of the site which you want to force to use .NET 1.1.

3. Finally, change the root W3SVC/ to use .NET 2.0 so that all newly created sites will inherit from the root and default to use .NET 2.0. To change the root, from the .NET 2.0 framework folder, run aspnet_regiis -sn W3SVC/.

You can run aspnet_regiis -lk again to verify your settings.

Remember?

O to rember is wonderful. To forget is well, I forget.

Remember: When you have a farm with multiple servers, or the host name(s) differ from the computer name, you have to disable the loopback check or Specify the Host names in the registry.

See: http://support.microsoft.com/kb/896861

O how wonderful it is to remember.

SQL Tips

Need to manage a lot of Databases on various different drives?

Use this query to get information re. Databases on your SQL Server:

select db.dbid
,db.name
,db.filename as DBFileName
,logFile.filename as LogFileName
,convert(decimal(18,2),(db.size*8)/1024.0) as DBSize
,convert(decimal(18,2),(logFile.size*8)/1024.0) as LogFileSize
,d.compatibility_level
,d.recovery_model_desc
From (select dbid,name,filename,size
from sys.sysaltfiles safDBFiles
where safDBFiles.groupid>0) db,
(select dbid,name,filename,size
from sys.sysaltfiles safLogFiles
where safLogFiles.groupid = 0) logFile,
sys.databases d
where logFile.dbid = db.dbid
and
d.database_id = db.dbid