ADO cursorType/lockType changes automatically

  • I don't know if this is the correct forum for this query (apologies if it isn't), but I'm experiencing a problem when opening an ADODB.Recordset that selects records from a SQL view that uses a table-type UDF in its definition.

    When I open keyset/lockOptimistic recordsets that select records from other views it works fine, but if the view definition uses a table-type UDF the cursorType changes to forwardOnly, and the lockType changes to readOnly.

    Eg (simplified version - the UDF does do a lot more really):

    CREATE FUNCTION dbo.myUDF()

    RETURNS @results TABLE (id integer)

    AS

    BEGIN

        INSERT @results (id) VALUES(1)

        RETURN

    END

    CREATE VIEW dbo.myView

    AS

        SELECT myTable.*

        FROM myTable

        WHERE myTable.id IN (SELECT id FROM dbo.myUDF())

    Does anyone know how I can open a keysey/lockOptimistic ADO recordset that selects records from such a view WITHOUT it automatically changing to forwardOnly/lockReadOnly? CursorLocation is server-side if that helps.

    Thanks

  • More information :

    • CursorLocation is adUseServer (recordset works fine if opened as a client-side cursor, but I really need it to be server-side)
    • SQLOLEDB is the provider for my ADO connection

    Thanks

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply