SQL Server Performance

ADO Recordset, Cursor And Stored Procedures

Discussion in 'T-SQL Performance Tuning for Developers' started by boim, Feb 10, 2003.

  1. boim New Member

    Hi
    I have a problem changing the cursor type for the ADO Recordset when i am querying SQL SERVER 2000.
    when i run a simple query, like "select * from dsa" and on the recordset i change the cursor type to static (not only the move forward) i can see the property "recordcount" or i can moveback in the recordset.
    but, when i use the exact same query, but i do it in a stored procedure (the recordset opens the Stored procedue), the cursor is only moveforward (althought i changed it!@)
    because of it i cant use moveback and recordcount.
    what can i do? i have to use both the moveback and also Stored Procedures..
  2. axlcrew New Member

    Are you executing the SQL statement with the ADO connection object, or the command object? if the command object, which command type are you using?
  3. Argyle New Member

    As mentioned, how are you executing the stored procedure? The easiest way to have control over your recordset and SP is to use the command object.<br /><br />An example of an ASP function:<br /><pre>---------------------------<br />Function GetAuthor(authorID)<br />Dim rs, cmd<br /><br />'Instantiate objects<br />Set rs = Server.CreateObject("adodb.Recordset")<br />Set cmd = Server.CreateObject("adodb.Command")<br /><br />'Open connection <br />cmd.ActiveConnection = GetConnectionString()<br /> <br />'Specify SP<br />cmd.CommandText = "dbo.GetAuthorByAuthorID"<br />cmd.CommandType = adCmdStoredProc<br /><br />'Specify parameters<br />cmd.Parameters.Append cmd.CreateParameter("@authorID", adInteger, adParamInput, , authorID)<br /> <br />'Execute the query with clientside cursor<br />rs.CursorLocation = adUseClient<br />rs.Open cmd, , adOpenStatic, adLockReadOnly<br /><br />'Disconnect the recordset and clean up<br />Set cmd.ActiveConnection = Nothing<br />Set cmd = Nothing<br /><br />'Return the recordset<br />Set GetAuthor = rs<br />End Function<br /><br />Function GetConnectionString() <br /> GetConnectionString = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=MyDB;User Id=MyUser<img src='/community/emoticons/emotion-4.gif' alt=';P' />assword=MyPass;App=MyApp"<br />End Function <br />---------------------------</pre><br />/Argyle
  4. boim New Member

    i am opening it with the connection.

    for example,

    set objConn = new adodb.connection
    set rTemp = new adodb.recordset
    ....
    rTemp.Open "sp_test",objConn,3

    where 3 is the cursor type.

    do you the problem? cause if i use "select * from.." instead of the "sp_test" it does work!
  5. Argyle New Member

    Strange. I've tried your exact syntax and I get a static cursor in both scenarios.

    I have MDAC 2.6 on the web server, connecting to a SQL 2000 SP3

    /Argyle
  6. boim New Member

    i have the same configuration, just MDAC2.7

    anyway, try to use local Variables in the stored procedure.
    i think i read thats the prob..

    do you get the error?
    do you have any idea how to overcome it!?@
  7. Argyle New Member

    Can't see how local variables in the SP could affect the cursor type. I know SELECT TOP statments can affect your cursor type but only if you have a locking type different from read only.

    Could you post an example of your SP code?

    To overcome it try and execute the SP the way I did in a previous post.

    /Argyle
  8. boim New Member

    it does affect. i read an article about it on the MSDN...
  9. Argyle New Member

    Can you please post the link to the article. Would be interesting to read.

    /Argyle

Share This Page