Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    USA - Nebraska
    Posts
    2

    Angry Unanswered: DB Maint Plan Delima - Error 22029

    Howdy all - hope someone can offer me assistance!

    My problem stems from a recent SQL database move to SQL 2000 from SQL Server 7.0. I successfully moved 8 databases from SQL Server 7 to SQL 2000 (using series of scripts that detached/attached dbs - see URL http://www.sqlteam.com/item.asp?ItemID=9465). My problem is with the Database Maintenance Plans, as I am receiving the following error message when attempting to manually execute a 'Job' from SQL Server Agents 'Jobs':

    "Executed as user: NT AUTHORITY\SYSTEM. SQLMaint.exe failed. [SQLSTATE 42000] (Error 22029), the step failed"

    Oddly, after I used a script to move the SQL Server Agent "Jobs" between servers, the new SQL 2000 Enterprise Manager displays each job, but there are not any 'Database Maintenance Plans' showing.

    Any thoughts as to the "22029" error or the lack of Database Maint Plans?

    Confused!?!

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Arrow Re: DB Maint Plan Delima - Error 22029

    1 Assuming you did not move MSDB: The maintenance plan references would be incorrect for scripts made on 7.0 maintenance plan jobs (they would refer to non-existent maintenance plan IDs in your 2k instance).

    2 You may wish to simply recreate your maintenance plans via the wizard on your 2k instance.

    3 Another option may be to upgrade the source (7.0 --> 2k), then do a backup dump of MSDB (from the upgraded 2k source), and then restore that MSDB dump to the new 2k instance.

  3. #3
    Join Date
    Dec 2002
    Location
    USA - Nebraska
    Posts
    2

    Re: DB Maint Plan Delima - Error 22029

    DBA,

    You are correct on #1 - I did not move the MSDB table from 7.0 to 2000. I have already planned on your #2 option, as creating the new maint plans on the 2000 server is a viable solution. The #3 option sounds interesting (I would have to perform this prior to #2, of course) - I might attempt this as I still have my source (7.0) server and all databases intact (all I need to do is reattach the dbs). I'll let you know what I come up with if I decide on this option.

    ...very good info/observations! Thanks DBA!

  4. #4
    Join Date
    Oct 2002
    Posts
    369

    Re: DB Maint Plan Delima - Error 22029

    You are welcome.

    (It sounds like you may quite conveniently redo or restore. On large systems with many maintenance plans, redoing them and running dozens to hundreds of scripts for custom jobs, msdb stored procedures, alerts, etc., can be a very tedious chore.)

Posting Permissions

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