Sander's Blog

Easier than uploading by hand, create a batch file in the folder containing your udcx files and add these lines:

for /f "tokens=*" %%a in ('dir /b "*.udcx"') do (
stsadm -o adddataconnectionfile -filename %%a -overwrite true[default]/false -webaccessible true/false[default] -category "Category1" 
)

The -overwrite, -webaccessible and category params are optional.

More info on the stsadm command: http://technet.microsoft.com/en-us/library/cc263067.aspx 

This can be a real time-saver! Do you know of the concept of Snapshots in SQL Server 2005? No? It is one of the new features available in the Enterprise edition of SQL 2005.

Database Snapshots allows you to create a read-only copy of your database that can be used for other purposes, such as reporting, auditing or recovering data. Currently, you can do that by running a backup and restoring it to another database. However, the big advantage you gain by using Database Snapshots instead is the speed at which a snapshot occurs, as well as the ability to create multiple snapshots at different points in time quickly.

If you want to read more about the concept, the advantages and the disadvantages you can read the great post of Greg Robidoux at:
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1176142,00.html#cons

For SharePoint development it is a great way to test some configuration, and if you changed your mind, roll back all the changes you've made in just a few seconds! This can be handy for testing scripts, for manual configuration in the UI, or for example to give demo's.

The following scripts create and restore a snapshot on a SharePoint Content Database (please see the limitions at the bottom of this post)

Creating the snapshot

To create the snapshot you can run the following T-SQL (run a new Query dialog in SQL Server Management Studio or create a .sql file)

CREATE DATABASE WSS_Content_Snapshot ON — [The name of the snapshot]
(
    NAME = WSS_Content_Snapshot, — [The name of the logical filename]
    FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataSnapshotsWSS_Content_Snapshot.ss'  — [The filename and location of the snapshot]
)

AS SNAPSHOT OF WSS_Content — [The original Content Database]

Restoring 

Restoring a database is as easy as the following line:
RESTORE DATABASE WSS_Content FROM DATABASE_SNAPSHOT = 'WSS_Content_Snapshot'

You might run into an error if other users (SharePoint processes) are still connected to the database: 

Database state cannot be changed while other users are using the database 'WSS_Content'
RESTORE DATABASE is terminating abnormally.

To avoid this you can first run a script to kill all existing processes:

– First, kill all active processes
DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'WSS_Content'

DECLARE @SQL varchar(max)
SET @SQL = ''

SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';' FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

EXEC(@SQL)

– Second, Restore the database from the snapshot
RESTORE DATABASE WSS_Content FROM DATABASE_SNAPSHOT = 'WSS_Content_Snapshot'

Limitions

Please note that the snapshot above is limited to the Content Database, the changes you can restore include:
- Adding/Deleting/Editing Subsites
- Adding/Deleting/Editing Lists
- Adding/Deleting/Editing List-items
- Adding/Deleting/Editing Content Types
- Adding/Deleting/Editing Webparts
- Enabling/Disabling Features
- Adding/Deleting/Editing Workflows
- Editing the Navigation
- Editing the Sitecollection in the Site Settings (not deleting or creating sitecollections!)
- …

Some things you cannot do:
- Sitecollections are registered in the configuration database, if you add or delete sitecollections you must ensure you also snapshot the configuration database
- Solution packages (wsp files) are registered at the Farm level, if you add or delete solution packages you must ensure you also snapshot the configuration database

This is not an exhausting list, please beware of any configuration done at the Farm level since this is registered in the configuration database. If you really want to snapshot your whole environment I suggest you lock all databases, create snapshots of all databases and restore all databases at the same time. You might want to try some variations, but I would not recommend using it in any environment containing critical data since it is not supported my MS. 

Today I received a message from our Database admin concerning the Temp Database. It seems a SharePoint process is filling the TempDB up to 15gb. After that it reaches it physical limit (disk space is very limited..) and throws an exception:

"The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"

From this moment on you can consider your SQL server to be crashed, since probably every application uses the Temp database and every query using the TempDB will result in an error.

If you follow the suggestion given in de event-log exception you're going to check the log_reuse_wait_desc, in my case this quotes: "ACTIVE_TRANSACTION".
In the SQL Server Activity Monitor you can see the current application using the TempDB is "Windows SharePoint Server". The details of this activity shows that there is a MOSS Crawler stored procedure (forgot to copy the name) running.

Usually the crawler will use the Temp DB while indexing, it will then flush this data to the file system and clear the Temp database. I don't know what triggers this 'flush', but I assume it flushes between different sitecollections. Why this assumption?
1. Because I've only got 1 (extremely large) sitecollection to index and the Temp database never shrinks in the crawl process, it just grows until it crashes.
2. Because I can't find any other information about this process..

When you will stop a crawl (Manage Content Sources -> Stop Crawl) it will wait until the current data is flushed, in my case  (1 extremely large sitecollection) this means it will wait until it crashes. After it crashes you'll have to empty the Temp database manually. 

I'm now in the process of telling SharePoint not to do the crawling. This is not easy since it must complete the current crawl before it can flush the data and inherently stop crawling. You can't even stop the "Microsoft Office SharePoint Search" service, it will tell you it's busy. To stop this thing you will really need to kill the MSSearch process (I don't advice you to do this, but it is a last resort..).

After you've killed the process it is automatically started again by the "Microsoft Office SharePoint Search" service. Workaround is to disable the service and then kill the process. 

Now you might think you can re-configure or re-schedule the Content Source, wrong! Since you've stopped the "Microsoft Office SharePoint Search" service, you cannot use the Search Settings pages in your Shared Service Provider. 

Once you will re-start the "Microsoft Office SharePoint Search" service it will automatically continue the Crawl, even before you can stop it. 

Following question will now be; how can I stop this full Crawl? As far as I found I've got 2 options left:
1. Give the Temp database more space and wait for the crawl to be finished
2. Hack in the database to remove the current Crawl according to this thread: http://msmvps.com/blogs/obts/archive/2006/12/18/432542.aspx
3. …

Of course I opt for the first one which is obviously the most healthy and logical option. 

I hope you will never need it, but if you will you now have my experience at your dispense..

(Sorry for this post being Dutch. Tonight I presented Nintex workflow at the Dutch Information Worker Group. In this post I will publish the sheets and code I've used)

In navolging tot de Nintex Workflow presentatie kan je nu de slides downloaden van m'n blog.

Ik heb de presentatie bijgewerkt met:
- Een aantal dingen die ik vergeten ben
- Toelichtingen en details over de demo's
- Verwijzigingen naar help-files en de Nintex SDK
- Contact gegevens 
- De cut-scenes: sheets die de presentatie niet gered hebben..

De code voor een Custom Activity project kan je eveneens downloaden van m'n blog. Gebruik hierbij de Nintex SDK om dit voorbeeld te installeren en aan te passen.

Ik wil jullie via deze weg nogmaal bedanken voor de grote opkomst en de interesse. Vragen kan je uiteraard kwijt in de comments.

Sander

This morning I had a conversation with a colleague about Schemalogic. This tool enables you to manage your content types in a central location for your whole farm or even multiple farms.

Based on this concept I thought it might be cool to make a 'master' sitecollection with all site-columns and content-types and reflect those to all other site collections. Don't ask me why I don't use features or other concepts, we're currently evaluating the different possibilities..

This is my quick-and-dirty script to sync the Site Columns and Content Types between sites. It does te following:

1. Create Site Columns if they don't exist
2. Update Site Columns if the are different
3. Create Content Types if they don't exist
4. Add/Remove Site Columns to existing Content Types
5. Remove Site Columns if they are removed from the master site

A number of questions arise when building the script:

- How to handle lookup fields, since they link to a local list. We could ignore these fields or assume the list exists..
- It should remove Content Types if they are removed from the master site (not very complex, just forgot it in this version)
- It should iterate through all site collections instead of just one

Well, I'll continue evaluating the other possibilities, but we'll might go for this method. In meanwhile you are free to use the (beta)code I've written for this concept. Please feel free to comment your findings and suggestions!

static void Main(string[] args)
{
    SPSite sourceRoot = new SPSite("http://wss3dev/sites/TEST2");
    SPWeb sourceWeb = sourceRoot.OpenWeb();

    SPSite targetRoot = new SPSite("http://wss3dev/sites/TEST3");
    SPWeb targetWeb = targetRoot.OpenWeb();

    SyncSiteColumns(sourceWeb, targetWeb);
    SyncContentTypes(sourceWeb, targetWeb);
    DeleteSiteColumns(sourceWeb, targetWeb);
}

/// <summary>
/// Syncs content types based on a sourceweb and a targetweb.
/// Add's Content Types if they don't exist.
/// Add's/Removes Fields in existing Content Types
/// </summary>
/// <param name="sourceWeb"></param>
/// <param name="targetWeb"></param>
private static void SyncContentTypes(SPWeb sourceWeb, SPWeb targetWeb)
{
    foreach (SPContentType sourceType in sourceWeb.ContentTypes)
    {
        if (!sourceType.ReadOnly && !sourceType.Sealed && !sourceType.Hidden) // Exclude non-editable types
        {
            bool update = false;

            if (targetWeb.ContentTypes[sourceType.Name] == null)
            {
                SPContentType parentContentType = targetWeb.ContentTypes[sourceType.Parent.Name];
                SPContentTypeCollection collection = targetWeb.ContentTypes;

                SPContentType newContentType = new SPContentType(parentContentType, collection, sourceType.Name);
                targetWeb.ContentTypes.Add(newContentType);

                newContentType = targetWeb.ContentTypes[sourceType.Name];

                foreach (SPField field in sourceType.Fields)
                {
                    if (newContentType.FieldLinks[field.InternalName] == null)
                    {
                        newContentType.FieldLinks.Add(new SPFieldLink(targetWeb.Fields[field.Title]));
                    }
                }

                newContentType.Update(false); // Do not update children, there are no children yet
            }
            else
            {
                SPContentType targetType = targetWeb.ContentTypes[sourceType.Name];

                foreach (SPField sourceField in sourceType.Fields)
                {
                    if (targetType.FieldLinks[targetWeb.Fields.GetFieldByInternalName(sourceField.InternalName).Id] == null)
                    {
                        targetType.FieldLinks.Add(new SPFieldLink(targetWeb.Fields.GetFieldByInternalName(sourceField.InternalName)));
                        update = true;
                    }
                }

                for (int i = (targetType.FieldLinks.Count - 1); i >= 0; i--)
                {
                    if (sourceType.FieldLinks[targetType.FieldLinks[i ].Name] == null)
                    {
                        targetType.FieldLinks.Delete(targetType.FieldLinks[i ].Name);
                        update = true;
                    }
                }

                if (update)
                    targetType.Update(false);
            }
        }
    }
}

/// <summary>
/// Syncs Sitecolumns based on a sourceweb and targetweb
/// Updates a sitecolumn if it is different.
/// Add's a sitecolumn if does not exist.
/// </summary>
/// <param name="sourceWeb"></param>
/// <param name="targetWeb"></param>
private static void SyncSiteColumns(SPWeb sourceWeb, SPWeb targetWeb)
{
    foreach (SPField field in sourceWeb.Fields)
    {
        if (!targetWeb.Fields.ContainsField(field.Title))
        {
            targetWeb.Fields.AddFieldAsXml(field.SchemaXml);
        }
        else
        {
            SPField targetField = targetWeb.Fields.GetFieldByInternalName(field.InternalName);

            if (targetField.SchemaXml != field.SchemaXml)
            {
                targetField.SchemaXml = field.SchemaXml;
                targetField.Update(true);
            }
        }
    }

    targetWeb.Update();
}

/// <summary>
/// Removes sitecolumns from the target-site which do not exist in the source-site
/// </summary>
/// <param name="sourceWeb"></param>
/// <param name="targetWeb"></param>
private static void DeleteSiteColumns(SPWeb sourceWeb, SPWeb targetWeb)
{
    for (int i = (targetWeb.Fields.Count - 1); i >= 0; i--)
    {
        if (!sourceWeb.Fields.ContainsField(targetWeb.Fields[ i].Title))
        {
            targetWeb.Fields.Delete(targetWeb.Fields[i ].Title);
        }
    }
}

If you add Site Columns to a site or web using code you can do it like this:

SPSite root = new SPSite("http://wss/sites/site");
SPWeb web = root.OpenWeb();
string fieldname = web.Fields.Add("TEST", SPFieldType.Text, true);

After adding a column this way my Site Gallery page (mngfield.asp) broke with the following error:

Object reference not set to an instance of an object.   at Microsoft.SharePoint.ApplicationPages.FieldListRenderer.Render(HtmlTextWriter output)
   at System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter)
   at System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter)
   at System.Web.UI.Control.RenderControl(HtmlTextWriter writer) 
…..

The cause of this is the case sensitivity when using the Object Model to open site objects. The Site Gallery page uses the FieldListRenderer control to display all Site Columns. In the Render method (by using Reflector) you'll find a piece like:

SPWeb web = allWebs[field.Scope];
string title = web.Title;

In this case field is the actual Site Column and field.Scope stores the URL as it was once used to construct the parent web. In our code snippet it would contain "http://wss/sites/site". This value is used to get the web from allWebs, and this method turns out to be case sensitive…!!

In my case the site was created with the url: http://wss/sites/Site. So the allWebs method returns null and the web.Title causes an Object reference exception.

By using the right casing everything was fixed in a second:

SPSite root = new SPSite(http://wss/sites/Site);
SPWeb web = root.OpenWeb();
string fieldname = web.Fields.Add("TEST", SPFieldType.Text, true);

Installing the ServerAdminTemplates and creating sites is a lot easier and quicker by using these scripts:

 1. Download the package from http://www.microsoft.com/downloads/details.aspx?familyid=AAE4CB5A-91D2-4F1B-9A45-3BB894E218F8&displaylang=en

2. Unpack it to C:Temp

3. Create and run a .bat file in C:Temp with this script to deploy the solutions (thanks to Adnan for this part):

Indexed columns are a great way to improve performance. Especially the SiteMapProviders (used by a CQWP) benefit from the indexes. I know there are some performance-tests out there, but just try to index every field you use in a Content Query Webpart and you'll know the difference. 

It's a shame not every fieldtype can be indexed. In my experience only Text, Number, Date and Choice can be indexed. So what about Lookups, Calculated values or Custom fieldtypes?

My workaround is to copy the values of those field types to a plain textfield and to index those textfields. This can easily be done by a workflow or an event handler:

public override void HandleEvent(SPItemEventProperties properties)
{
    if (properties.EventType == SPEventReceiverType.ItemUpdated || properties.EventType == SPEventReceiverType.ItemAdded)
    {
        SPListItem item = properties.ListItem;

        // Copy contents of the Lookup to a text field for indexing
        SPFieldLookupValueCollection values = item["LOOKUP"] as SPFieldLookupValueCollection;
        if (values != null)
        {
            foreach (SPFieldLookupValue value in values)
            {
                item["LOOKUP_INDEXED"] += ", " + value.LookupValue;
            }

            // update the item without user/time-stamp 
            item.SystemUpdate(false);
        }
    }
}

Took me a while to find out how to connect to the Site Gallery, so this might save you some time.

using (SPSite root = new SPSite([yourRootURL]))
{
    // DELETE OLD TEMPLATES
    List<string> toDelete = new List<string>();

    using (SPWeb web = root.OpenWeb())
    {
        SPFolder folder = web.Folders["_catalogs"];
        SPFolder subfolder = folder.SubFolders["wt"];

        using (SPWeb web = root.OpenWeb())
        {
            SPFolder folder = web.Folders["_catalogs"];
            SPFolder subfolder = folder.SubFolders["wt"];

             int numberOfTemplates = subfolder.Files.Count;

             for (int i = (numberOfTemplates - 1); i >= 0; i--)
             {
                 subfolder.Files.Delete(subfolder.Files.Url);
             }
        }
    }

    // SAVE SITE AS TEMPLATE
    using (SPWeb web = root.OpenWeb([yourSiteUrl]))
    {
        web.SaveAsTemplate("[Filename]", "[Title]", "[Description]", true/false);
    }
}

Credits to Serge for pointing me in the right direction: http://weblogs.asp.net/soever/archive/2006/06/28/NAnt-task-for-SharePoint_3A00_-Save-SPWeb-as-site-template-to-the-filesystem.aspx

The new Multi-Value fields are causing me quite some headaches lately. Exporting to Excel does not work, showing in Content-Editor-Webparts does not work, SharePoint Designer does not understand them, and now there's an issue querying them trough the Search Engine.

The SDK describes Multi-valued (ARRAY) Comparisons in Windows SharePoint Services Search SQL Syntax, but when using it it returns an error. The problem lies in SharePoint not recognizing an Multi-Value Lookup as Multi-valued.

You can use the MetaData Property Mappings page to check whether an Site Column gets indexed as a Multi-Value column:

I used an out-of-the-box environment with SP1. I've made a Content Type and added/created some Site Columns of different Multi-Value types:
- Multi Choice field
- Multiline text field (I know, this is just a blob, but I wanted to be sure..)
- Multiline text field with unlimited length
- Multi-Value Lookup field
- Multi-Value Lookup field with unlimited length
- Multi-Value People field

Except for the Multi Choice field none of them gets recognized as a Multi-Value field. I even tried to create a piece of content based on the content type with Multiple selections made, before I let the Index server crawl the content and discover the new Site Columns. If it doesn't see it by then it never wil. So were stuck using LIKE operators, which will probably not do the performance any good.  

Anyone with the same experience or a workaround?