Results 1 to 5 of 5

Thread: sp_cursoropen

  1. #1
    Join Date
    Feb 2003
    Location
    Aussie Land
    Posts
    114

    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

  2. #2
    Join Date
    Feb 2003
    Posts
    1,048
    - 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.

  3. #3
    Join Date
    Feb 2003
    Location
    Aussie Land
    Posts
    114
    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

  4. #4
    Join Date
    Feb 2003
    Posts
    1,048
    Here's an interesting article that talks about it: http://support.microsoft.com/default...b;en-us;253010

  5. #5
    Join Date
    Feb 2003
    Location
    Aussie Land
    Posts
    114
    thanks RawHide.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •