Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    6

    Unanswered: Indexes on Computed columns and ADO

    OK maybe this crosses the SQLServer forum (apologies and dlet me know if so) BUT:

    I have a composite PK/index with a computed column (basically CASTing a bit value). The index is fine in SQL2K however when I try to access the table through an ADO recordset (to a VBA form) the Datasheet control is locked.

    Seems like a behind the scene ADO cursor-type/lock-type issue. ANY input is welcome I AM UNDER THE PRODUCTION GUN.

    TIA

  2. #2
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: Indexes on Computed columns and ADO

    You can try to set the "arithmetic abort" property to on (in SQlServer>properties>connection tab).

    I had the same problem, in one of my application, except there I was logging on to the server via ODBC drivers, not OLEDB (as ADO usualy does).

    I hope it works. Good luck!

    IONUT

  3. #3
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    Re: Indexes on Computed columns and ADO

    Originally posted by zarathustra
    OK maybe this crosses the SQLServer forum (apologies and dlet me know if so) BUT:

    I have a composite PK/index with a computed column (basically CASTing a bit value). The index is fine in SQL2K however when I try to access the table through an ADO recordset (to a VBA form) the Datasheet control is locked.

    Seems like a behind the scene ADO cursor-type/lock-type issue. ANY input is welcome I AM UNDER THE PRODUCTION GUN.

    TIA
    Try what happens if you lose the computed column in the primary key and replace it by the column(s) that the computed column was based on. There should be no need to have a computed column in the primary key.

    I must admit that I also had trouble with ADO when using computed columns and I quickly ended up avoiding computed columns, not wanting to spend the time to find how to solve the problems.

  4. #4
    Join Date
    Feb 2002
    Posts
    9

    Re: Indexes on Computed columns and ADO

    Originally posted by Ad Dieleman
    Try what happens if you lose the computed column in the primary key and replace it by the column(s) that the computed column was based on. There should be no need to have a computed column in the primary key.

    I must admit that I also had trouble with ADO when using computed columns and I quickly ended up avoiding computed columns, not wanting to spend the time to find how to solve the problems.
    I agree about not having the PK contain a computed val but IXs can't have bit values. Hence the comp'd field. I've got it working now. I've seen issues as well with computed fields/ADO and connection settings like ARITHABORT etc.... joy.

    thanks to you both.

Posting Permissions

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