Aparently I have written something on that note before for CMS 4 and it looks like someone still needs it as I got a request for an updated version for it a couple of days ago. So here we go:

for the most part the syntax for the call is equivalent to what is was before so go to my previous article regarding that (check out the old article for details). What I?ve added this time around is:

• the @PropertyName can be declared as ?%? if you want to look in all property names
• @PropertyType can be ?1 if you want to look in all property types otherwise you need to specify type id (this has changed from type name before due to database schema changes)
• additionally this version of the stored proc will only look in the Master language Branch, so it will work for the single language pages and for multi-language but for language agnostic properties. (should you require the language to be variable the change is pretty simple ? I can send you the updated version by email.

/****** Object:  StoredProcedure [dbo].[PagedSearch]    Script Date: 07/07/2009 12:18:10 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PagedSearch]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PagedSearch]
GO

CREATE Procedure PagedSearch
    @Condition varchar(1024),
    @PropertyName varchar(1024),
    @PropertyType int,
    @PageSize int,
    @PageNumber int,
    @Offset int
AS
BEGIN

    DECLARE @RowStart int
    DECLARE @RowEnd int

    SET @RowStart = @PageSize * @PageNumber + @Offset;
    SET @RowEnd = @RowStart + @PageSize + @Offset;

    WITH PageRefs AS
        (SELECT page.pkID as PageId,
            ROW_NUMBER() OVER (ORDER BY pageLang.StartPublish DESC) as RowNumber
            FROM tblPage page, tblProperty propValue, tblPageDefinition propDef, tblPageLanguage pageLang
            WHERE page.pkID = propValue.fkPageID
                AND page.fkMasterLanguageBranchID = pageLang.fkLanguageBranchID
                AND page.pkID = pageLang.fkPageID
                AND propValue.fkPageDefinitionID = propDef.pkID
                AND (@propertyType = -1 or propDef.fkPageDefinitionTypeID = @propertyType) -- is proper type
                AND propDef.Searchable = 1 -- the property is searchable
                AND propValue.String like @Condition -- contains facets
                AND propDef.[Name] like @PropertyName) -- property of proper name
    SELECT PageId
        FROM PageRefs
        WHERE (RowNumber Between @RowStart and @RowEnd) or (@PageSize = 0);
END
GO

However… looking how the schema has changed over time, I am not convinced this approach is really the best one for someone who is not prepared to deal with the changes (e.g. you better be able to change the stored procedure based on the schema changes – or bribe me with pizza and beers for updates :) ).

Additionally this procedure only searches for properties that store their value in Short string field. To make it look into long string you need to Change the highlighted line to.

AND (propValue.LongString like @Condition)

or alternatively to look in both change it to:

AND ((propValue.String like @Condition) or (propValue.LongString like @Condition))

Enjoy!

Have you ever (or have your customers) created and edited a page in one language only to realize that their selected locale was wrong? Have you ever wished you could delete a master language branch of a page  after creating its localized counterpart but you could only delete the newly created slave language instead? Have a customer ever requested that they could copy a whole branch and you convert it to another language so that they could then translate in-place?

Well I have? and I?m sure I will. And so did Fredrikj on the our #epicode IRC channel ;).

Basically I had the tool that would convert from one language to another, but Fredrikj requested something that would switch master language of a page from one to another. Since I?ve already had some of the work done, I?ve updated the stored procedure I?ve written some time ago and slapped a nice GUI up on it. Here?s the result:

What the tool allows you to do is perform either language conversion or master branch switching on a selected page and all of its children (if you choose so).

The stored procedure have been updated to work on CMS5 R2 (will no longer work on R1 ? but if you need that functionality, comment here or give me a shout and I?ll create a compatible version for you).

A word of caution though ? I take no guarantee whatsoever about its operation. Especially, if you wreck your client?s database with it. I did what I could to prevent some of the obvious problems (like switching to a non existing master or converting to an existing one) but I will not be responsible if it won?t work for you. make a database backup and experiment there before you do any changes on the real data. That said ? it works for me, so I think it should also work for you.

You can download the archive containing the tool here. unzip it to your EPiServer web application folder keeping the folder structure or the plugin reference will be wrong. Include the *.aspx and the *.cs files in your project and apply the SQL file to your database (The manipulation is performed by a stored procedure located in the file).

Also if you?re performing the change in a load balanced environment, you may need to restart the other servers once you do the changes. I reset the DataFactory cache, but I am not sure it propagates through to other servers.

We’ve not been talking much about it and that’s partially my fault as well (busy with other projects), but Cognifide has a really cool initiative called Cognifide Labs that we intend to grow over time. The plan is to devote up to 10% company time into side projects that help us grow expertise and allow our devs to dwell into interesting technologies, methodologies and languages and develop their skills.

One of the first projects (that I took part in) is CogniScale – an app that allows FlexiScale users to manage their servers. Here’s the story…

Being the agile company taking part in many EPiServer projects we never seem to have enough environments to test our web-apps and software in general in various scenarios. We find ourselves constantly reinstalling and trying to keep our servers in a state that can can at least remotely be called as stable. After all how many deployments and tearing down of various EPiServer, CruiseControl, TeamCity, SQL Server and other "I need to have" apps can a server take before slowing down to a crawl or collapsing all together (that said I bow before our faithful THOTH for taking all the abuse it does). We definitely needed more servers! And we needed them now!

Early this year we’ve started to talk to the guys at XCalibre that came up with a great idea. What if you could have an unlimited amount of servers available for you at any given time? I mean really what if you could have 0 servers one day and the next day have a rich farm of servers for literally no cost, paying only when you power them up and not paying a bit if you take them down.  This turned out to be quite a project for them that turned to materialize as FlexiScale. (you can read more about it here). Looking at all that I’ve mentioned before while eliminating the cost of maintaining the servers locally we decided to give FlexiScale a spin.

Read the rest of this article »

…with an unconventional approach to data fetching.

This article is a first of a series describing the faceted navigation system for EPiServer that we have internally developed in Cognifide and that’s already proven to be a robust solution for delivering tagged content a heavy traffic site, which will be released shortly as an open source project. The article outlines some pitfalls of EPiServer that we’ve run into and the nature of the project in which the module was used first and which influenced a lot of our design decisions.

This article and the Faceted Navigtation module is developed on EPiServer 4.61 and not the latest version 5 of the CMS so far, so mind that some of my reservations may not be a problem if you’re just starting to work on a brand new project and have the luxury of using new features of it.

Also (which may be a good thing) our sites uses a different approach to navigation the content, we do not really care much for the tree structure, but we treat all EPiServer pages equally when looking for content because of how the site is designed from the creative point of view.

Read the rest of this article »

I’ve been on a sole task today to improve a database performance of the project we’ve been working on. As much as I enjoyed the task there is one thing that costed me quite a bit of head scratching. Being a standard nerd I usually have about 20-30 applications running on my machine at a time, I didn’t immediately associated the error with the tuning advisor but rather assumed it’s one of my other 29 applications/servers/daemons/services in background did something stupid, probably even some of my code craving for a bit of attention, right? So I moved to tune the database on the server via remote desktop, but when I hit a wall there I’ve looked around for the solution and it looks like that it’s been known to MS for almost 2 years now.

Found it hilarious enough though to post it here though :)

“This indicates a bug in your application”, how vicious of them show a dialog like this to a developer. It’s not in MY code you bad thing you! It’s in YOUR code!

The solution or rather a workaround is available here. And NO, you don’t need to restart your computer after applying the change to the registry, just kill and restart Explorer and then start the Tuning advisor again. Make sure to restart the SQL Server Management Studio as well if you’re launching the tuning advisor form it, the setting is applied on app startup.

Searches paged in the database have posed a problem in SQL Server at least prior to version 2005. I’ve found some solution to the problem on the net but they were so cludgy that I would never really put anything like that in a production server. I hope the following will shed some light on how they work in general by using in in EPiServer context.

Theoretically in EPiServer you can pull the pages that match the criteria from the database with EPiServer.Global.EPDataFactory.FindPagesWithCriteria() into the PageList but that seemed to be imposing a strong performance penalty with increased number of pages meeting the criteria. Since this search is sometimes done even multiple times on a page request in our project we needed something better.

Read the rest of this article »