@@FETCH_STATUS... one per simultaneous execute? Hope not!

Hello,
In another question i am using @.@.FETCH_STATUS . Below is an excerpt from SQL
Books Online.
"Because @.@.FETCH_STATUS is global to all cursors on a connection, use
@.@.FETCH_STATUS carefully".
Sounds silly but can anyone define a "connection" for me with respect to Sql
Server. I dont want to get caught out by using this construct. Does the
above mean "watch yourself if your doing things like nesting udfs and stored
procs etc" or does it mean "dont use this construct in high volume
environments like a web application".
In other words if my DAL has a 2 users calling the same procedure at
precisely the same time and that SP/UDF uses @.@.FETCH_STATUS, im still safe
right? Because they are on different connections.
cmdSelect.Connection.Open
Or does SQL Server "define" a connection differently?
TIA
Ian
> In other words if my DAL has a 2 users calling the same procedure at
> precisely the same time and that SP/UDF uses @.@.FETCH_STATUS, im still safe
> right? Because they are on different connections.
That's right... these are different connections.
HTH
Kalen Delaney
www.solidqualitylearning.com
"Ian Evitable" <whole@.hog.com> wrote in message
news:%238eKHzctFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Hello,
> In another question i am using @.@.FETCH_STATUS . Below is an excerpt from
> SQL
> Books Online.
> "Because @.@.FETCH_STATUS is global to all cursors on a connection, use
> @.@.FETCH_STATUS carefully".
> Sounds silly but can anyone define a "connection" for me with respect to
> Sql
> Server. I dont want to get caught out by using this construct. Does the
> above mean "watch yourself if your doing things like nesting udfs and
> stored
> procs etc" or does it mean "dont use this construct in high volume
> environments like a web application".
> In other words if my DAL has a 2 users calling the same procedure at
> precisely the same time and that SP/UDF uses @.@.FETCH_STATUS, im still safe
> right? Because they are on different connections.
> cmdSelect.Connection.Open
> Or does SQL Server "define" a connection differently?
> TIA
> Ian
>|||Kalen
if you are executing two cursors at the same time, then you will definitely
face problem. if latest fetch status for the other cursor is 1 or 2, by the
time you are in cursor1 then latest fetch status for the cursor1 is 1 or 2
not zero(though actually not)
In otherwords it is like @.@.identity but specific to connection.
Regards
R.D
"Kalen Delaney" wrote:

>
> That's right... these are different connections.
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "Ian Evitable" <whole@.hog.com> wrote in message
> news:%238eKHzctFHA.3500@.TK2MSFTNGP09.phx.gbl...
>
>|||That's true, but the OP just wanted to know about connections. He seemed
pretty clear that the docs said the @.@.FETCH_STATUS was the last fetch on the
connection, so if you have two or more on the SAME CONNECTION, you can have
problems.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:29EA91F8-AA2B-476F-BFBB-938840F57BF1@.microsoft.com...
> Kalen
> if you are executing two cursors at the same time, then you will
> definitely
> face problem. if latest fetch status for the other cursor is 1 or 2, by
> the
> time you are in cursor1 then latest fetch status for the cursor1 is 1 or 2
> not zero(though actually not)
> In otherwords it is like @.@.identity but specific to connection.
> Regards
> R.D
>
> "Kalen Delaney" wrote:
>
>|||Yes thanks RD but after Kalens' original clarification i now understand. The
idea of what constitutes a connection in Sql Server is the same as in
dotNet.
If however i want to something really fancy using multiple cursors on the
same connection then i should take extra special care that my use of
@.@.Fetch_STATUS is acting on the/signalling the status of the cursor at hand.
I thought that was the way it would be but i needed to be sure least i get
stuck debugging some wild goose chase.
Thanks again.
Ian
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23nBoMZjtFHA.1472@.TK2MSFTNGP15.phx.gbl...
> That's true, but the OP just wanted to know about connections. He seemed
> pretty clear that the docs said the @.@.FETCH_STATUS was the last fetch on
the
> connection, so if you have two or more on the SAME CONNECTION, you can
have
> problems.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:29EA91F8-AA2B-476F-BFBB-938840F57BF1@.microsoft.com...
2
the
>
>sql

@@FETCH_STATUS Reset

Sorry if this is a dumb question, but I am new to SQL Server and trying a simple cursor. Works fine the first time I execute, but then @.@.Fetch_Status stays at -1 and it will not work again. If I disconnect and then connect it will work once again. What I am doing wrong?

declare @.entityid int, @.datafrom nvarchar (255),

@.datato nvarchar (255), @.updatedate datetime

declare statustime1 cursor

local scroll static

for

select audit.entityid, audit.datafrom, audit.datato, audit.updatedate

from audit

, orders

where audit.entityid = orders.orderid

and audit.entitytypeid = 4

order by orders.orderid, audit.updatedate desc

open statustime1

While @.@.fetch_status = 0

Begin

fetch next from statustime1

into @.entityid, @.datafrom, @.datato, @.updatedate

print @.entityid

end

close statustime1

deallocate statustime1

go

The main thing that you are doing 'wrong' is using a CURSOR.

SQL Server operates most efficiently handling SET based data. (NOT row-wise data). A major mistake for most developers is keeping the 'recordset' mindset.

CURSORS are considered by some to be 'evil incarnate', whereas others recognize that there is a very small and highly limited use for them.

Most likely, your operation can be handled in a single set based query, with orders of magnitude imporvements in speed and reduced blocking.

If you want to post what you are attempting to accomplish, we may be able to better direct you to a set base solution.

But the main thing to understand is that in your learning, avoid using CURSORs. Push yourself to master set based logic. You will be much happier with the results.

|||

Thanks very much. I need to calculate the number of business days between two status events. Each is recorded in a row in the table, eg,

Order Event1 Date

1 Open June 1

1 Hold June 10

1Close June 30

2 Open July 1

2 hold July 10

I plan to fetch a row, save the values, fetch another row and calculate the days, continuining unitl a change in order and write output accordingly.

If you can suggest a set based approach I am all for it, however can anyone tell me why the @.@.fetch_status is not being reset.

|||

There are a few 'tools' that make the DBA's job much simplier. One of those tools is have a Calendar table in the database.

With a Calendar table, it is simple to JOIN against other tables with date values and derive the span of time between such dates. You may explore using a Calendar table with this resource.

With a Calendar table, you can easily meet your requiremens with a single query.

You need a FETCH before the WHILE statement. And then normally, the FETCH inside the loop is the last line -not the first line.

|||

I actually have a time dimension table so that will be a help, but can you show how we can get the data to do the subtraction between the two dates using set based approach. The trick is bringing together two rows that follow each other in sequence for the same order. I can't figure how to JOIN to do that.

I agree on fetch first outside the loop and so on, but that does not help with the question. Why does @.@.fetch_status still have a -1 even after the close and deallocate? It only seems to reset when I disconnect and that can't be right.

Thanks,

|||

You can use the function to fetch this data..

Sample.,

Code Snippet

Create function dbo.TotalBusinessDays

(

@.Start datetime,

@.End Datetime

)

returns int

as

Begin

Declare @.Result as int;

Set @.Result = 0;

While @.Start<=@.End

Begin

Select @.Result = @.Result + Case When datepart(weekday,@.Start) in (1,7) Then 0 Else 1 End

Set @.Start = @.Start + 1

End

return @.Result;

End

Go

Code Snippet

Create Table #events (

[Order] int ,

[Event1] Varchar(100) ,

[Date] datetime

);

Insert Into #events Values('1','Open','June 1 2007');

Insert Into #events Values('1','Hold','June 10 2007');

Insert Into #events Values('1','Close','June 30 2007');

Insert Into #events Values('2','Open','July 1 2007');

Insert Into #events Values('2','hold','July 10 2007');

Select

[Order]

,dbo.TotalBusinessDays([Open],[Hold]) OpenToHold

,dbo.TotalBusinessDays([Hold],[Close]) HoldToClose

From

(

Select

[Order]

, Max(case when [Event1] = 'Open' Then [Date] end) as [Open]

, Max(case when [Event1] = 'Hold' Then [Date] end) as [Hold]

, Max(case when [Event1] = 'Close' Then [Date] end) as [Close]

from

#events

Group bY

[Order]

) as Data

|||

I am impressed, looks pretty good. I may have oversimplied the example however,so how would it work for this data?

Order Event1 Date

3 Open July 1

3 Hold July 10

3 open july 20

3 hold Aug 1

3 open Aug 3

3 fill Aug 20

And I still would like to know why @.@.fetch_status stays -1 after I close and even deallocate the cursor.

|||

@.@.FETCH_STATUS is similar to a static variable with connection scope.

It holds the last know value.

If you try this, in a new connection window, without a CURSOR:

SELECT @.@.FETCH_STATUS

It will return [ 0 ].

It will reset on the next execution of FETCH.

|||

Bingo! You just solved it for me. I was being dense, and I was missing the point that @.@.fetch_status resets on the execution of fetch. It is not reset on open cursor. So I really need the first fetch outside the while loop and problem solved! My confusion is I am too familiar with that other DB (evil DB2) where SQLCODE would be zero after the open (lol) ..

Very interesting discussion and if you can think of set process approach to handle the more general case of multiple status changes during the order I would love to hear it.

|||

Here another sample,

Code Snippet

Create Table #orders (

[Order] int ,

[Event1] Varchar(100) ,

[Date] datetime

);

Insert Into #orders Values('3','Open','July 1 2007');

Insert Into #orders Values('3','Hold','July 10 2007');

Insert Into #orders Values('3','open','july 20 2007');

Insert Into #orders Values('3','hold','Aug 1 2007');

Insert Into #orders Values('3','open','Aug 3 2007');

Insert Into #orders Values('3','fill','Aug 20 2007');

Select * into #temp from #orders order By 1,3

Alter table #temp Add RowId int Identity(1,1)

select

[Pre].[Order],

[Pre].[Event1] +' to ' + [Post].[Event1],

dbo.TotalBusinessDays([Pre].[Date],[Post].[Date])

from

#temp [Pre]

Join #temp [Post] on [Pre].RowId = [Post].RowId-1

|||Since everyone was so nice to tell you why cursors are bad and give alternatives, and no one has answered your question:

The problem with your code is you need to change it to:

open statustime1

fetch next from statustime1

While @.@.fetch_status = 0

Begin

print @.entityid

fetch next from statustime1

into @.entityid, @.datafrom, @.datato, @.updatedate

end

In your current code you never did a fetch, so @.@.fetch_status is uninitiallized.

|||

Actually Tom, I think that I did answer that question.

Arnie Rowland wrote:

You need a FETCH before the WHILE statement. And then normally, the FETCH inside the loop is the last line -not the first line.

And

Arnie Rowland wrote:

@.@.FETCH_STATUS is similar to a static variable with connection scope.

It holds the last know value.

...

It will reset on the next execution of FETCH.

|||I am sorry Arnie, I miss that one. :< You did answer the question.

@@FETCH_STATUS loops?

I've seen in several examples where cursors are used particularly on
this board that people use
While @.@.FETCH_STATUS <> -1
BEGIN
IF @.@.FETCH_STATUS <> -2 BEGIN
..
END
END
On most of the Microsoft BOL examples I see that they seem to always
use...
WHILE @.@.FETCH_STATUS = 0
BEGIN
..
END
I have looked up the different meanings
"0 FETCH statement was successful.
-1 FETCH statement failed or the row was beyond the result set.
-2 Row fetched is missing."
http://msdn.microsoft.com/library/d... />
s_1c1f.asp
To get to the point, my question is why use one method over the other?
Is there some drawback to useing the "@.@.FETCH_STATUS = 0" method that
might not be apparent?
Do I need to worry about endless loops?
As you might surmise I have a UDF that is running much to long, and
when I look at the number of times my objects have been called, the
number is larger than it should be.Why? Consistency. Almost every fetch loop I've run across looks like this:
FETCH
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- do something here
FETCH
END
When you or the poor slob who has to maintain your code looks at it six
months, one, or two years from now, it will be a lot easier to read if it
uses the above pattern.
<Contraptor@.gmail.com> wrote in message
news:1132634990.886050.150040@.g49g2000cwa.googlegroups.com...
> I've seen in several examples where cursors are used particularly on
> this board that people use
> While @.@.FETCH_STATUS <> -1
> BEGIN
> IF @.@.FETCH_STATUS <> -2 BEGIN
> ...
> END
> END
> On most of the Microsoft BOL examples I see that they seem to always
> use...
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> ...
> END
> I have looked up the different meanings
> "0 FETCH statement was successful.
> -1 FETCH statement failed or the row was beyond the result set.
> -2 Row fetched is missing."
> http://msdn.microsoft.com/library/d...>
als_1c1f.asp
> To get to the point, my question is why use one method over the other?
> Is there some drawback to useing the "@.@.FETCH_STATUS = 0" method that
> might not be apparent?
> Do I need to worry about endless loops?
> As you might surmise I have a UDF that is running much to long, and
> when I look at the number of times my objects have been called, the
> number is larger than it should be.
>|||Ok, so al long as I'm not doing anything to change the @.@.FETCH_STATUS
between when I FETCH and check for a valid @.@.FETCH_STATUS I have no
need to worry about infinite loops if using the
FETCH
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- do something here
FETCH
END
method.
Now I just need to figure out why my UDF totally iced our test server
last night.

@@fetch_status in trigger

in the trigger for update and insert i have the following cursor

declare crs cursor static local for
SELECT [id] FROM inserted

open crs
fetch next from crs into @.v1

print @.@.fetch_status

while @.@.fetch_status = 0
begin
print @.v1

fetch next from crs into @.v1
end

close crs
deallocate crs

the problem is that the @.@.fetch_status is now always -1 and I'm pretty sure that it worked some time ago and i can't figure what is changed.

any ideas ?
thnx.Why would you want to do this? What's the real trigger look like?

Can you post that code?

A cursor in a trigger would more than likely perform poorly...|||I use cursor in trigger cause i can have inserts/updates from multiple sources and i don't want to break the logic.

code in attach|||fixed.

cursor threshold was 0; changed to -1

@@FETCH_STATUS

Hi there..
I have this problem: I run my stored proc alot of times for testing, but one
time it want work.
It just give me back this message: The command(s) completed successfully.
I checked the @.@.FETCH_STATUS and it became -1, why is that when it worked
before..
Can any body help me'
THX@.@.FETCH_STATUS is for cursors, you want to use @.@.ERROR instead if it's 0
then there is no error
http://sqlservercode.blogspot.com/
"Hussain Al-Mousawi" <hal_mousawi@.hotmail.com> wrote in message
news:uv6dVVTRGHA.2532@.TK2MSFTNGP10.phx.gbl...
> Hi there..
> I have this problem: I run my stored proc alot of times for testing, but
> one time it want work.
> It just give me back this message: The command(s) completed successfully.
> I checked the @.@.FETCH_STATUS and it became -1, why is that when it worked
> before..
> Can any body help me'
> THX
>

@@FETCH_STATUS

hi.
i have a question about @.@.FETCH_STATUS when doing a cursor loop.
i found 2 examples and i want to know what the diffrence is between
them,
the first one i found in the help:
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
here it uses @.@.FETCH_STATUS = 0, wich would be while it is successfull.
the next exaple was an example in the QA:
DECLARE <cursor_name, sysname, test_cursor> CURSOR
READ_ONLY
FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>
DECLARE @.name varchar(40)
OPEN <cursor_name, sysname, test_cursor>
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
-- PRINT 'add user defined code here'
-- eg.
DECLARE @.message varchar(100)
SELECT @.message = 'my name is: ' + @.name
PRINT @.message
END
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
END
CLOSE <cursor_name, sysname, test_cursor>
DEALLOCATE <cursor_name, sysname, test_cursor>
here it first says, while @.@.FETCH_STATUS <> -2 and then says
only if @.@.FETCH_STATUS <> -1
in my world this would result in the same thing, since according to the
help the 3 diffrent statuses is 0, -1 and -2.
what is the diffrence between the two? and wich one is the "right" one
to use?
/Anders0
The FETCH statement was successful.
-1
The FETCH statement failed or the row was beyond the result set.
-2
The row fetched is missing.
-oj
<aebohlin@.gmail.com> wrote in message
news:1141228790.445782.79910@.v46g2000cwv.googlegroups.com...
> hi.
> i have a question about @.@.FETCH_STATUS when doing a cursor loop.
> i found 2 examples and i want to know what the diffrence is between
> them,
> the first one i found in the help:
> DECLARE Employee_Cursor CURSOR FOR
> SELECT LastName, FirstName FROM Northwind.dbo.Employees
> OPEN Employee_Cursor
> FETCH NEXT FROM Employee_Cursor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM Employee_Cursor
> END
> CLOSE Employee_Cursor
> DEALLOCATE Employee_Cursor
> here it uses @.@.FETCH_STATUS = 0, wich would be while it is successfull.
> the next exaple was an example in the QA:
> DECLARE <cursor_name, sysname, test_cursor> CURSOR
> READ_ONLY
> FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>
> DECLARE @.name varchar(40)
> OPEN <cursor_name, sysname, test_cursor>
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> -- PRINT 'add user defined code here'
> -- eg.
> DECLARE @.message varchar(100)
> SELECT @.message = 'my name is: ' + @.name
> PRINT @.message
> END
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> END
> CLOSE <cursor_name, sysname, test_cursor>
> DEALLOCATE <cursor_name, sysname, test_cursor>
> here it first says, while @.@.FETCH_STATUS <> -2 and then says
> only if @.@.FETCH_STATUS <> -1
> in my world this would result in the same thing, since according to the
> help the 3 diffrent statuses is 0, -1 and -2.
> what is the diffrence between the two? and wich one is the "right" one
> to use?
> /Anders
>|||You don't need <>-2 checking for READONLY CURSOR. If the cursor is Ket-Set
driven cursor, you may read non-existing rows. Then you need to cehck for <>
-2
Refer "Cursor Types" in BOL
--
Thanks & Rate the Postings.
-Ravi-
"aebohlin@.gmail.com" wrote:

> hi.
> i have a question about @.@.FETCH_STATUS when doing a cursor loop.
> i found 2 examples and i want to know what the diffrence is between
> them,
> the first one i found in the help:
> DECLARE Employee_Cursor CURSOR FOR
> SELECT LastName, FirstName FROM Northwind.dbo.Employees
> OPEN Employee_Cursor
> FETCH NEXT FROM Employee_Cursor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM Employee_Cursor
> END
> CLOSE Employee_Cursor
> DEALLOCATE Employee_Cursor
> here it uses @.@.FETCH_STATUS = 0, wich would be while it is successfull.
> the next exaple was an example in the QA:
> DECLARE <cursor_name, sysname, test_cursor> CURSOR
> READ_ONLY
> FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>
> DECLARE @.name varchar(40)
> OPEN <cursor_name, sysname, test_cursor>
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> -- PRINT 'add user defined code here'
> -- eg.
> DECLARE @.message varchar(100)
> SELECT @.message = 'my name is: ' + @.name
> PRINT @.message
> END
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> END
> CLOSE <cursor_name, sysname, test_cursor>
> DEALLOCATE <cursor_name, sysname, test_cursor>
> here it first says, while @.@.FETCH_STATUS <> -2 and then says
> only if @.@.FETCH_STATUS <> -1
> in my world this would result in the same thing, since according to the
> help the 3 diffrent statuses is 0, -1 and -2.
> what is the diffrence between the two? and wich one is the "right" one
> to use?
> /Anders
>|||thanks alot.sql