Full-Text population initiation in transaction

• Slawek Guzek

  Ten Centuries

  Points: 1069

  More actions

  September 10, 2010 at 12:43 pm

  #226802

  Hi All,

  Here is the story and config:

  Tables A contains large amount of data, and have 3 fields which are full-text indexed. Two fields are of varchar(250), third one is of type of image, but it contains text data.

  Table C controls population on Full-Text indexes in a quite unusual way - it has AFTER UPDATE trigger defined, and when application updates some field in table C, then table C trigger fires and executes ALTER FULLTEXT INDEX ON A START INCREMENTAL POPULATION.

  So far so good, everything works fine on both SQL2005 and SQL2008 servers, however it requires to have COMMIT TRAN just before ALTER INDEX in order to make things working. This is fine as ALTER FULLTEXT INDEX is the last statement in trigger code. SQL2005 also requires BEGIN TRAN after ALTER FULLTEXT INDEX statement.

  Without COMMIT TRAN command any update to table C throws an error: ALTER FULLTEXT INDEX statement cannot be used inside a user transaction.

  Now I had to add another table B working on the same basis - full text population is controlled by the same code in trigger, the only difference was addition of second ALTER FULLTEXT INDEX statement. Now at the end of trigger code there are two ALTER FULLTEXT statements:

  COMMIT TRAN

  ALTER FULLTEXT INDEX ON A START INCREMENTAL POPULATION

  ALTER FULLTEXT INDEX ON B START INCREMENTAL POPULATION

  The problem is that with the addition of second ALTER FULLTEXT INDEX statement solution stopped working. Now each time the trigger is executed it throws an error ALTER FULLTEXT INDEX statement cannot be used inside a user transaction.

  I got stuck and don't know how to solve this. The Error is thrown only if two ALTER FULLTEXT INDEX statements are executed, code with single ALTER FULLTEXT INDEX works fine, no matter if table's A or B fultext index is altered.

  What is interesting the above code with two ALTER FULLTEXT INDEX statements in the trigger works perfectly fine in SQL2005

  I did tried adding second COMMIT TRAN before second ALTER - no success 🙁

  Both table A and B have timestamp column, but I cannot rely on automatic change tracking as both tables have image type column which I cannot get rid of. Also with automatic changes tracking inserting large amount of data to table A or B takes long time - too long for my application. It is much faster to insert the data, and then initiate incremental or even full population.

  I also cannot make separate connection to the server just to launch ALTER FULLTEXT INDEX statements. I cannot change image column to varchar(max). It is done via trigger as there is a closed application working on tables A, B and C and.

  Any help or clue please.

  Regards,

  Slawek