Re: Stored Procedures and RecordSets

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Stephen Howe wrote:
To date, all my Stored Procedures have been either actions (INSERT,
UPDATE or DELETE) or a single-row input/output of parameters. I am
mystified as to what needs to happen with regards to CursorLocation,
CursorType, LockType. Do I setup CursorLocation, CursorType,
LockType
in the Stored Procedure or does that happen with ADO's Command &
RecordSet object?

I do it with the ADO properties myself. Retrieving a recordset from
a stored
procedure is exactly the same as doing it with an ad hoc sql
statement.

Thanks. What I am mystified about is how the SP "knows" how to
communicate the recordset back.
If the Recordset was Server-sided, Forward-only, Read-only, then a
cursor has to be opened SQL Server.
How does the SP know that it needs to do this?
There is some aspect to this that I am missing.

The SP doesn't. It's the query engine that's running the queries in the
compiled procedure that is doing the work. And the query engine is
responding to the request received from ADO.

Speculation:
Ah, I wonder?
Perhaps the Recordset and/or ADO does what is necessary in terms of
opening and creating rowsets.
And then when the SP runs, it just fills what is there.
It is rather like a file handle and OPEN in VB. Perhaps the SP just
write records to whatever the "file handle" points to.

Close. I don't know the exact details - for those, you would need someone
like Bill Vaughn or David Sceppa. But it is something like that. ADO
requests the query engine to run the procedure and return the results with
the requested cursor type. Exactly how it does that depends on the provider
being used. The MSDASQL provider uses a different mechanism than the native
SQLOLEDB provider uses.

Of course, you do realize that the cursor and lock type you request might
not be the cursor and lock type you get.

See this thread: http://tinyurl.com/3cl6e

IMO, this is a bug in the SQLOLEDB provider. Interestingly enough, there is
a similar but different bug in the ODBC driver. When using ODBC with the
tests I performed in that thread, I get these results:

Before open, using stored procedure executed via dynamic sql, cursortype = 3
After open, using stored procedure executed via dynamic sql, cursor type =
3; RecordCount = -1

Before open, using stored procedure executed via
procedure-as-connection-method, cursortype = 3
After open, using stored procedure executed via
procedure-as-connection-method, cursor type = 3; RecordCount = -1

Before open, using stored procedure executed via Command object, cursortype
= 3
After open, using stored procedure executed via Command object, cursor type
= 3; RecordCount = -1

Before open, using dynamic sql, cursortype = 3
After open, using dynamic sql, cursor type = 3; RecordCount = 23

So, even though it's giving us the "right" cursortype, the recordcount is
not known. When I add this line:
"rsTest.Supports(adBookmark)=" & rsTest.Supports(adBookmark)

I discover that the only time bookmarks are supported is when using a
dynamic sql statement to open the recordset, rather than the stored
procedure.

This is not the documented behavior.

Workaround: use a client-side cursor (rs.cursorlocation=3) when opening the
recordset on a procedure, or use a more efficient method of getting the
record count.


Just remember, if this is a SQL Server stored procedure, to have "SET
NOCOUNT ON" as the first line in your procedure, so you avoid the
extra
recordsets created by statements in your procedure that generate "x
rows
were affected by this query" messages.

Thank I know about "SET NOCOUNT ON".
Effectively it means the 2nd parameter of rthe Command Object is not
filled in on return.

Oh no, it's more than that. Those messages are sent as closed recordsets,
and you cannot see the results of the intended recordset until those are
handled. Thus the need for NextRecordset in these cases.

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: Gebundene Controls aus ADO-Recordset aktualisieren
    ... Engpass ist vor allem das LAN und der Server selbst. ... ob man mit einem SQL-Server oder der Jet-Engine ... Cursor und statischen Recordsets, egal welches Datenbanksystem ... dass eine Bewegung im Recordset eben auch ...
    (microsoft.public.de.vb.datenbank)
  • Re: Gebundene Controls aus ADO-Recordset aktualisieren
    ... Wozu ein serverseitiger Cursor? ... Ich kann also z.B. bei adOpenKeyset im Programmcode ... CursorLocation adUseServer bei Access sinnlos, ... wenn Du in Deinem Recordset zu einem anderen ...
    (microsoft.public.de.vb.datenbank)
  • Re: Suche mit SEEK
    ... ein Recordset mit serverseitigem Cursor ... Das heisst also es muss ein Recordset mit serverseitigem Cursor ... clientseitigem Cursor arbeiten und die Suche nach einem bestimmten Datensatz ...
    (microsoft.public.de.vb.datenbank)
  • RE: CursorType issue with .mdb and Excel VBA, ADO
    ... the cursor type of the recordset would be useless when populating the same ... So, as a solution to my problem, I must set the cursor type at the objConn ... > Dim cnUnit7_Log_Data As New Adodb.Connection ...
    (microsoft.public.data.ado)
  • Re: How to summarize recordset...Select Distinct alternative?
    ... functionality to store a set of paired values ... then the recordset is copied into local ... ADO provides a Cursor Library that provides the cursor ... reconnected to the database simply by setting the ActiveConnection to an ...
    (microsoft.public.data.ado)