views:

186

answers:

1

I'm writing an SQL data updater to convert old text/ntext/image type columns into varchar/nvarchar/varbinary. The updaters are run within transactions when the system boots to update the database from an older version. I've gotten the SQL to work on its own, but a handful of the columns being changed were full-text indexed, which means I can't alter their type without first dropping the index, like so:

ALTER FULLTEXT INDEX ON Table DROP (Column)
exec dbo.ConvertDataType 'Table', 'Column', 'nvarchar(max)'
ALTER FULLTEXT INDEX ON Table ADD (Column)

Problem is that the first line does not work in the data updater because of the error "ALTER FULLTEXT INDEX statement cannot be used inside a user transaction." Is there any way to make this happen within the transaction? Either by making this code work, or changing the index to the new type in some other way?

+1  A: 

No. Many DDL changes don't work in a transaction context because they cannot guarantee rollback. As long as the statement just modifies some internal database metadata there is a chance is supported inside a transaction as a rollback is handled by ordinary database tables updates rollback. But statements that create files, open sockets, connect to external services (like fulltext indexing service) cannot rollback so they are not supported inside transactions.

Remus Rusanu