Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Non-transactional RECONFIGURE statement not working in SQL Server #2082

Closed
JLopeDeB opened this issue Jul 16, 2018 · 1 comment
Closed

Non-transactional RECONFIGURE statement not working in SQL Server #2082

JLopeDeB opened this issue Jul 16, 2018 · 1 comment

Comments

@JLopeDeB
Copy link

@JLopeDeB JLopeDeB commented Jul 16, 2018

Which version and edition of Flyway are you using?

5.1.4 (but it hasn't worked with previous versions, using it since 5.1.1)

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Command-line

Which database are you using (type & version)?

Microsoft SQL Server 2014 (SP2-CU10-GDR) (KB4052725) - 12.0.5571.0 (X64)

Which operating system are you using?

Windows 10 (Flyway), Windows Server 2008 R2 (SQL Server)

What did you do?

Tried to migrate scripts containing non transactional commands and DDL and it failed. I tried with FullText Catalogs, Linked Servers, and the last time with sp_configure. I have flyway.mixed=true in my config file, but also tried by adding -mixed=true to the command line.

Command:
PS C:\repos\cpp> flyway -n -configFiles="conf/flyway_Desa.conf" -mixed=true migrate

V2018.07.16.1036__Migrar_Datos_dbo_Proveedores.txt

What did you expect to see?

Successful migration.

What did you see instead?

Output (partial)

Migration V2018.07.16.1036__Migrar_Datos_dbo_Proveedores.sql failed
SQL State : S1000
Error Code : 574
Message : No se puede usar la instrucción CONFIG dentro de una transacción de usuario
Location : C:\repos\CPP\Datos\V2018.07.16.1036__Migrar_Datos_dbo_Proveedores.sql (C:\repos\CPP\Datos\V2018.07.16.1036__Migrar_Datos_dbo_Proveedores.sql)
Line : 22
Statement : EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE

Worth to mention that I haven't been able to run non-transactional code ever. In this case, the code is mixed with something else but in other cases it wasn't. For everything else, I find Flyway an excellent tool and I'm super happy with it. Thanks for it!

EDIT: I forgot to mention that I ran a trace on it, and I was able to see that Flyway always use SET IMPLICIT_TRANSACTIONS ON, no matter if mixed migrations is enabled or not. Setting the option OFF explicitly in the code didn't work either.

@axelfontaine axelfontaine changed the title Non-transactional migrations not working in SQL Server Non-transactional RECONFIGURE statement not working in SQL Server Aug 2, 2018
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Aug 2, 2018

The issue in this case is the RECONFIGURE statement. This has now been fixed.

@axelfontaine axelfontaine added this to the Flyway 5.2.0 milestone Aug 2, 2018
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Aug 2, 2018
dohrayme pushed a commit to dohrayme/flyway that referenced this issue Feb 3, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants