|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
@@FETCH_STATUS... one per simultaneous execute? Hope not!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 That's right... these are different connections.> precisely the same time and that SP/UDF uses @@FETCH_STATUS, im still safe > right? Because they are on different connections. HTH Kalen Delaney www.solidqualitylearning.com Show quote "Ian Evitable" <wh***@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 Show quote "Kalen Delaney" wrote: > > > 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" <wh***@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 > > > > > > > > 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. Show quote "R.D" <R*@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: > >> >> > 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" <wh***@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 >> > >> > >> >> >> >> > 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 Show quote "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" <R*@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: > > > >> > >> > 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" <wh***@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 > >> > > >> > > >> > >> > >> > >> > > > > >  |
|||||||||||||||||||||||
Other interesting topics