SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can SQL Server perform like Oracle's DDL/DML?

    Hi there,

    I have a SQL script like this which is run in a batch with implicit_transactions ON:

    CREATE TABLE dbo.w2EDRMSMetaData
    (
    w2EDRMSMetaDataID integer IDENTITY(100,1) NOT NULL,
    CallTypeCode varchar(50) NOT NULL,
    tagIdentity varchar(255) NOT NULL,
    tagname varchar(255),
    tagdescription varchar(255),
    MergeTag varchar(255)
    )
    GO


    CREATE TABLE dbo.W2ProcEvidencePoints
    (
    w2ProcEvidencePointsID integer IDENTITY(100,1) NOT NULL,
    ProcId integer NOT NULL,
    EvidenceId varchar(12) NOT NULL,
    moddate datetime,
    moduser varchar(50)
    )
    GO

    and let's say I already have a w2ProcEvidencePoints table then the w2EDRMSMetaData table isn't created as the transaction is rolled back.

    If I turn implicit_transactions OFF and run the above SQL then it does create the w2EDRMSMetaData table, I guess because each statement is committed after execution, and it also gives an error saying the W2ProcEvidencePoints table exists. The trouble with this is I don't have the option to rollback anything on the transaction, e.g. if I had other 'insert' statements etc further on.

    So, my question is, does anyone know if there is a way that I can have the functionality of implicit_transactions OFF, but still retain the ability to perform rollbacks? I know this can be achieved in Oracle as all DDL statements are always committed, but DML statements can be rolled back.

    Many thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i'm not really sure if i'm answering the question that you asked, but try your statements without the GO after each one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the suggestion but it didn't seem to make any difference

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sorry, i am not a dba

    can you do something like test existence before creating?
    Code:
    if exists 
         ( select * 
             from dbo.sysobjects 
            where id = object_id(N'[dbo].[MyTableName]') 
              and OBJECTPROPERTY(id, N'IsUserTable') = 1   )
       drop table [dbo].[MyTableName]
    go
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In an ideal world, yes that's what we'd do. The trouble is, we have some very lazy developers here and the thought of adding checks prior to creating tables will put the fear of death into them, and will probably result in things being thrown at me from across the room. So I'm trying not to go down that route just yet, and see if there's another solution

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    if they're using sql server management studio i expect they will be very comfortable with it

    the "if exists" code i showed you was produced by the tool, it's a menu option

    (actually i'm not sure which tool i used, it was either ssms or its predecessor, enterprise manager, but i saved the sql)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    does SQL server support CREATE TABLE statements in transactions? in mysql, anytime you CREATE, ALTER, or DROP, any open transaction is automatically committed.

  8. #8
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    ^^^^Yes, it does, at least for Sql 2005. In fact, the sql modification scripts generated by Management Studio are in transactions.

    I think you could probably put the DML queries in an explicit, named transaction.

    Also, it seems like you are doing some sort of batched import. Why are you creating tables instead of just TRUNCATing them?

  9. #9
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, it's just the way that SQL Server works then with implicit_transactions ON? Basically if an error occurs the whole transaction is automatically rolled back in an 'all or nothing' manner. Hmmm that's not too good, I prefer Oracle and MySQL's implementation. Right, I'll have to weight the pro's and con's up on my options. Thanks for you input everyone

  10. #10
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In fact, I think it might be a bug in SQL Server 2000!! I changed the second (W2ProcEvidencePoints) CREATE to have an error in (notice 'integers'):

    CREATE TABLE dbo.w2EDRMSMetaData
    (
    w2EDRMSMetaDataID integer IDENTITY(100,1) NOT NULL,
    CallTypeCode varchar(50) NOT NULL,
    tagIdentity varchar(255) NOT NULL,
    tagname varchar(255),
    tagdescription varchar(255),
    MergeTag varchar(255)
    )
    GO


    CREATE TABLE dbo.W2ProcEvidencePoints
    (
    w2ProcEvidencePointsID integer IDENTITY(100,1) NOT NULL,
    ProcId integers NOT NULL,
    EvidenceId varchar(12) NOT NULL,
    moddate datetime,
    moduser varchar(50)
    )
    GO


    Only when this is run, it DOES create the w2EDRMSMetaData table, even though the W2ProcEvidencePoints CREATE failed - that looks to me like how it should operate (i.e. the same way as MySQL and Oracle). It seems odd to me that it would perform a rollback for one type of error, and not for another type of error.

    Does anyone have SQL Server 2005 and try running a test to see if it's been fixed?

  11. #11
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Update: I got access to a SQL Server 2005 DB and it still operates in the same way :-(

    Our company has Microsoft support so I might see if I can contact them about it because it looks like a bug to me.

  12. #12
    Employed Again Viflux's Avatar
    Join Date
    May 2003
    Location
    London, On.
    Posts
    1,127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you tried placing a COMMIT statement between the 2?

    When implicit transaction mode, SQL will automatically start a new transaction when a CREATE statement is issued. That transaction is open until you either COMMIT or ROLLBACK.

    If you issue a COMMIT between the 2, it should commit the first transaction (attempt to create first table) and then begin a new one.

    I've tested what I think you're saying above, and with a COMMIT between the 2 it works fine.

  13. #13
    Employed Again Viflux's Avatar
    Join Date
    May 2003
    Location
    London, On.
    Posts
    1,127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  14. #14
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The trouble is if I commit then I don't have the option to rollback, and it works exactly the same way as if I turn implicit_transactions OFF - which I can't do.

  15. #15
    Employed Again Viflux's Avatar
    Join Date
    May 2003
    Location
    London, On.
    Posts
    1,127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't understand.

    Under what circumstance would you want to create a bunch of tables, but rollback the entire script if your insert fails?

    I would think you'd only want to rollback the insert, no?

  16. #16
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    a syntax error won't cause a rollback. in general, rollbacks only happen if the client disconnects unexpectedly, the server crashes before the transaction is committed, or a transaction is is explicitly rolled back.

    i think what you're looking for is a rollback upon ANY error. that's not the point of transactions. they only roll back on errors that the client is incapable of handling.

  17. #17
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Try using an explicit transaction here, not implicit transactions. They are really meant to deal with common scenarios--like a chained insert/update. Not for dealing with DDL.

    You can also use named transactions to rollback parts of the process if you choose.

  18. #18
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    a syntax error won't cause a rollback. in general, rollbacks only happen if the client disconnects unexpectedly, the server crashes before the transaction is committed, or a transaction is is explicitly rolled back.

    i think what you're looking for is a rollback upon ANY error. that's not the point of transactions. they only roll back on errors that the client is incapable of handling.
    Argh. Forgot about this angle. This is the correct answer.

    I still really know what you are trying to do, and how creating tables in a transaction is necessary.

  19. #19
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's exactly my point, I don't necessarily want to rollback, but SQL Server does it automatically - i want to have the option.

    I'm not running the statements in Query Analyzer, I'm running it through our software, like this:

    a) create DB connection
    b) tokenize the statements and run execute each query on the same DB connection
    c) if the last statement caused an error, present the user with the option to rollback the previously executed statements so they can correct the error and re-run the script.

    But, as I've discovered, SQL Server automatically does a rollback if (as I've now discovered) a table, trigger or index already exists, which is very wrong in my opinion as it means it's not re-runnable. Oracle and MySQL don't operate in this manner.

    What I'm doing now is, if the statement starts with CREATE then I'll programatically extract the name and check whether it already exists. That way I'm doing as Rudy suggested and now trying to create things that already exist, and I'll keep my colleagues happy and not need to add the checks to the SQL scripts themselves - everyone's happy! I still think it's a bug with SQL Server, but I can't wait a few years till it's developed.

  20. #20
    Employed Again Viflux's Avatar
    Join Date
    May 2003
    Location
    London, On.
    Posts
    1,127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not so sure it's a bug.

    By turning on implicit transactions, you're asking it to wrap your whole thing in a transaction. If you encounter an error that will cause a rollback, of course it will rollback the entire transaction.

    The link I pointed to explicitly says that a CREATE statement will, with implicit transactions on, open a transaction. Sounds like a documented feature and not a bug to me.

    How would Oracle or MySQL handle a similar situation?

  21. #21
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Moreover, running it with an explict transaction controlled by your software would let you get to where you want to be without too much trouble.

  22. #22
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ah, i see. what you need to do is open another transaction right before you try to create the table. that way if the transaction is rolled back due to a duplicate table error, only that sub-transaction is rolled back and not the parent.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •