Menu
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?
611
Right-click the index that you want to modify and then click Properties. In the Index Properties dialog box, make the desired changes. For example, you can add or remove a column from the index key, or change the setting of an index option.
In Oracle ALTER INDEX statement is used to change or rebuild an existing index. Prerequisites : The index must be in your own schema or you must have to ALTER ANY INDEX system privilege. To execute the MONITORING USAGE clause, the index must be in your own schema.
To create a full text index choose your table and right click on that table and select “Define Full-Text Index” option. Now select Unique Index. It is compulsory that for “Full Text Index” table must have at least one unique index. Select columns name and language types for columns.
If you have an index on every column, the cost is additional storage space and a little bit of overhead on inserts and updates. When you insert a new record into your table with 60 indexed columns, you also have to create the index entries for those 60 columns.
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.
162
Fulltext catalogs and indexes and a few other statements can't be changed in a transaction.
See list of disallowed statements on: http://msdn.microsoft.com/nl-nl/library/ms191544(v=sql.105).aspx
I think there is no work around.
30
answered Sep 22 '22 00:09
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
