Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Why would @@FETCH_STATUS return -2
Message
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Versions des environnements
SQL Server:
SQL Server 2000
Divers
Thread ID:
01094578
Message ID:
01094585
Vues:
28
Here the remearks for @@FETCH_STATUS in BOL:

Because @@FETCH_STATUS is global to all cursors on a connection, use @@FETCH_STATUS carefully.
After a FETCH statement is executed, the test for @@FETCH_STATUS must occur before any other FETCH statement is executed
against another cursor. The value of @@FETCH_STATUS is undefined before any fetches have occurred on the connection.

For example, a user executes a FETCH statement from one cursor, and then calls a stored procedure that opens and processes
the results from another cursor. When control is returned from the called stored procedure, @@FETCH_STATUS reflects the last
FETCH executed in the stored procedure, not the FETCH statement executed before the stored procedure is called.

To retrieve the last fetch status of a specific cursor, query the fetch_status column of the sys.dm_exec_cursors dynamic
management function.





>Periodically @@FETCH_STATUS returns a -2. What could be causing this problem? This procedure calls Proc2 inside the loop.
>
>
>declare
>@Ctr			int,
>@stor_id		int
>
>Set @ctr = 1
>
>declare Temp1 cursor scroll
>	for 	
>		select
>			stor_id
>		from stores with (nolock)
>
>open Temp1
>
>FETCH ABSOLUTE @Ctr FROM Temp1 INTO @stor_id
>
>WHILE @@FETCH_STATUS = 0
>	BEGIN
>	>		EXEC Proc2
>
>		set @ctr = @Ctr + 1
>
>		FETCH ABSOLUTE @Ctr FROM Temp1 INTO @stor_id
>		print '---------------------------------'
>		print 'sp1 ' + convert(char,@ctr)+convert(char,@@FETCH_STATUS)
>	END
>
>CLOSE Temp1
>DEALLOCATE Temp1
>
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform