 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008Error 14247 can't add ,update or delete job
I'm not very good at SQL. I have three jobs that were created on the old SQL server and they are pointing to the old server. In fact they're jobs for a database that doesn't even exist any longer, but they're throwing a lot of SYSLOG errors. How do I delete these jobs? I keep getting this message, "Error 14247 can't add ,update or delete job that generated from MSX server". I saw something that I could delete it in msdb..sysjobs, but that's Greek to me. Can someone tell me (step by step) how to delete these jobs? Thanks. Andy Siegel Dover, DE > I'm not very good at SQL. I have three jobs that were created on the old > SQL [quoted text clipped - 10 lines] > Andy Siegel > Dover, DE Hi Andy You don't say how/where you get the error message, or which version of SQL Server you are using? John Please specify version of SQL Server. I will assume 2000 (because of behavior). If so: You either get this error because rename of the machine or because you once were a target server and got the jobs from your master server. I recommend that you update the sysjobs table (step 3 in article). If you renamed the machine there are some other steps as well. See http://www.karaszi.com/SQLServer/info_change_server_name.asp.  SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > I'm not very good at SQL. I have three jobs that were created on the old SQL > server and they are pointing to the old server. In fact they're jobs for a [quoted text clipped - 6 lines] > Andy Siegel > Dover, DE Andy, The issue is you restored MSDB from a different server, and the jobs think that the old server is the MASTER (MSX) job server. Open query analyzer and run the following SQL Statement. You will then be able to delete the jobs from enterprise manager. --Start here UPDATE msdb.dbo.sysjobs SET originating_server = (SELECT @@servername) where originating_server <> (SELECT @@servername) Go Hope that helps! David Hay Can I just cut and paste, or do I need to change something in there. Like, does servername need to be the name of the server (old or new) and what about originating_server? Just as it is? Thanks for taking the time to help. > Andy, > [quoted text clipped - 15 lines] > > David Hay Andy, SQL Server 2005 Books Online @@SERVERNAME Returns the name of the local server running Microsoft® SQL ServerT. SQL Server 2000 BOL say basically the same thing. RLF > Can I just cut and paste, or do I need to change something in there. > Like, [quoted text clipped - 23 lines] >> >> David Hay Andy, I thought I posted this but it didn't come through. I am assuming that you are running SQL 2000. The issue is you restored MSDB from another server, so the jobs think they are controlled by the "old" server, which probably doesn't exist any more. Open Query anaylzer and run the following UPDATE msdb.dbo.sysjobs SET originating_server = (SELECT @@servername) where originating_server <> (SELECT @@servername) You should now be able to go into SQL enterprise manager and delete the jobs. Hope that helps! David Hay Well, I essentially cut and pasted that into QA against the master DB and the MSDB and I got no errors, but it also said no lines affected. When I go back and look at the jobs, it still shows "Source: Dover-2" which is my old server. I tried doing this with the SQL Agent started and with it stopped - not sure if it makes a difference. I must be missing something. > Andy, > [quoted text clipped - 16 lines] > > David Hay Andy, Do you get the answer 'Dover-2' when you run SELECT @@SERVERNAME? If so, run the following: select * from master.dbo. sysservers Does srvid 0 have the name 'Dover-2'? If so, then your master database believes that is the name of your server. It is quite possible to have your internal server name be different from the actual server name. This causes confusion in many places. I remember vaguely from my last encounter that in some contexts the actual servername is important and in others the servername stored in sysservers is important. Even if that is a fever dream, you want them to match. Therefore, do the following: exec sp_dropserver 'Dover-2' exec sp_addserver 'ProperServerName', local The rerun the cut and pasted step to update your jobs. RLF > Well, I essentially cut and pasted that into QA against the master DB and > the [quoted text clipped - 25 lines] >> >> David Hay When I run SELECT @@servername against Master and MSDB, it returns Dover-2. I tried running SELECT * but it returns an error "Must specify table to select from." before I run the exec sp_... , do I need to stop the SQL Agent, or not. Also, I just want to make sure the "UPDATE" script is right. Should "where" be on a separate line? I'm putting it on one and just wanted to make sure it's right. Does it matter? As I said, I can spell SQL but that's it. > Andy, > [quoted text clipped - 48 lines] > >> > >> David Hay Andy, It looks like you did not copy the whole select statement: select * from master.dbo.sysservers But it does not matter. Since your server is calling itself 'Dover-2' you need to change it to the proper name. You do not need to stop SQL Agent to reset the server name using the "exec sp_..." code. And, T-SQL statements may be all on one line or every token on a different line. It does not matter. RLF > When I run SELECT @@servername against Master and MSDB, it returns > Dover-2. [quoted text clipped - 64 lines] >> >> >> >> David Hay Oops. Sorry. It returns the following for svrid 0: svrstatus is 1089 (1,2, & 3 is 1184); svrname is ProperServerName. I hope this makes sense to you. > Andy, > [quoted text clipped - 80 lines] > >> >> > >> >> David Hay Andy, 1. Did you run the following script? exec sp_dropserver 'Dover-2' exec sp_addserver 'ProperServerName', local If not, please do so. 2. Did you run the following script from David Hay? UPDATE msdb.dbo.sysjobs SET originating_server = (SELECT @@servername) where originating_server <> (SELECT @@servername) If not, please do so. Please run the following script in text mode and past the results in the reply to this message: SELECT @@ServerName AS [@@ServerName] SELECT COUNT(*) job_count, originating_server FROM msdb.dbo.sysjobs GROUP BY originating_server ORDER BY originating_server If everything is what we think it sounds like, you should get an answer like this: @@ServerName ------------------------ ProperServerName (1 row(s) affected) job_count originating_server ----------- ------------------------------ 3 ProperServerName (1 row(s) affected) If the @@ServerName and the originating_server are identical, then the problem lies elsewhere. Show us what you get. RLF > Oops. Sorry. It returns the following for svrid 0: svrstatus is 1089 > (1,2, [quoted text clipped - 98 lines] >> >> >> >> >> >> David Hay First of all - Thank you for taking the time to help me. I really appreciate your assistance. I did everything there. On the last one, I got @@ServerName is DOVER-2. job_count = 11 and originating_server is DOVER-2 > Andy, > [quoted text clipped - 144 lines] > >> >> >> > >> >> >> David Hay Hi Have you renamed the server or restored an image/clone? If so exec sp_dropserver 'Dover-2' exec sp_addserver 'New Server Name', local John > Well, I essentially cut and pasted that into QA against the master DB and > the [quoted text clipped - 25 lines] >> >> David Hay When I stood up this server about 18 months ago, I built it with the name DOVER2. The old one was DOVER-2. Again, being the SQL dummy, It was recommended that the best way to bring in a database was to detatch it from the old server, copy to new server, and attach it. I did that and it seemed that everything was working. I no longer needed these databases for anything, so I detatched and deleted them. I didn't know about the jobs until later. I have the databases on backup tapes and could restore them, but it seems to me that it wouldn't matter because the jobs are pointing to the wrong location, anyway. In the script you say to run, do I substitute the 'New Server Name' with the new server's name (DOVER2), or do I run it as typed below? > Hi > [quoted text clipped - 34 lines] > >> > >> David Hay > When I stood up this server about 18 months ago, I built it with the name > DOVER2. The old one was DOVER-2. Again, being the SQL dummy, It was [quoted text clipped - 12 lines] > the > new server's name (DOVER2), or do I run it as typed below? Hi I assume you have moved other system databases not just msdb? You would need to run: exec sp_dropserver 'Dover-2' exec sp_addserver 'DOVER2', local John Thank you. I really don't recall what databases were moved and which ones were restored from tape. At any rate, when I run the script you referenced, I get a message that says Dover-2 doesn't exist and that there is already a server 'local' Does my inability to delete these jobs have anything to do with the fact that the database they were set to run against no longer exists? > > When I stood up this server about 18 months ago, I built it with the name > > DOVER2. The old one was DOVER-2. Again, being the SQL dummy, It was [quoted text clipped - 23 lines] > > John |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.