Drop failed for table

Hi,
Here is what I have done:
1. I created two tables using:
CREATE TABLE CursorTest
(
RowID INT,
RowText CHAR(4)
)
GO
CREATE TABLE CursorTestOdd
(
RowID INT,
RowText CHAR(4)
)
GO

I then populate the tables using:
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
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:
WHERE RowID % 2 = 1
is not working correctly, it seems that the code returns nothing, if I changed it to
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?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.Exceptio nTemplates.FailedOperationExceptionText&EvtID=Drop+Table&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?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476

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

OK
----------

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

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

----------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.Exceptio nTemplates.FailedOperationExceptionText&EvtID=Drop+Table&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?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1222&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,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|||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,|||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:
-- 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|||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!|||if u r updating/inserting or deleteing from the table , from which u created the cursor , u should create a static cursor.