Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Australia
    Posts
    46

    Question Unanswered: Stored Procedure Syntax??

    Hi All,

    Can Anyone help me with this?

    Where can I find the Actual syntax for the following Stored Procedures

    Sp_CurorFetch
    Sp_CursorExecute
    Sp_CursorOpen
    Sp_CursorClose

    I would like to know what's the syntax for what values you pass in the statement as well as what they actually do and if and what values are returned.
    I have a Database Trace on some activity and I am now sifting through the code trying to figure out the sequence of things to determine the actual process

    How can I get this Information??

    Thanks
    Anthony

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: Stored Procedure Syntax??

    RE:
    Hi All,
    Q1 Can Anyone help me with this?

    Where can I find the Actual syntax for the following Stored Procedures
    Sp_CursorFetch
    Sp_CursorExecute
    Sp_CursorOpen
    Sp_CursorClose
    I would like to know what's the syntax for what values you pass in the statement as well as what they actually do and if and what values are returned.
    I have a Database Trace on some activity and I am now sifting through the code trying to figure out the sequence of things to determine the actual process
    Q2 How can I get this Information?? Thanks Anthony
    A1 Yes. Those are some of the special system stored procedures implemented on Sql Server to return data (API Server Cursors). The OLE DB provider, ODBC driver, and DB-Lib use them to interact with / control cursor operations. For example: A sp_cursorfetch call fetches a row (or block of rows) for an API cursor; changing the ADO CacheSize property is one way to make sp_cursorfetch return more than one row i.e.(the procs are used by the API, and not normally directly called by applications / users).

    A2 For additional background information, see:
    http://msdn.microsoft.com/library/de...on_07_7xpv.asp

    (see msdn documentation pertaining to writing API code for specifics such as API cursor parameters, etc.)

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Here's a comment from Kalen Delaney (I think) some time ago - may have changed now. I would have thought it is in the driver dev quide.

    There is never a need for you to call sp_cursoropen directly. It was not written to be accessed by user applications and the parameters are not documented anywhere. It really is completely for internal use. If you need to define a cursor, you can either use ODBC's cursor calls, or TSQL statements. This procedure is really not a stored procedure at all in that it is not defined as SQL code. It's just an entry point into the server's kernel code, that can be processed like a stored procedure by the ODS layer.

    If you look at the syntax for dbcursoropen, dbcursorfetch,... it closely matches the syntax of these XPs.

    The following gives a hint as to what is happenning and an example.

    @a = cursor pointer

    @b = scrollopt
    CUR_DYNAMIC
    CUR_FORWARD
    CUR_KEYSET
    CUR_INSENSITIVE

    @c = ccopt
    CUR_READONLY
    CUR_LOCKCC
    CUR_OPTCC
    CUR_OPTCCVAL

    @d = record count (in cursor and returned by fetch)

    declare @a int, @sql nvarchar(1000), @b int, @c int, @d int
    select @b = 1
    select @c = 8

    select @sql = 'select top 30 * from sysobjects'

    exec sp_cursoropen @a out, @sql, @b out, @c out, @d out

    select @d = @d/3
    exec sp_cursorfetch @a, 2, 1, @d
    exec sp_cursorfetch @a, 2, 1, @d
    exec sp_cursorfetch @a, 2, 1, @d

    exec sp_cursorclose @a
    Last edited by nigelrivett; 01-30-03 at 00:06.

Posting Permissions

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