Why does calling full text search in SQL Server 2008 fail when inside transaction scope?

Question

The following code:

var foo = Users.Join(
                tvf_SearchUsers(queryString),
                u => u.User_Id,
                s => s.User_Id,
                (u, s) => u);

Selects users that match the query string based on a table valued function (tvf_SearchUsers), which utilises full text search. This code snippet is part of a larger method which works successfully when used in production. However, when I call the same method as part of an MBUnit test, which has a rollback attribute, the search fails to return the expected results. If I remove the rollback attribute, it works correctly. The unit test creates dummy users etc which are then cleaned up by the rollback. It seems that these created items are not being seen by the table valued function, as though it were outside the transaction scope. Does anyone know how to get around this?

Updated

If I replace the full text search functionality with a simpler query in the tvf the operation proceeds as expected. So the issue has something to do with the full text search. I'm using SQL Server 2008 so afaik SQL Server should be able to keep the full text query in transaction scope.

Answer 1

It is probably due to the full text search / transaction combination. I don't think the full text search will see the changes when you are under a transaction if it is sql 2005. Check this post http://blog.stackoverflow.com/2008/11/sql-2008-full-text-search-problems/, when it is mentioned that sql server 2008 now works with transactions on full text search and that caused some issues to SO some time ago.