I am trying to use a cursor to create a mass Text field with the results fro
m
the selections from a series of VarChar(8000) values. I know I need to use
UpdateText, but it only seems to store the 1st one it reads. Can anyone hel
p?
Here's my text:
Declare @.TriggerText nVarChar(4000)
Declare @.ptrval Binary(16)
Declare @.Offset Int
-- Create temporary table to hold Text field
Create Table #tempTrigger
(TextField Text NULL)
Insert Into #tempTrigger Select ''
-- Get Trigger "basis"
Declare curTriggerBasis Insensitive Cursor For
Select c.Text
From sysObjects o (nolock)
Inner Join sysComments c (nolock)
On o.ID = c.ID
Where o.Name = 'cttx_Customer'
Order By ColID
For Read Only
Open curTriggerBasis
Fetch Next From curTriggerBasis Into @.TriggerText
While @.@.Fetch_Status = 0
Begin
Select @.ptrval = TEXTPTR(TextField),
@.Offset = DataLength(TextField)
From #tempTrigger (nolock)
UpdateText #tempTrigger.TextField @.ptrval @.Offset 0 @.TriggerText
Fetch Next From curTriggerBasis Into @.TriggerText
End
Close curTriggerBasis
Deallocate curTriggerBasis
Select * From #tempTrigger (nolock)> but it only seems to store the 1st one it reads
How are you determining this? What does 'SELECT DATALENGTH(TextField) FROM
#tempTrigger' return?
Happy Holidays
Dan Guzman
SQL Server MVP
"bobnunny" <u17151@.uwe> wrote in message news:59ac1c8e96b9c@.uwe...
>I am trying to use a cursor to create a mass Text field with the results
>from
> the selections from a series of VarChar(8000) values. I know I need to
> use
> UpdateText, but it only seems to store the 1st one it reads. Can anyone
> help?
> Here's my text:
> Declare @.TriggerText nVarChar(4000)
> Declare @.ptrval Binary(16)
> Declare @.Offset Int
> -- Create temporary table to hold Text field
> Create Table #tempTrigger
> (TextField Text NULL)
> Insert Into #tempTrigger Select ''
> -- Get Trigger "basis"
> Declare curTriggerBasis Insensitive Cursor For
> Select c.Text
> From sysObjects o (nolock)
> Inner Join sysComments c (nolock)
> On o.ID = c.ID
> Where o.Name = 'cttx_Customer'
> Order By ColID
> For Read Only
> Open curTriggerBasis
> Fetch Next From curTriggerBasis Into @.TriggerText
> While @.@.Fetch_Status = 0
> Begin
> Select @.ptrval = TEXTPTR(TextField),
> @.Offset = DataLength(TextField)
> From #tempTrigger (nolock)
> UpdateText #tempTrigger.TextField @.ptrval @.Offset 0 @.TriggerText
> Fetch Next From curTriggerBasis Into @.TriggerText
> End
> Close curTriggerBasis
> Deallocate curTriggerBasis
> Select * From #tempTrigger (nolock)|||I've put Print statements in there to check this out. It shows Datalength a
s
4000 everytime except the last one. BUT, like an idiot I was checking the
loop so hard, but the Select statement at the end will only return the first
4000. Once I changed that to DataLength, it showed it had it all.
Thanx!
Dan Guzman wrote:
>How are you determining this? What does 'SELECT DATALENGTH(TextField) FRO
M
>#tempTrigger' return?
>
>[quoted text clipped - 36 lines]
Showing posts with label cursor. Show all posts
Showing posts with label cursor. Show all posts
Friday, February 24, 2012
Sunday, February 12, 2012
API cursors?
Im really confused about cursor stuff that isnt quite explained in the BOL.
What exactly is the difference between a Tsql cursor and an API cursor? When
I access a db from a .NET application is this an API cursor? is an API cursor
the same as a server cursor? does an API cursor/ server cursor hold any locks
on the db? does a .NET application? BOL says to use a 'server' cursor as the
prefered methos to give a web app user a result set from an aribtrary query
defined by the user which might be unpredicably large so that you can fetch n
rows at a time without bringing the entire result set in and without holding
locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
is how and if it differs from a Tsql cursor called by a stored procedure or
something. Can anyone explain this?
Hi Charles,
Here is some information...
T-SQL cursors are used in stored production and query development in
applications like Query Analyzer. Since SQL is, for the most part, a batch
processing language, the support for row-by-row processing (using a cursor)
is necessary is some situations. Other than learning about them for
troubleshooting purposes, I would avoid using.
API cursors (which can be server-side or client-side) are used in
application development, like VB, VC, not ADO.NET (due to poor performance,
cursors are not supported in ADO.NET). If you still want to use a cursor, a
previous version of ADO will need to be used. With the new SqlDataReader
object, you can read the data very quickly into a collection and process the
data row-by-row.
API cursors, as with all database transactions, acquire locks. For more
info on locking types and duration, I suggest reading about locking modes and
transaction isolation levels.
A book I suggest...'Inside Microsoft SQL Server 2000' by Delaney/Soukup,
Microsoft Press.
HTH,
Adam
"CharlesT" wrote:
> Im really confused about cursor stuff that isnt quite explained in the BOL.
> What exactly is the difference between a Tsql cursor and an API cursor? When
> I access a db from a .NET application is this an API cursor? is an API cursor
> the same as a server cursor? does an API cursor/ server cursor hold any locks
> on the db? does a .NET application? BOL says to use a 'server' cursor as the
> prefered methos to give a web app user a result set from an aribtrary query
> defined by the user which might be unpredicably large so that you can fetch n
> rows at a time without bringing the entire result set in and without holding
> locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
> is how and if it differs from a Tsql cursor called by a stored procedure or
> something. Can anyone explain this?
>
|||okay, well suppose I have an SQL database of availible rental properties.
People in a company update this database frequently. They want to make a .NET
application that will allow user on the internet to search this database -
users would be able to find online what properties are availible and their
rates and pictures etc. Based on whatever search values the user inputs
their result set may be unpredicably large. I could make .NET simply load
the entire database each time the user makes a search into a dataset but this
takes time and resources and it is likely that the user might want to change
their search after seeing that hundreds of properties have met their criteria
etc. Alternatively, I could have the user's search run a stored procedure
that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
user decides to keep looking fetches the next 10 rows etc. I understand how
the cursor in the stored procedure works and I can estimate its effect on
locking and even change its isolation level based on network traffic etc.
Performance wont be a factor at all when dealing with only 10 rows. The
variable which stores how deep the user was in the database could exist in
the application so the cursor can be closed and reopened each time the user
selects next so it doesnt hold locks etc. Or, and this is the thing I dont
get, I could use the ExecuteReader() object from .NET to access the database.
I suppose that since this is using ADO it is not an API cursor, but then what
is it? what is going on in SQL when an outside application runs a cursor in
SQL via ADO. What is the concurrency price of possible simultaneous
SELECT/UPDATE to an SQL DB from inside a company with people accessing it
over the internet with a web application? If .NET doesnt use API cursors
when it reads data from SQL with ADO then what does it use? and if it isnt
API cursors then what is the fuss over API cursors that I see mentioned
everywhere in SQL books and where would they ever concievably appear?
Message posted via http://www.droptable.com
|||CharlesT via droptable.com wrote:
> okay, well suppose I have an SQL database of availible rental properties.
> People in a company update this database frequently. They want to make a .NET
> application that will allow user on the internet to search this database -
> users would be able to find online what properties are availible and their
> rates and pictures etc. Based on whatever search values the user inputs
> their result set may be unpredicably large. I could make .NET simply load
> the entire database each time the user makes a search into a dataset but this
> takes time and resources and it is likely that the user might want to change
> their search after seeing that hundreds of properties have met their criteria
> etc. Alternatively, I could have the user's search run a stored procedure
> that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
> user decides to keep looking fetches the next 10 rows etc. I understand how
> the cursor in the stored procedure works and I can estimate its effect on
> locking and even change its isolation level based on network traffic etc.
> Performance wont be a factor at all when dealing with only 10 rows. The
> variable which stores how deep the user was in the database could exist in
> the application so the cursor can be closed and reopened each time the user
> selects next so it doesnt hold locks etc. Or, and this is the thing I dont
> get, I could use the ExecuteReader() object from .NET to access the database.
> I suppose that since this is using ADO it is not an API cursor, but then what
> is it? what is going on in SQL when an outside application runs a cursor in
> SQL via ADO. What is the concurrency price of possible simultaneous
> SELECT/UPDATE to an SQL DB from inside a company with people accessing it
> over the internet with a web application? If .NET doesnt use API cursors
> when it reads data from SQL with ADO then what does it use? and if it isnt
> API cursors then what is the fuss over API cursors that I see mentioned
> everywhere in SQL books and where would they ever concievably appear?
> --
> Message posted via http://www.droptable.com
For your scenario you should take a look at some of the solutions
offered here:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
What exactly is the difference between a Tsql cursor and an API cursor? When
I access a db from a .NET application is this an API cursor? is an API cursor
the same as a server cursor? does an API cursor/ server cursor hold any locks
on the db? does a .NET application? BOL says to use a 'server' cursor as the
prefered methos to give a web app user a result set from an aribtrary query
defined by the user which might be unpredicably large so that you can fetch n
rows at a time without bringing the entire result set in and without holding
locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
is how and if it differs from a Tsql cursor called by a stored procedure or
something. Can anyone explain this?
Hi Charles,
Here is some information...
T-SQL cursors are used in stored production and query development in
applications like Query Analyzer. Since SQL is, for the most part, a batch
processing language, the support for row-by-row processing (using a cursor)
is necessary is some situations. Other than learning about them for
troubleshooting purposes, I would avoid using.
API cursors (which can be server-side or client-side) are used in
application development, like VB, VC, not ADO.NET (due to poor performance,
cursors are not supported in ADO.NET). If you still want to use a cursor, a
previous version of ADO will need to be used. With the new SqlDataReader
object, you can read the data very quickly into a collection and process the
data row-by-row.
API cursors, as with all database transactions, acquire locks. For more
info on locking types and duration, I suggest reading about locking modes and
transaction isolation levels.
A book I suggest...'Inside Microsoft SQL Server 2000' by Delaney/Soukup,
Microsoft Press.
HTH,
Adam
"CharlesT" wrote:
> Im really confused about cursor stuff that isnt quite explained in the BOL.
> What exactly is the difference between a Tsql cursor and an API cursor? When
> I access a db from a .NET application is this an API cursor? is an API cursor
> the same as a server cursor? does an API cursor/ server cursor hold any locks
> on the db? does a .NET application? BOL says to use a 'server' cursor as the
> prefered methos to give a web app user a result set from an aribtrary query
> defined by the user which might be unpredicably large so that you can fetch n
> rows at a time without bringing the entire result set in and without holding
> locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
> is how and if it differs from a Tsql cursor called by a stored procedure or
> something. Can anyone explain this?
>
|||okay, well suppose I have an SQL database of availible rental properties.
People in a company update this database frequently. They want to make a .NET
application that will allow user on the internet to search this database -
users would be able to find online what properties are availible and their
rates and pictures etc. Based on whatever search values the user inputs
their result set may be unpredicably large. I could make .NET simply load
the entire database each time the user makes a search into a dataset but this
takes time and resources and it is likely that the user might want to change
their search after seeing that hundreds of properties have met their criteria
etc. Alternatively, I could have the user's search run a stored procedure
that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
user decides to keep looking fetches the next 10 rows etc. I understand how
the cursor in the stored procedure works and I can estimate its effect on
locking and even change its isolation level based on network traffic etc.
Performance wont be a factor at all when dealing with only 10 rows. The
variable which stores how deep the user was in the database could exist in
the application so the cursor can be closed and reopened each time the user
selects next so it doesnt hold locks etc. Or, and this is the thing I dont
get, I could use the ExecuteReader() object from .NET to access the database.
I suppose that since this is using ADO it is not an API cursor, but then what
is it? what is going on in SQL when an outside application runs a cursor in
SQL via ADO. What is the concurrency price of possible simultaneous
SELECT/UPDATE to an SQL DB from inside a company with people accessing it
over the internet with a web application? If .NET doesnt use API cursors
when it reads data from SQL with ADO then what does it use? and if it isnt
API cursors then what is the fuss over API cursors that I see mentioned
everywhere in SQL books and where would they ever concievably appear?
Message posted via http://www.droptable.com
|||CharlesT via droptable.com wrote:
> okay, well suppose I have an SQL database of availible rental properties.
> People in a company update this database frequently. They want to make a .NET
> application that will allow user on the internet to search this database -
> users would be able to find online what properties are availible and their
> rates and pictures etc. Based on whatever search values the user inputs
> their result set may be unpredicably large. I could make .NET simply load
> the entire database each time the user makes a search into a dataset but this
> takes time and resources and it is likely that the user might want to change
> their search after seeing that hundreds of properties have met their criteria
> etc. Alternatively, I could have the user's search run a stored procedure
> that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
> user decides to keep looking fetches the next 10 rows etc. I understand how
> the cursor in the stored procedure works and I can estimate its effect on
> locking and even change its isolation level based on network traffic etc.
> Performance wont be a factor at all when dealing with only 10 rows. The
> variable which stores how deep the user was in the database could exist in
> the application so the cursor can be closed and reopened each time the user
> selects next so it doesnt hold locks etc. Or, and this is the thing I dont
> get, I could use the ExecuteReader() object from .NET to access the database.
> I suppose that since this is using ADO it is not an API cursor, but then what
> is it? what is going on in SQL when an outside application runs a cursor in
> SQL via ADO. What is the concurrency price of possible simultaneous
> SELECT/UPDATE to an SQL DB from inside a company with people accessing it
> over the internet with a web application? If .NET doesnt use API cursors
> when it reads data from SQL with ADO then what does it use? and if it isnt
> API cursors then what is the fuss over API cursors that I see mentioned
> everywhere in SQL books and where would they ever concievably appear?
> --
> Message posted via http://www.droptable.com
For your scenario you should take a look at some of the solutions
offered here:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
API cursors?
Im really confused about cursor stuff that isnt quite explained in the BOL.
What exactly is the difference between a Tsql cursor and an API cursor? When
I access a db from a .NET application is this an API cursor? is an API curso
r
the same as a server cursor? does an API cursor/ server cursor hold any lock
s
on the db? does a .NET application? BOL says to use a 'server' cursor as the
prefered methos to give a web app user a result set from an aribtrary query
defined by the user which might be unpredicably large so that you can fetch
n
rows at a time without bringing the entire result set in and without holding
locks. But I cant find anywhere else in BOL what exactly this 'server cursor
'
is how and if it differs from a Tsql cursor called by a stored procedure or
something. Can anyone explain this?Hi Charles,
Here is some information...
T-SQL cursors are used in stored production and query development in
applications like Query Analyzer. Since SQL is, for the most part, a batch
processing language, the support for row-by-row processing (using a cursor)
is necessary is some situations. Other than learning about them for
troubleshooting purposes, I would avoid using.
API cursors (which can be server-side or client-side) are used in
application development, like VB, VC, not ADO.NET (due to poor performance,
cursors are not supported in ADO.NET). If you still want to use a cursor, a
previous version of ADO will need to be used. With the new SqlDataReader
object, you can read the data very quickly into a collection and process the
data row-by-row.
API cursors, as with all database transactions, acquire locks. For more
info on locking types and duration, I suggest reading about locking modes an
d
transaction isolation levels.
A book I suggest...'Inside Microsoft SQL Server 2000' by Delaney/Soukup,
Microsoft Press.
HTH,
Adam
"CharlesT" wrote:
> Im really confused about cursor stuff that isnt quite explained in the BOL
.
> What exactly is the difference between a Tsql cursor and an API cursor? Wh
en
> I access a db from a .NET application is this an API cursor? is an API cur
sor
> the same as a server cursor? does an API cursor/ server cursor hold any lo
cks
> on the db? does a .NET application? BOL says to use a 'server' cursor as t
he
> prefered methos to give a web app user a result set from an aribtrary quer
y
> defined by the user which might be unpredicably large so that you can fetc
h n
> rows at a time without bringing the entire result set in and without holdi
ng
> locks. But I cant find anywhere else in BOL what exactly this 'server curs
or'
> is how and if it differs from a Tsql cursor called by a stored procedure o
r
> something. Can anyone explain this?
>|||okay, well suppose I have an SQL database of availible rental properties.
People in a company update this database frequently. They want to make a .NE
T
application that will allow user on the internet to search this database -
users would be able to find online what properties are availible and their
rates and pictures etc. Based on whatever search values the user inputs
their result set may be unpredicably large. I could make .NET simply load
the entire database each time the user makes a search into a dataset but thi
s
takes time and resources and it is likely that the user might want to change
their search after seeing that hundreds of properties have met their criteri
a
etc. Alternatively, I could have the user's search run a stored procedure
that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
user decides to keep looking fetches the next 10 rows etc. I understand how
the cursor in the stored procedure works and I can estimate its effect on
locking and even change its isolation level based on network traffic etc.
Performance wont be a factor at all when dealing with only 10 rows. The
variable which stores how deep the user was in the database could exist in
the application so the cursor can be closed and reopened each time the user
selects next so it doesnt hold locks etc. Or, and this is the thing I dont
get, I could use the ExecuteReader() object from .NET to access the database
.
I suppose that since this is using ADO it is not an API cursor, but then wha
t
is it' what is going on in SQL when an outside application runs a cursor in
SQL via ADO. What is the concurrency price of possible simultaneous
SELECT/UPDATE to an SQL DB from inside a company with people accessing it
over the internet with a web application? If .NET doesnt use API cursors
when it reads data from SQL with ADO then what does it use? and if it isnt
API cursors then what is the fuss over API cursors that I see mentioned
everywhere in SQL books and where would they ever concievably appear?
Message posted via http://www.droptable.com|||CharlesT via droptable.com wrote:
> okay, well suppose I have an SQL database of availible rental properties.
> People in a company update this database frequently. They want to make a .
NET
> application that will allow user on the internet to search this database -
> users would be able to find online what properties are availible and their
> rates and pictures etc. Based on whatever search values the user inputs
> their result set may be unpredicably large. I could make .NET simply load
> the entire database each time the user makes a search into a dataset but t
his
> takes time and resources and it is likely that the user might want to chan
ge
> their search after seeing that hundreds of properties have met their crite
ria
> etc. Alternatively, I could have the user's search run a stored procedure
> that uses a cursor on the SQL database to fetch the 1st 10 rows, then if t
he
> user decides to keep looking fetches the next 10 rows etc. I understand ho
w
> the cursor in the stored procedure works and I can estimate its effect on
> locking and even change its isolation level based on network traffic etc.
> Performance wont be a factor at all when dealing with only 10 rows. The
> variable which stores how deep the user was in the database could exist in
> the application so the cursor can be closed and reopened each time the use
r
> selects next so it doesnt hold locks etc. Or, and this is the thing I dont
> get, I could use the ExecuteReader() object from .NET to access the databa
se.
> I suppose that since this is using ADO it is not an API cursor, but then w
hat
> is it' what is going on in SQL when an outside application runs a cursor
in
> SQL via ADO. What is the concurrency price of possible simultaneous
> SELECT/UPDATE to an SQL DB from inside a company with people accessing it
> over the internet with a web application? If .NET doesnt use API cursors
> when it reads data from SQL with ADO then what does it use? and if it isn
t
> API cursors then what is the fuss over API cursors that I see mentioned
> everywhere in SQL books and where would they ever concievably appear?
> --
> Message posted via http://www.droptable.com
For your scenario you should take a look at some of the solutions
offered here:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
What exactly is the difference between a Tsql cursor and an API cursor? When
I access a db from a .NET application is this an API cursor? is an API curso
r
the same as a server cursor? does an API cursor/ server cursor hold any lock
s
on the db? does a .NET application? BOL says to use a 'server' cursor as the
prefered methos to give a web app user a result set from an aribtrary query
defined by the user which might be unpredicably large so that you can fetch
n
rows at a time without bringing the entire result set in and without holding
locks. But I cant find anywhere else in BOL what exactly this 'server cursor
'
is how and if it differs from a Tsql cursor called by a stored procedure or
something. Can anyone explain this?Hi Charles,
Here is some information...
T-SQL cursors are used in stored production and query development in
applications like Query Analyzer. Since SQL is, for the most part, a batch
processing language, the support for row-by-row processing (using a cursor)
is necessary is some situations. Other than learning about them for
troubleshooting purposes, I would avoid using.
API cursors (which can be server-side or client-side) are used in
application development, like VB, VC, not ADO.NET (due to poor performance,
cursors are not supported in ADO.NET). If you still want to use a cursor, a
previous version of ADO will need to be used. With the new SqlDataReader
object, you can read the data very quickly into a collection and process the
data row-by-row.
API cursors, as with all database transactions, acquire locks. For more
info on locking types and duration, I suggest reading about locking modes an
d
transaction isolation levels.
A book I suggest...'Inside Microsoft SQL Server 2000' by Delaney/Soukup,
Microsoft Press.
HTH,
Adam
"CharlesT" wrote:
> Im really confused about cursor stuff that isnt quite explained in the BOL
.
> What exactly is the difference between a Tsql cursor and an API cursor? Wh
en
> I access a db from a .NET application is this an API cursor? is an API cur
sor
> the same as a server cursor? does an API cursor/ server cursor hold any lo
cks
> on the db? does a .NET application? BOL says to use a 'server' cursor as t
he
> prefered methos to give a web app user a result set from an aribtrary quer
y
> defined by the user which might be unpredicably large so that you can fetc
h n
> rows at a time without bringing the entire result set in and without holdi
ng
> locks. But I cant find anywhere else in BOL what exactly this 'server curs
or'
> is how and if it differs from a Tsql cursor called by a stored procedure o
r
> something. Can anyone explain this?
>|||okay, well suppose I have an SQL database of availible rental properties.
People in a company update this database frequently. They want to make a .NE
T
application that will allow user on the internet to search this database -
users would be able to find online what properties are availible and their
rates and pictures etc. Based on whatever search values the user inputs
their result set may be unpredicably large. I could make .NET simply load
the entire database each time the user makes a search into a dataset but thi
s
takes time and resources and it is likely that the user might want to change
their search after seeing that hundreds of properties have met their criteri
a
etc. Alternatively, I could have the user's search run a stored procedure
that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
user decides to keep looking fetches the next 10 rows etc. I understand how
the cursor in the stored procedure works and I can estimate its effect on
locking and even change its isolation level based on network traffic etc.
Performance wont be a factor at all when dealing with only 10 rows. The
variable which stores how deep the user was in the database could exist in
the application so the cursor can be closed and reopened each time the user
selects next so it doesnt hold locks etc. Or, and this is the thing I dont
get, I could use the ExecuteReader() object from .NET to access the database
.
I suppose that since this is using ADO it is not an API cursor, but then wha
t
is it' what is going on in SQL when an outside application runs a cursor in
SQL via ADO. What is the concurrency price of possible simultaneous
SELECT/UPDATE to an SQL DB from inside a company with people accessing it
over the internet with a web application? If .NET doesnt use API cursors
when it reads data from SQL with ADO then what does it use? and if it isnt
API cursors then what is the fuss over API cursors that I see mentioned
everywhere in SQL books and where would they ever concievably appear?
Message posted via http://www.droptable.com|||CharlesT via droptable.com wrote:
> okay, well suppose I have an SQL database of availible rental properties.
> People in a company update this database frequently. They want to make a .
NET
> application that will allow user on the internet to search this database -
> users would be able to find online what properties are availible and their
> rates and pictures etc. Based on whatever search values the user inputs
> their result set may be unpredicably large. I could make .NET simply load
> the entire database each time the user makes a search into a dataset but t
his
> takes time and resources and it is likely that the user might want to chan
ge
> their search after seeing that hundreds of properties have met their crite
ria
> etc. Alternatively, I could have the user's search run a stored procedure
> that uses a cursor on the SQL database to fetch the 1st 10 rows, then if t
he
> user decides to keep looking fetches the next 10 rows etc. I understand ho
w
> the cursor in the stored procedure works and I can estimate its effect on
> locking and even change its isolation level based on network traffic etc.
> Performance wont be a factor at all when dealing with only 10 rows. The
> variable which stores how deep the user was in the database could exist in
> the application so the cursor can be closed and reopened each time the use
r
> selects next so it doesnt hold locks etc. Or, and this is the thing I dont
> get, I could use the ExecuteReader() object from .NET to access the databa
se.
> I suppose that since this is using ADO it is not an API cursor, but then w
hat
> is it' what is going on in SQL when an outside application runs a cursor
in
> SQL via ADO. What is the concurrency price of possible simultaneous
> SELECT/UPDATE to an SQL DB from inside a company with people accessing it
> over the internet with a web application? If .NET doesnt use API cursors
> when it reads data from SQL with ADO then what does it use? and if it isn
t
> API cursors then what is the fuss over API cursors that I see mentioned
> everywhere in SQL books and where would they ever concievably appear?
> --
> Message posted via http://www.droptable.com
For your scenario you should take a look at some of the solutions
offered here:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
API cursors?
Im really confused about cursor stuff that isnt quite explained in the BOL.
What exactly is the difference between a Tsql cursor and an API cursor? When
I access a db from a .NET application is this an API cursor? is an API cursor
the same as a server cursor? does an API cursor/ server cursor hold any locks
on the db? does a .NET application? BOL says to use a 'server' cursor as the
prefered methos to give a web app user a result set from an aribtrary query
defined by the user which might be unpredicably large so that you can fetch n
rows at a time without bringing the entire result set in and without holding
locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
is how and if it differs from a Tsql cursor called by a stored procedure or
something. Can anyone explain this?Hi Charles,
Here is some information...
T-SQL cursors are used in stored production and query development in
applications like Query Analyzer. Since SQL is, for the most part, a batch
processing language, the support for row-by-row processing (using a cursor)
is necessary is some situations. Other than learning about them for
troubleshooting purposes, I would avoid using.
API cursors (which can be server-side or client-side) are used in
application development, like VB, VC, not ADO.NET (due to poor performance,
cursors are not supported in ADO.NET). If you still want to use a cursor, a
previous version of ADO will need to be used. With the new SqlDataReader
object, you can read the data very quickly into a collection and process the
data row-by-row.
API cursors, as with all database transactions, acquire locks. For more
info on locking types and duration, I suggest reading about locking modes and
transaction isolation levels.
A book I suggest...'Inside Microsoft SQL Server 2000' by Delaney/Soukup,
Microsoft Press.
HTH,
Adam
"CharlesT" wrote:
> Im really confused about cursor stuff that isnt quite explained in the BOL.
> What exactly is the difference between a Tsql cursor and an API cursor? When
> I access a db from a .NET application is this an API cursor? is an API cursor
> the same as a server cursor? does an API cursor/ server cursor hold any locks
> on the db? does a .NET application? BOL says to use a 'server' cursor as the
> prefered methos to give a web app user a result set from an aribtrary query
> defined by the user which might be unpredicably large so that you can fetch n
> rows at a time without bringing the entire result set in and without holding
> locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
> is how and if it differs from a Tsql cursor called by a stored procedure or
> something. Can anyone explain this?
>|||okay, well suppose I have an SQL database of availible rental properties.
People in a company update this database frequently. They want to make a .NET
application that will allow user on the internet to search this database -
users would be able to find online what properties are availible and their
rates and pictures etc. Based on whatever search values the user inputs
their result set may be unpredicably large. I could make .NET simply load
the entire database each time the user makes a search into a dataset but this
takes time and resources and it is likely that the user might want to change
their search after seeing that hundreds of properties have met their criteria
etc. Alternatively, I could have the user's search run a stored procedure
that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
user decides to keep looking fetches the next 10 rows etc. I understand how
the cursor in the stored procedure works and I can estimate its effect on
locking and even change its isolation level based on network traffic etc.
Performance wont be a factor at all when dealing with only 10 rows. The
variable which stores how deep the user was in the database could exist in
the application so the cursor can be closed and reopened each time the user
selects next so it doesnt hold locks etc. Or, and this is the thing I dont
get, I could use the ExecuteReader() object from .NET to access the database.
I suppose that since this is using ADO it is not an API cursor, but then what
is it' what is going on in SQL when an outside application runs a cursor in
SQL via ADO. What is the concurrency price of possible simultaneous
SELECT/UPDATE to an SQL DB from inside a company with people accessing it
over the internet with a web application? If .NET doesnt use API cursors
when it reads data from SQL with ADO then what does it use? and if it isnt
API cursors then what is the fuss over API cursors that I see mentioned
everywhere in SQL books and where would they ever concievably appear?
--
Message posted via http://www.sqlmonster.com|||CharlesT via SQLMonster.com wrote:
> okay, well suppose I have an SQL database of availible rental properties.
> People in a company update this database frequently. They want to make a .NET
> application that will allow user on the internet to search this database -
> users would be able to find online what properties are availible and their
> rates and pictures etc. Based on whatever search values the user inputs
> their result set may be unpredicably large. I could make .NET simply load
> the entire database each time the user makes a search into a dataset but this
> takes time and resources and it is likely that the user might want to change
> their search after seeing that hundreds of properties have met their criteria
> etc. Alternatively, I could have the user's search run a stored procedure
> that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
> user decides to keep looking fetches the next 10 rows etc. I understand how
> the cursor in the stored procedure works and I can estimate its effect on
> locking and even change its isolation level based on network traffic etc.
> Performance wont be a factor at all when dealing with only 10 rows. The
> variable which stores how deep the user was in the database could exist in
> the application so the cursor can be closed and reopened each time the user
> selects next so it doesnt hold locks etc. Or, and this is the thing I dont
> get, I could use the ExecuteReader() object from .NET to access the database.
> I suppose that since this is using ADO it is not an API cursor, but then what
> is it' what is going on in SQL when an outside application runs a cursor in
> SQL via ADO. What is the concurrency price of possible simultaneous
> SELECT/UPDATE to an SQL DB from inside a company with people accessing it
> over the internet with a web application? If .NET doesnt use API cursors
> when it reads data from SQL with ADO then what does it use? and if it isnt
> API cursors then what is the fuss over API cursors that I see mentioned
> everywhere in SQL books and where would they ever concievably appear?
> --
> Message posted via http://www.sqlmonster.com
For your scenario you should take a look at some of the solutions
offered here:
http://www.aspfaq.com/show.asp?id=2120
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
What exactly is the difference between a Tsql cursor and an API cursor? When
I access a db from a .NET application is this an API cursor? is an API cursor
the same as a server cursor? does an API cursor/ server cursor hold any locks
on the db? does a .NET application? BOL says to use a 'server' cursor as the
prefered methos to give a web app user a result set from an aribtrary query
defined by the user which might be unpredicably large so that you can fetch n
rows at a time without bringing the entire result set in and without holding
locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
is how and if it differs from a Tsql cursor called by a stored procedure or
something. Can anyone explain this?Hi Charles,
Here is some information...
T-SQL cursors are used in stored production and query development in
applications like Query Analyzer. Since SQL is, for the most part, a batch
processing language, the support for row-by-row processing (using a cursor)
is necessary is some situations. Other than learning about them for
troubleshooting purposes, I would avoid using.
API cursors (which can be server-side or client-side) are used in
application development, like VB, VC, not ADO.NET (due to poor performance,
cursors are not supported in ADO.NET). If you still want to use a cursor, a
previous version of ADO will need to be used. With the new SqlDataReader
object, you can read the data very quickly into a collection and process the
data row-by-row.
API cursors, as with all database transactions, acquire locks. For more
info on locking types and duration, I suggest reading about locking modes and
transaction isolation levels.
A book I suggest...'Inside Microsoft SQL Server 2000' by Delaney/Soukup,
Microsoft Press.
HTH,
Adam
"CharlesT" wrote:
> Im really confused about cursor stuff that isnt quite explained in the BOL.
> What exactly is the difference between a Tsql cursor and an API cursor? When
> I access a db from a .NET application is this an API cursor? is an API cursor
> the same as a server cursor? does an API cursor/ server cursor hold any locks
> on the db? does a .NET application? BOL says to use a 'server' cursor as the
> prefered methos to give a web app user a result set from an aribtrary query
> defined by the user which might be unpredicably large so that you can fetch n
> rows at a time without bringing the entire result set in and without holding
> locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
> is how and if it differs from a Tsql cursor called by a stored procedure or
> something. Can anyone explain this?
>|||okay, well suppose I have an SQL database of availible rental properties.
People in a company update this database frequently. They want to make a .NET
application that will allow user on the internet to search this database -
users would be able to find online what properties are availible and their
rates and pictures etc. Based on whatever search values the user inputs
their result set may be unpredicably large. I could make .NET simply load
the entire database each time the user makes a search into a dataset but this
takes time and resources and it is likely that the user might want to change
their search after seeing that hundreds of properties have met their criteria
etc. Alternatively, I could have the user's search run a stored procedure
that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
user decides to keep looking fetches the next 10 rows etc. I understand how
the cursor in the stored procedure works and I can estimate its effect on
locking and even change its isolation level based on network traffic etc.
Performance wont be a factor at all when dealing with only 10 rows. The
variable which stores how deep the user was in the database could exist in
the application so the cursor can be closed and reopened each time the user
selects next so it doesnt hold locks etc. Or, and this is the thing I dont
get, I could use the ExecuteReader() object from .NET to access the database.
I suppose that since this is using ADO it is not an API cursor, but then what
is it' what is going on in SQL when an outside application runs a cursor in
SQL via ADO. What is the concurrency price of possible simultaneous
SELECT/UPDATE to an SQL DB from inside a company with people accessing it
over the internet with a web application? If .NET doesnt use API cursors
when it reads data from SQL with ADO then what does it use? and if it isnt
API cursors then what is the fuss over API cursors that I see mentioned
everywhere in SQL books and where would they ever concievably appear?
--
Message posted via http://www.sqlmonster.com|||CharlesT via SQLMonster.com wrote:
> okay, well suppose I have an SQL database of availible rental properties.
> People in a company update this database frequently. They want to make a .NET
> application that will allow user on the internet to search this database -
> users would be able to find online what properties are availible and their
> rates and pictures etc. Based on whatever search values the user inputs
> their result set may be unpredicably large. I could make .NET simply load
> the entire database each time the user makes a search into a dataset but this
> takes time and resources and it is likely that the user might want to change
> their search after seeing that hundreds of properties have met their criteria
> etc. Alternatively, I could have the user's search run a stored procedure
> that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
> user decides to keep looking fetches the next 10 rows etc. I understand how
> the cursor in the stored procedure works and I can estimate its effect on
> locking and even change its isolation level based on network traffic etc.
> Performance wont be a factor at all when dealing with only 10 rows. The
> variable which stores how deep the user was in the database could exist in
> the application so the cursor can be closed and reopened each time the user
> selects next so it doesnt hold locks etc. Or, and this is the thing I dont
> get, I could use the ExecuteReader() object from .NET to access the database.
> I suppose that since this is using ADO it is not an API cursor, but then what
> is it' what is going on in SQL when an outside application runs a cursor in
> SQL via ADO. What is the concurrency price of possible simultaneous
> SELECT/UPDATE to an SQL DB from inside a company with people accessing it
> over the internet with a web application? If .NET doesnt use API cursors
> when it reads data from SQL with ADO then what does it use? and if it isnt
> API cursors then what is the fuss over API cursors that I see mentioned
> everywhere in SQL books and where would they ever concievably appear?
> --
> Message posted via http://www.sqlmonster.com
For your scenario you should take a look at some of the solutions
offered here:
http://www.aspfaq.com/show.asp?id=2120
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Subscribe to:
Posts (Atom)