Is there any way to run part of a SQL azure statement outside a transaction scope

Asked Sep 19 '17 at 23:17

Active Sep 19 '17 at 23:17

Viewed 90 times

I need to create a fulltext index for a database deployment and the deployment process involves a versioning software (flyway, for those interested) which runs everything in a transaction. T-SQL does not allow this in a transaction.

The "proper" solution here is to get flyway to run my updates outside of a transaction. It might have that capability, but there is no documentation nor examples. You can see the relevant GitHub issue here

I want to try to bypass this issue altogether and just hack my SQL updates into the current process, which lead my to this post, the answer to which explains that a loopback linked server can execute a remote procedure call from within a transaction but not carry the transaction scope to that execution.

Sounds like exactly what I want but now I am confronted by no support for sp_addlinkedserver in SQL Azure.

Does anyone know a way to run an autonomous transaction in sql azure?

asked Sep 19 '17 at 23:17

Mr. Em

1

Does it need to be in an autonomous transaction or can you commit the previous one, do your thing then open a new one? – Martin Smith Sep 19 '17 at 23:35
I did attempt that just by committing before my code and opening a new transaction after creating the index but I received the same error as before – Mr. Em Sep 19 '17 at 23:50
What's the error text? – Xedni Sep 20 '17 at 01:29
`CREATE FULLTEXT INDEX statement cannot be used inside a user transaction.` – Mr. Em Sep 20 '17 at 15:49

Is there any way to run part of a SQL azure statement outside a transaction scope

0 Answers0