| Author |
Topic |
|
janetb
Yak Posting Veteran
71 Posts |
 Posted - 2008-02-28 : 10:51:00
|
| Had to replace leased server hardware running Server2003, Sql2005. Had machine Server B, set up sql2005, set up maintenance plan to do full backup on all user dbs every night to Server C network share. Worked great for over a week. Came time to do the swamp, brought down old server, renamed Server B to Server A, ran:EXEC sp_dropserver '<old_name>' GO EXEC sp_addserver '<new_name>', 'local' GO All other functions to the database seem to be working fine. Was using a network administrator account - same one that worked before the name change so the SID is the same. Unfortunately, I decided to try and delete and start over in case it was just a gliche. Now, the history seems to be gone, but I can't actually delete the job or the maintenance plan. Get an error "Does not allow remote connections." even when I'm physically sitting at the machine.Now, maintenance plans won't run - can't delete them, can't modify them. Other dts packages that execute a query or bring in data from other datasources work just fine.Help!? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-28 : 10:59:57
|
| you will have problems with jobs while renaming Server name:You have update MSDB database.USE MSDBUPDATE sysjobsSET originating_server = 'New_Server_Name' |
 |
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2008-02-28 : 14:38:28
|
| SoDeep,Thanks for the reply, but I didn't do that one because I'm running sql 2005; I understood that was only for 7 & 2000? Tried it anyway and got:Invalid column name 'originating_server'. |
|
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2008-02-28 : 14:42:11
|
| SoDeep,There's an orginating_server_id, but they are all 0 - the ones that are working and the ones that aren't.Janet |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-28 : 14:48:04
|
| You should have your new server name in originating_server column. It works for SQL 2005 too.Please update. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-28 : 14:58:31
|
| Sorry my bad, updating system tables are not supported in 2005. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-01 : 22:18:01
|
| Use sp_help_jobserver to find out job server name. |
|
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2008-03-03 : 09:32:32
|
| While others are listed under sp_help_jobserver, there is no entry for the specific one I'm dealing with here. Any ideas? |
|
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2008-03-03 : 16:15:15
|
| Have a litte more information to contribute. I successfully recreated the two maintenance plans (except one minor problem) and they work fine. I'll deal with the new ones' small problem later, separately.But, since I've got new plans for system and user databases, I'd like to delete the originals, but can't. For the systemMtnc, when I try to delete, I get this error when I'm physically sitting at the machine: "Exception has been thrown by the target of an invocation(mscorlib). An error has occurred while establishing a connection to the server. When connecting to sql server 2005, this failure may be cause by the fact that under the default settings, sql server does not allow remote connecitons. (provider: tcp provider, error: 0 no such host is know) error 11001I've disabled both jobs, but would prefer to delete the plan/jobs. But, it won't let me. Any ideas? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-03 : 22:25:40
|
| What do you get from 'select @@servername'? |
|
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2008-03-04 : 09:07:15
|
| I get the correct (now) servername. What I mean is, I get the server name as it is at this time, after the rename, e.g. was serverB and is now serverA, which is correct. Janet |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-04 : 22:44:05
|
| Tried with sp_delete_maintenance_plan? |
|
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2008-03-05 : 10:48:29
|
| Found this and it worked like a charm - hope it helps someone else.1. Select the ID with the select statementselect * from sysmaintplan_plans2. Replace with the selected ID and run the delete statementsdelete from sysmaintplan_log where plan_id = ''delete from sysmaintplan_subplans where plan_id = ''delete from sysmaintplan_plans where id = ''3. Delete the SQL Server Jobs with the Management Studio |
|
|
|
xyxoxy
Starting Member
4 Posts |
Posted - 2008-05-05 : 17:18:13
|
| I just ran into the exact same problem after a server rename and your solution worked like a charm!You saved me a ton of time. Thanks for posting it !! |
|
|
|
kgerde
Starting Member
1 Post |
Posted - 2008-05-20 : 16:46:44
|
Might I ask what did you do to fix the fact that sp_help_jobserver was returning nothing?quote:
Originally posted by janetb
While others are listed under sp_help_jobserver, there is no entry for the specific one I'm dealing with here. Any ideas?
|
|
|
|
dba0000
Starting Member
2 Posts |
Posted - 2008-05-20 : 22:25:33
|
| I had the same problem and the post by Janetb (on 03/05/2008) appeared to solve the problem but after creating a new Maintenance Plan (using the Wizard) with the same name (not sure that this mattered) all the information that I thought I deleted (such as the connection still pointing to the old server) was in the new Maintenance Plan.However, this time I was able to delete the Maintenance Plan via the GUI and the next time I created a Maintenance Plan (with the same name) everything looked correct. |
|
|
|
dba0000
Starting Member
2 Posts |
Posted - 2008-05-20 : 22:35:38
|
| I'm wondering if there are any other "gotchas" that are going to come back to bite me in the butt. Does anyone have a list of things to change for SQL 2005 when renaming the server? I found a couple of places (registry/Config Mgr) where the IP address (and/or the server name) was still referencing the old server.Has anyone experienced any other aspects of SQL Server not working properly because of a rename. Possibly the reporting server or SSIS packages?To be honest, I'm not exactly renaming the servers...I'm actually creating VMWare templates and building VMs on blade servers using these templates. TIA |
|
|
|
Spoower
Starting Member
2 Posts |
Posted - 2008-05-27 : 07:30:00
|
| Hi,Is there anyone who has a solution?I'm stuck with the same problem but I don't want to delete the Maintenance Plans!The best would be if there is a way to edit the connection in the Maintenance Plan since it points to the old server. |
|
|
|
mdubey
Posting Yak Master
133 Posts |
Posted - 2008-05-27 : 09:18:31
|
| Please try to stop the service for the agent and delte the MP's. but agin this is last option. before that you can try to delete from Query if not then try to do it from GUIManojMCP, MCTS |
|
|
|
mdubey
Posting Yak Master
133 Posts |
Posted - 2008-05-27 : 09:21:27
|
| Basically if you rename the server name, it is must that you should have to restart the SQL services. It may takes effected once you restart services for SQL server (Agent and Engine).ManojMCP, MCTS |
|
|
|
Spoower
Starting Member
2 Posts |
Posted - 2008-05-28 : 10:34:40
|
| The problem is that I really don't want to delete these large Maintenance Plans. It would be so much easier if it is possible to change the connection inside each MP. |
|
|
|
Next Page
|
Maintenance plan won't run, modify or delete