May 10, 2007 at 12:06 am
I've recently installed 32-bit SQL Server 2005 on a new server, then installed SP2, then installed Build 3054 (to bring it up to the same build level as my other servers) - see http://www.microsoft.com/downloads/details.aspx?familyid=D09CF5C4-FEC4-4322-9FEE-06A43401CF0C&displaylang=en.
I then created Database Maintenance Plans, but now get the error "Enumerate target servers failed for Job 'MaintenancePlan.SubPlan_1'. (Microsoft.SqlServer.Smo)" whenever I try to modify or delete any maintenance plan (or the Agent jobs they created).
Googling for this error shows that some others have had this problem but have not found a solution yet. Any suggestions?
Hopefully,
David.
May 10, 2007 at 7:47 am
David you are not alone. Same happened to me a couple of months ago. I posted the message here, even sent to a MS contact and got no answers. All happened after installing SP2a. The solution?. No other than re-install all over again, drastic but was the only wait I got back to my already created maintenance plans.
You will need to un-install SQL2005 (all components). Good luck.
May 10, 2007 at 4:30 pm
Thanks Camilo for replying and sharing your experience. My experience is a little different from yours, in that I don't have any pre-existent Maintenance Plans that I'm trying to save - mine are all new ones (for doing database backups and integrity checks).
I'm willing to go through the uninstall if it's the only way to fix it, but I'm not clear on how it will be better next time around. If the problem is due to a bug in the software, and if I uninstall everything, then follow the same installation steps (install base product, install SP2a, install Build 3054, then create my Maintenance Plans), then why would I get a different result? Or are you suggesting that I create the Maintenance Plans after I install the base product and before I install SP2a?
David.
May 11, 2007 at 12:04 pm
Sorry I cannot offer additional help. I took the programmer approach..."puched the car up the hill and puched back down". I did not have any more issues after the re-install. And nobody came up with an explanation either.
October 23, 2007 at 9:39 am
UPDATE: 11/12/2007
I left my original post below, because it might help someone somewhere.
The actions listed in the old post below help you to enumerate jobs which are not scheduled. The minute you try to have them run automatically, you'll get errors. The errors will be different, but it still won't work.
The problem is that your name is too long. Specifically, contains too many characters.
Ok, no problem, just fire up cliconfg and add an alias. Sorry, won't work. Didn't for me anyway.
Answer is to change the virtual server name. Instructions are at http://msdn2.microsoft.com/en-us/library/ms178083.aspx.
Here they are in case that link goes away:
1.) Using Cluster Administrator, change the SQL Network Name to the new name.
2.) Take the network name resource offline. This takes the SQL Server resource and other dependent resources offline as well.
3.) Bring the SQL Server resource back online.
Simple enough, right? One snag I hit after this is that I had Reporting Services already installed, and apparently the name update isn't kind with Reporting Services. I had to do a registry tweak to get it to work.
The error I received when I tried to update the virtual server name appeared thusly:
Application Error
Event Source: MSSQL$
Event ID: 19019
[sqsrvres] GetRegKeyAccessMask: Could not get registry access mask for registry key Software\Microsoft\Microsoft SQL Server\MSSQL.3\Cluster (status 0)).
Turns out there is no \Cluster key for MSSQL.3, because that's the instance for Reporting Services, which is not MSCS-aware.
So I just created a key manually and gave it the "ClusterName"=" "
After this, I updated MSSQL.4 and .5 as well, as these represented the Database Engine and OLAP instance.
These keys will automatically revert to the old virtual server name until you do the same procedure to the other node of the MSCS cluster.
Now you should be able to complete your update. I had to manually start my engine service in Windows Service Manager before I could start the cluster resource the first time from Cluster Administrator.
Connect to the instance now, and you should be able to add scheduled jobs, including Maintenance Plans.
I've spent a lot of hours on this, so I sincerely hope it helps someone.
-Tom Kretzmer
---Older Post---
I experienced exactly this error, and spent a few days chasing down a cure. I had already stood up a SharePoint installation previous to discovering the problem, so un-installing and re-installing SQL really wasn't an attractive option. It's fixed now, and unfortunately, I'm not really sure what fixed it. I'll list what I did, and hopefully the next person with this issue will be able to figure out an easier solution.
Initial environment when I noticed the problem was SQL 2005 SP2 on Windows 2003 SP2. Servers configured in an Active-Passive cluster using shared SAN over fibre channel. Cluster uses named instance. All critical updates applied as of 10/11/2007. I then applied the update mentioned at the start of the post, to no avail.
It seemed to be to be client related, so I used the SQL Server Surface Area Configuration tool to set the server to a static IP port (1433), using the cluster IP. Then, I used cliconfg to enumerate the instance. No good.
I'm still thinking it's a client issue, and the initial install only installed the client piece (SSMS) on one node of the cluster. I had planned on standing up another instance to make an Active-Active cluster anyway, so I went ahead and installed another instance.
Wouldn't you know it, but the new instance had the same problem again. Well, at least I can try the solution of reinstall SQL without killing MOSS.
So I uninstalled the new instance and reinstalled it a few times, and still had the same issue. I tried it from both nodes, and still didn't work.
Now here is where I'm not sure what fixed it. It could be that I didn't install SP2 on SQL properly on the second instance, which was causing a similar but different error. However, at the same time I found some stuff on Google which suggested something about registration settings. I looked at the registration settings, changed the Connect to Database field to Master and the Network Protocol to TCP/IP, and hit save. I only did that for one of the instance registrations.
Now, suddenly, I can modify maintenance plans and SQL jobs no problem, for both instances. What's strange is that I never touched the first instance, but both nodes can work with both instances without any problems. Also, it continues to work if I set the registration settings back to what they were initially.
I think this may be a bug (sorry, undocumented feature) related to using instances in a clustered environment without any default instance. I saw some posts with people describing some similar behaviors without default instances, which led me to look at SQL registration.
So here is my recommendation. First, force an update with the registration by opening up SSMS and hitting view --> Registered servers. Get to the properties of your instance, make some trivial changes, and hit save. Maybe the problem is it just needs to reset the registration settings. That would be the nicest solution. If that doesn't work, stand up another instance in your cluster (I had additional disks to make an entirely different disk group, but you probably can use the same group). If you're licensed per processor, this won't cost you anything. Apply SP2 and whatever else you need to bring it to the same version as the first instance. See if the problem occurs on both nodes for both instances. Again, if you've fixed it, great. If it's still doesn't work, try to uninstall the new instance and then re-install the instance. Worked for me. If any of this works for you, please add a post saying what is needed - help the next guy. As it is, this info cost me about 2 full days over the last couple weeks.
Good luck!
-Tom Kretzmer
(I posted this on my blog at http://geekswithblogs.net/HammerTips/archive/2007/10/23/116261.aspx)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply