Results 1 to 6 of 6

Thread: Drop create table - check if it is used.

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Drop create table - check if it is used.

    Hi.
    I need to create a table but first i need to check if it is already there and drop it, if it is not used.
    This will be on vb.net so the code for drop and create will be in vb.net.
    I have 2 questions here.
    1)Will the table give out an exception if it is is use and I try to drop it? Meaning if i have a Begin transaction, will the table be dropped anyhow, while in use?
    If not and it will give an error that is fine, I can catch that and do something.
    2)Will writing vb statement understand the GO command? Drop and create must not be in the same batch so I need to use GO on drop and then re-create.
    There is something bothering me here but I can't recall. Had something to do with .net not understanding the GO commands on SqlCommands? I may be mistaken though.

    Thanks

    Edit. I think I was right about the GO. I read that I must use a semicolon instead. Is that the case?
    Last edited by sapator; Sep 19th, 2017 at 06:58 AM.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Drop create table - check if it is used.

    Quote Originally Posted by sapator View Post
    I think I was right about the GO. I read that I must use a semicolon instead. Is that the case?
    Yes.

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Drop create table - check if it is used.

    GO is NOT a T-SQL command.

    It's a silly UI command in SSMS that indicates that the text above should be sent as a batch to the server.

    GO is like an END statement in a way.

    If the table is in use it will block your DROP until the table is free. You are either going to "wait" for that or it's going to deadlock you, under the same critieria that all LOCKS work.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Drop create table - check if it is used.

    Hello again.

    OK so I have another related question.
    Will statements that run inside a stored procedure be considered one transaction.
    Well not exactly one transaction but statements that need to be completed before the lock is released.
    So if a stored procedure have insert and updates inside, then the complete stored procedure (with insert and updates) must be run, before a DROP can be initiated.

    Is that the case or if , for example, the insert part is complete inside a stored procedure and an update, in the same sproc, is about to begin, then a DROP might find a time worm hole and DROP the table while the stored procedure is not finished yet?

    Thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Drop create table - check if it is used.

    I just did this test

    Code:
    Drop Table Locker
    Drop Table Updater
    Go
    Create Table Locker (LockFlag int)
    Go
    Insert into Locker values (0)
    Go
    Create Table Updater (UpdData varchar(100))
    Go
    Insert into Updater values ('Test A'),('Test B')
    Go
    Drop Procedure Do_I_Block_With_Update
    Go
    Create Procedure Do_I_Block_With_Update 
    As
    Set NoCount On
    Begin Tran				-- Comment out these lines to see different behavior
    Declare @WaitFor int
    Insert into Updater values ('Test C')
    Set @WaitFor=0
    While @WaitFor<>1
    Begin
    	Set @WaitFor=(Select LockFlag From Locker)
    End
    Select @WaitFor,* From Updater
    Commit				-- Comment out these lines to see different behavior
    Go
    Execute the code above - gives you a Lock table and an Updater table

    Now go to a different SSMS query window and enter the following command:

    Exec Do_I_Block_With_Update

    You will see that it shows no results and continues to run.

    Go to another SSMS query window and do: Select * From Updater

    This will NOT run - it's blocked by the TRANSACTION in the SPROC. Cancel the QUERY (the SELECT query!)

    From that same SSMS window enter this command: Update Locker Set LockFlag=1

    And then try Select * From Updater - you will see three rows.

    And go back to the EXEC window and you will see the SPROC completed and returned "1" with three rows of data from the table.

    So with BEGIN TRAN/COMMIT the WHOLE sproc is a transaction and can block appropriately.

    Now go back to the script I gave you above and COMMENT out the BEGIN TRAN/COMMIT. Re-execute the whole script to re-create the tables and what not.

    Now try the exact same steps above.

    You will see that the SELECT is NOT blocked - you will get three rows - even before the SPROC itself is allowed to complete.

    I also tested it by seeing if I could DROP the UPDATER table with the BEGIN TRAN/COMMIT - it blocked that and did not allow me to do that.

    Without the BEGIN TRAN/COMMIT I was allowed to DROP the table from another window and when I unlocked the lock table the SPROC attempted to READ from the now DROPPED table (returning an error message).
    Last edited by szlamany; Sep 20th, 2017 at 08:08 AM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Drop create table - check if it is used.

    Hi.
    Yes thanks for that, I would be using the Transaction either way.
    What I want to know is, can a stored procedure be run in a begin trans statement.

    So lets say we have a procedure named procedureX.
    Create ProcedureX...etc
    BEGIN
    insert into...
    update...
    delete...
    END

    So begin transaction
    exec procedureX
    commit transaction

    Will this be considered the same as
    Begin transaction
    insert into...
    update...
    delete..
    commit transaction

    Are those the same?

    Also , can I run multiple stored procedures in one transaction and those be considered as one transaction?
    begin transaction
    exec proc1
    exec proc2
    exec proc3
    commit transaction
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

Posting Permissions

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



Click Here to Expand Forum to Full Width