-
sp_cursoropen
declare @P1 int set @P1=180150000
declare @P2 int set @P2=1
declare @P3 int set @P3=16388
declare @P4 int set @P4=4
exec sp_cursoropen
@P1 output,
N'Select * from MyTable
Where LoggedOn = ''Y''' ,
@P2 output,
@P3 output,
@P4 output
select
@P1 as 'Cursor',
@P2 as 'ScrollOption',
@P3 as 'ConcurrencyOption',
@P4 as 'RowCount'
- What happens when exec sp_cursoropen gets run consecutively & numerously (with the same command)?
- Does exec sp_cursoropen have to have a corresponding "sp_cursorclose" ?
-Anybody experience with these system procedures? thanks
-
- What happens when exec sp_cursoropen gets run consecutively & numerously (with the same command)?
A new cursor gets opened every time. If you put it in a loop, it will eventually eat up all of SQL Server's memory and affect performance. May even crash it or cause it to freeze up.
- Does exec sp_cursoropen have to have a corresponding "sp_cursorclose" ?
Unless you want the memory to remain used up until the connection is closed or times out.
-Anybody experience with these system procedures?
No. They're not really there for our use. SQL Server uses them when we open, close, and use cursors.
-
Thanks RawHide.
Im just abit confused because SQL Trace is showing that its executing sp_cursoropen with the same command FREQUENTLY, and I cannot find even 1 "sp_cursorclose". Could it be that it might be closed once the data connection is terminated?
I think the app (im not much of a programmer) is using an ADO connection to call these cursors and populate some parameters frequently.
thanks for any help or opinion - will be great
-
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|