What are examples for @@ fetch_status values ​​of -2 and -9

Question

I need examples for @@fetch_status values ​​of -2 and -9

  0 = The FETCH statement was successful. -1 = The FETCH statement failed or the row was beyond the result set. 

Here is an example cursor example

  declare @country varchar(50) declare cur_country cursor for select name from global open cur_country fetch next from cur_country into @country print @@FETCH_STATUS while (@@FETCH_STATUS=0) begin insert into country select @country fetch next from cur_country into @country end close cur_country deallocate cur_country. 

-2 Missing line. -9 The cursor does not perform a fetch operation.

need a scenario where @@fetch_status gives -2 0r -9

Answer 1

@@FETCH_STATUS = -2 usually occurs when some cursor OUTSIDE process deletes a row in the table on which the cursor is based.

If task 1 opens the cursor and starts to cycle through records in table1, and while task 1 is executed cyclically, task 2 appears and deletes certain records in table1, task 1 can return -2 when it tries to get the expected row to find ( because it was there when the cursor started).

The following topic contains an example @@FETCH_STATUS = -2

During the study, I found the following explanation from this topic :

Indicates that the membership and row order of the cursor are fixed when the cursor is open. A set of keys that uniquely identifies rows embedded in a table in the tempdb database, known as a keyset. Changes to non-key values ​​in the base tables, either made by the owner of the cursor or transferred to other users, are visible as the owner scrolls the cursor. Insertions made by other users are not visible (insertions cannot be made through the Transact-SQL server cursor). If the row is deleted, an attempt to get the row returns @@ FETCH_STATUS from -2. Updating key values ​​outside the cursor resembles deleting an old line and then inserting a new line. Next to the new values ​​are not visible, and trying to retrieve the row with the old values ​​return @@ FETCH_STATUS -2. The new values ​​are visible if the update is performed using the cursor by specifying the WHERE CURRENT OF clause.

And as DVT commented. This question fooobar.com/questions/1013479 / ... contains an example for @@FETCH_STATUS = -9

More information about @@FETCH_STATUS can be found in this MSDN article.

Answer 2

Usually minimal complete verifiable examples should be part of a good question. Here I am trying to answer with two of them:

1. Minimum cursor script that yields -9 ("not fetch")

Please note that @@FETCH_STATUS will never accept this value (it will return 0). Only internal control structures have this as initial value for declared and never received cursors (whether open or not).

 DECLARE [cursor-9] CURSOR FOR SELECT null FROM sys.tables SELECT [fetch_status] FROM sys.dm_exec_cursors(@@SPID) WHERE name='cursor-9' DEALLOCATE [cursor-9] 

Further link: fooobar.com/questions/1013479 / ...

2. Minimum cursor script that will return -2 ("row missing")

Concurrency requires two connections. I used two instances of SSMS connected to 11.0.6020 with one script window, each of which is connected to the same empty database.

Run this script in the first instance:

 DECLARE @@id int CREATE TABLE tmp41307323 ( Id int not null PRIMARY KEY ) INSERT INTO tmp41307323 (Id) VALUES (1),(2) DECLARE [cursor-2] CURSOR KEYSET FOR SELECT Id FROM tmp41307323 ORDER BY Id OPEN [cursor-2] FETCH NEXT FROM [cursor-2] INTO @@id 

He will create a table with a primary key, add two rows and open the cursor on it. Without the KEYSET you get -1 instead of -2 because that's how the keys work: SQL Server creates a temporary table in tempdb that contains only ordered unique key values ​​for each row that the cursor selects. Then, when we get it, it scans the keys for the next row to retrieve into the temporary table and selects only that row from the real table. This, and only this, scenario is therefore susceptible to simultaneous deletions. In addition, he will see the changes made to any non-key columns of the source table made during the extraction.

Run this script in the second instance:

 DELETE FROM tmp41307323 WHERE Id=2 

Thus, we delete the line that the cursor will expect in the first instance when it selects the next.

Finally, run this script in the first instance (do not disable so that the cursor is still in scope):

 DECLARE @@id int FETCH NEXT FROM [cursor-2] INTO @@id SELECT @@FETCH_STATUS [@@FETCH_STATUS] CLOSE [cursor-2] DEALLOCATE [cursor-2] DROP TABLE tmp41307323 

Result:

It will work the same when launched in the same batch in the same connection with DELETE before the second FETCH . An installation with two connections demonstrates it in a realistic context, assuming that a developer who knows the KEYSET keyword will not specifically delete during extraction and will have no side effects in the cursor loop causing such deletions (using both cursors and triggers about the same abomination, like html manipulation with regular expression).