Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721

    Unanswered: 800a0cb3 Error in DTS Package

    I've been working on a DTS Package that runs every 10 minutes to check for new records to a table and sends out notification emails accordingly. After the email is sent, the new record is updated with a flag so it will not be pulled and emailed again.

    This package worked fine for a week (and is currently working - exact same code - in another SQL Server).

    As of last night, this process started throwing the following error:

    Code:
    Step Error Source: Microsoft Data Transformation Services (DTS) Package
    Step Error Description:Error Code: 0
    Error Source= ADODB.Recordset
    Error Description: Current Recordset does not support updating. This may be a limitation of the provider,
    or of the selected locktype.
    
    Error on Line 110
     (ADODB.Recordset (800a0cb3): Current Recordset does not support updating. This may be a limitation
    of the provider, or of the selected locktype.)
    Step Error code: 800403FE
    Step Error Help File:sqldts80.hlp
    Step Error Help Context ID:1100
    Thinking I may have screwed something up, I moved the functioning code from the other SQL Server back to the non-functioning SQL Server, and the problem persists. I stopped and restarted the SQL Server (but have not yet rebooted the server it's on), also with no results.

    The package is a single ActiveX object written in VB to open up a recordset of unprocessed records (KeySet and Lock Optomistic), send out notification emails, flag the files, then close. The error occurs at the updating of the flag.

    What is even more strange, is that this is happening on our development SQL server. Our production SQL server seems unaffected. I originally developed and tested this code on the dev box, and when I was satisfied, I moved it to production for testing. Then last night the dev server stopped functioning, but the production server shows no sign of problems.

    I was curious if anyone else has had an experience with a DTS package throwing a similar error.

    Any ideas?
    That which does not kill me postpones the inevitable.

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    I took the ActiveX code and copied it to VB and ran it.. when it's pointing to the server in question, the error occurs... when I change the server name to point to the production server, the same code functions properly.... amazing.....
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    I temporarily managed to resolve the problem by rebuilding the packages from scratch. I do not know if this fixed it, or if it was just coincidence that I did this at the same time something else happened, but it began working again.

    Unfortunately, as of this afternoon, the production and staging environments redeveloped the problem and my "solution" did not fix it.

    Thinking my transaction log may be filled I checked the settings. The DB and the Transaction log are both set to unlimited growth and to grow automatically.

    At this point I'm baffled. I have a DTS package on 3 servers... all three running successfully for days on end... spontaneously two of them stop working giving the error in the original post.... very very troubling...
    That which does not kill me postpones the inevitable.

  4. #4
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Would you believe I came across something that may have fixed it right after I posted? It wouldn't be the first time...

    I found a reference to the same error code, but different error message, in regard to an Oracle error. The solution was to make the recordset a client-side recordset. I had originally thought of this, but the DTS package is in SQL Server, so my thinking was, when this runs, there is no client, so it has to run as a server-side recordset. When I changed ADO to use a client-side recordset (which is still the SQL Server), it started working again. I changed it back, it failed, put it back in again, it worked.

    Very strange... why would you need to declare a recordset to be client-side, even though it's running in the SQL Server itself? Since DTS runs under a separate agent, does that count as a "client"?
    That which does not kill me postpones the inevitable.

Posting Permissions

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