Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    3

    Unanswered: Drop failed for table

    Hi,
    Here is what I have done:
    1. I created two tables using:
    Code:
    CREATE TABLE CursorTest
    (
    RowID INT,
    RowText CHAR(4)
    )
    GO
    CREATE TABLE CursorTestOdd
    (
    RowID INT,
    RowText CHAR(4)
    )
    GO
    I then populate the tables using:
    Code:
    SET NOCOUNT ON
    DECLARE @intCounter INT
    DECLARE @chrTextOdd CHAR(4)
    DECLARE @chrTextEven CHAR(4)
    SELECT @intCounter = 1
    SELECT @chrTextOdd = 'Odd'
    SELECT @chrTextEven = 'Even'
    WHILE (@intCounter <= 200000)
    BEGIN
    IF (@intCounter % 2) = 0
    BEGIN
    INSERT INTO CursorTest VALUES (@intCounter,
    @chrTextEven)
    END ELSE
    BEGIN
    INSERT INTO CursorTest VALUES (@intCounter,
    @chrTextOdd)
    END
    SELECT @intCounter = @intCounter + 1
    END
    GO
    3. I then tried to use cursor to insert and delete rows from the tables
    Code:
    DECLARE @intRowID INT
    DECLARE curOddRows CURSOR FOR
    SELECT RowID
    FROM CursorTest
    WHERE RowID % 2 = 1
    OPEN curOddRows
    FETCH NEXT FROM curOddRows INTO @intRowID
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    BEGIN TRANSACTION
    INSERT INTO CursorTestOdd
    SELECT *
    FROM CursorTest
    WHERE RowID = @intRowID
    DELETE CursorTest
    WHERE RowID = @intRowID
    COMMIT
    FETCH NEXT FROM curOddRows INTO @intRowID
    END
    CLOSE curOddRows
    DEALLOCATE curOddRows
    GO
    Here comes the mess:
    I have no problem with codes in step 1&2,but when I tried to execute the codes in step3, I got no error, however, when I checked the two tables, nothing has been changed which means that my insert and delete are not working on the two tables. SO I tried to debug the script and found out that the code:
    Code:
    WHERE RowID % 2 = 1
    is not working correctly, it seems that the code returns nothing, if I changed it to
    Code:
    WHERE RowID % 2 = 0
    it returns the correct answer.
    I then tried to execute the modified codes in step3, I got a message saying that
    "A cursor with the name 'curOddRows' already exists."
    If I changed the name to 'curOddRows1' and execute, it will say:
    "The cursor is already open."
    But I execute the code again whatever, and it takes a long time for it to finish such that I cannot wait. So I clicked the 'stop' button to cancel the execution.
    Now I want to drop the tables and got following error:

    **************************
    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Drop failed for Table 'dbo.CursorTest'. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdN...e&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

    For help, click: http://go.microsoft.com/fwlink?ProdN...2&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------


    ===================================

    Drop failed for Table 'dbo.CursorTest'. (Microsoft.SqlServer.Smo)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdN...e&LinkId=20476

    ------------------------------
    Program Location:

    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Dr opImpl()
    at Microsoft.SqlServer.Management.Smo.Table.Drop()
    at Microsoft.SqlServer.Management.SqlManagerUI.DropOb jects.DoDropObject(Int32 objectRowIndex)
    at Microsoft.SqlServer.Management.SqlManagerUI.DropOb jects.DropAllObjects(Boolean stopOnError)

    ===================================

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------
    Program Location:

    at Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
    at Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
    at Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(StringCollection queries)
    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ex ecuteNonQuery(StringCollection queries, Boolean includeDbContext)
    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Dr opImplWorker(Urn& urn)
    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Dr opImpl()

    ===================================

    Lock request time out period exceeded. (.Net SqlClient Data Provider)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdN...2&LinkId=20476

    ------------------------------
    Server Name:
    Error Number: 1222
    Severity: 16
    State: 56
    Line Number: 2


    ------------------------------
    Program Location:

    at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String methodName, Boolean async)
    at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    at Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

    **************************

    So how can I drop the table, right now I can not even
    view the tables' contents, cannot use 'SELECT' etc.. commands, whenever I tried to access the two tables, the program will be keeping doing the execution without stop.

    Your comments and suggestions are greatly appreciated!

    Thanks,
    Last edited by niuer; 03-04-06 at 17:00.

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Are you running this from the same query session window or is the one you ran this in still open?

    The clue I see is the message "A cursor with the name 'curOddRows' already exists." which means, because you've stopt the processing the script never comes to deallocating the cursor. When you try to drop the table the cursor still exists and is consequently locking the table (schema lock probably) so it can't be dropped. Close the query window or try to deallocate the cursor.

    It's good practice to declare a cursor as LOCAL (you're now implicetly making a global cursor) that way when a cursor ends the cursor is deallocated when the query batch ends. Doing this won't fix this problem when you keep the query window open!

    Lex

  3. #3
    Join Date
    Feb 2006
    Posts
    3
    Hi Lexiflex:
    Thanks for your response. I think it's the blocking problem. Because my cursor runs over the 200K rows, it takes a lot of time to finish it, I did not wait for it to finish and stopped it. As you said, I haven't deallocate the cursor which locked the table and failed my drop action.
    You mentioned query batch, what is a query batch?
    I used 'GO' to separate query statements, does the statements between 'GO' is a batch?
    What is the difference for statements in different query windows?
    Can you give me some simple explaination?

    Thanks,

  4. #4
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280

    Distinguish between query batches and sessions

    You have to distinguish between query batches and sessions. A query batch is, as you guessed, comprised of the statements between two GO's (simply put). A session is opened when you make a connection and is closed when the connection is broken (like opening and closing a query window in Query Analyzer). A session can consist of multiple query batches.

    In the case of a cursor, a local cursor exists as long as the query batch and a global cursor as long as the session. Unless you deallocate it explicitedly ofcourse.

    Look at this example:
    Code:
    -- This is just a dummy global cursor
    DECLARE global_csr CURSOR
    FOR SELECT 1
    
    -- The next statement returns -1 which means the cursor is allocated
    -- We're still in the query batch
    SELECT CURSOR_STATUS('global', 'global_csr')
    GO
    
    -- The query batch has ended but the cursor still exists because
    -- the next statement returns -1
    SELECT CURSOR_STATUS('global', 'global_csr')
    
    -- Now we deallocate the global cursor and the status returns -3
    DEALLOCATE global_csr
    SELECT CURSOR_STATUS('global', 'global_csr')
    GO
    
    -- This is just a dummy local cursor
    DECLARE local_csr CURSOR LOCAL
    FOR SELECT 1
    
    -- The next statement returns -1 which means the cursor is allocated
    SELECT CURSOR_STATUS('local', 'local_csr')
    GO
    
    -- The next statement returns -3 which means the cursor is deallocated
    -- with the end of the query batch
    SELECT CURSOR_STATUS('local', 'local_csr')
    GO
    Ofcourse this is just the tip of the iceberg. For example a stored procedure acts as a query batch. When you declare variables they live as long as your query batch but temporary tables (#-tables) live as long as your session.

    When you want to know more exact details please look in the BOL of any other SQL reference book.

    Hope I was of help.

    Lex

  5. #5
    Join Date
    Feb 2006
    Posts
    3
    Hi Lexiflex:
    Thanks a lot for your inputs. They are very clear and helpful for me to understand batch and session. I really appreciate your time and efforts.

    Thanks!

  6. #6
    Join Date
    Mar 2006
    Posts
    5
    if u r updating/inserting or deleteing from the table , from which u created the cursor , u should create a static cursor.

Posting Permissions

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