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?
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.
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.
(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.)