Why does dropping a table not work with SQLServer unless a Transaction is used?

Phil

I am running a Spring Java Web Application which can communicate with a database. The type of database is selectable by the user and can be MySQL, Oracle or SQLServer.

I don't understand why the following code fails to drop the table when using SQLServer ONLY (it works for Oracle and MySQL):

public void dropTables(String... tableNames) {
    Session session = localSessionFactory.getObject().openSession();
    try {
        dropTables(session, tableNames);
    } finally {
        session.close();
    }
}

private void dropTables(Session session, String... tableNames) {
    for(String currentTable : tableNames) {
        SQLQuery createSQLQuery = session.createSQLQuery("drop table "+currentTable);
        createSQLQuery.executeUpdate();
    }
}

The table fails to drop. It is as if the Drop SQL Query didn't run. The table still exists and retains its contents. The return value from the call to executeUpdate is 0.

However if I modify the code by adding a Transaction around the drop statements, then the drop succeeds on SQLServer:

public void dropTables(String... tableNames) {
    Session session = localSessionFactory.getObject().openSession();
    Transaction tx = null;
    try {
        tx = session.beginTransaction();
        dropTables(session, tableNames);
    } finally {
        tx.commit();
        session.close();
    }
}

private void dropTables(Session session, String... tableNames) {
    for(String currentTable : tableNames) {
        SQLQuery createSQLQuery = session.createSQLQuery("drop table "+currentTable);
        createSQLQuery.executeUpdate();
    }
}

Why do I have to put the Transaction around it? I thought that since DROP is a DDL statement the transaction is not necessary. Can anyone explain this for me?

a_horse_with_no_name

Unlike Oracle and MySQL, SQL Server supports transactional DDL. That means you have to commit DDL statements - unless your connection is configured to use auto-commit.

Transactional DDL is a very nice feature because it allows for "all-or-nothing" migration scripts. A rollback will also rollback a drop table!

Transactional DDL is nothing special to SQL Server. Postgres, DB2 and Firebird also support this (just to name a few).

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Sqlserver PIVOT to turn a "reconstruct" a flat table into columns - why does this not work?

From Dev

SQL Server - Does dropping table will recover the used space

From Dev

Does dropping a database have to be done not in any transaction?

From Dev

Ubuntu - does not allow to write in disk, in the middle of work, after restart it does the same unless its rebooted and used F

From Dev

Ubuntu - does not allow to write in disk, in the middle of work, after restart it does the same unless its rebooted and used F

From Dev

Schema 'SA' does not exist and dropping table

From Dev

Python script does not work unless run with sudo

From Dev

Why does the javascript I used to show/hide divs not work in IE?

From Dev

findOneAndUpdate does not update unless I call ".then()". Why?

From Dev

Hive dropping Internal table does not delete the warehouse files of that folder

From Dev

Hive dropping Internal table does not delete the warehouse files of that folder

From Dev

Why does dropping an index take longer than creating it?

From Dev

Why does my cpu's clockspeed keep dropping?

From Dev

Why does my cpu's clockspeed keep dropping?

From Dev

Why does Bootstrap Tooltip not work correctly when placed inside a table?

From Dev

Why does not display:block and 100% width work on table rows?

From Dev

why does sorting a table in Lua doesn't work

From Dev

Why does my jQuery slidedown not work in my table?

From Dev

Why does Bootstrap Tooltip not work correctly when placed inside a table?

From Dev

WHY does this simple Hive table declaration work? As if by magic

From Dev

Why Common table expression does not work with INSERT SELECT

From Dev

Why does this create table SQL work in MSSQL Server but not on MySQL?

From Dev

Angular : Why does sort only work on first Mat Table?

From Dev

Why does coercing a column in a data.table with by not work while coercing without does work without warning?

From Dev

why the code below wouldn't work unless the array is Integer[], not int[]

From Dev

Javascript Integer increment does not work unless called from function

From Dev

finishActivity() does not work unless BACK is pressed after selecting the submit button

From Dev

VbaProject.OTM does not work unless VB Editor opened once

From Dev

Hibernate NOWAIT does not work with SQLServer2008

Related Related

  1. 1

    Sqlserver PIVOT to turn a "reconstruct" a flat table into columns - why does this not work?

  2. 2

    SQL Server - Does dropping table will recover the used space

  3. 3

    Does dropping a database have to be done not in any transaction?

  4. 4

    Ubuntu - does not allow to write in disk, in the middle of work, after restart it does the same unless its rebooted and used F

  5. 5

    Ubuntu - does not allow to write in disk, in the middle of work, after restart it does the same unless its rebooted and used F

  6. 6

    Schema 'SA' does not exist and dropping table

  7. 7

    Python script does not work unless run with sudo

  8. 8

    Why does the javascript I used to show/hide divs not work in IE?

  9. 9

    findOneAndUpdate does not update unless I call ".then()". Why?

  10. 10

    Hive dropping Internal table does not delete the warehouse files of that folder

  11. 11

    Hive dropping Internal table does not delete the warehouse files of that folder

  12. 12

    Why does dropping an index take longer than creating it?

  13. 13

    Why does my cpu's clockspeed keep dropping?

  14. 14

    Why does my cpu's clockspeed keep dropping?

  15. 15

    Why does Bootstrap Tooltip not work correctly when placed inside a table?

  16. 16

    Why does not display:block and 100% width work on table rows?

  17. 17

    why does sorting a table in Lua doesn't work

  18. 18

    Why does my jQuery slidedown not work in my table?

  19. 19

    Why does Bootstrap Tooltip not work correctly when placed inside a table?

  20. 20

    WHY does this simple Hive table declaration work? As if by magic

  21. 21

    Why Common table expression does not work with INSERT SELECT

  22. 22

    Why does this create table SQL work in MSSQL Server but not on MySQL?

  23. 23

    Angular : Why does sort only work on first Mat Table?

  24. 24

    Why does coercing a column in a data.table with by not work while coercing without does work without warning?

  25. 25

    why the code below wouldn't work unless the array is Integer[], not int[]

  26. 26

    Javascript Integer increment does not work unless called from function

  27. 27

    finishActivity() does not work unless BACK is pressed after selecting the submit button

  28. 28

    VbaProject.OTM does not work unless VB Editor opened once

  29. 29

    Hibernate NOWAIT does not work with SQLServer2008

HotTag

Archive