?
Solved

T sql script to alter SQL Server agent job if exists

Posted on 2014-01-12
6
Medium Priority
?
2,444 Views
Last Modified: 2014-01-27
Hi Experts,

  Could you please help me with T-Sql script to alter SQL Server agent job.  If the job exists, then I need to alter the job script ,to  add more steps and also edit the existing steps.  
I don't want to add it manually through wizard as we want to run the T-Sql scripts only (as we don't have access to production environments)

I need a sample Alter script . Your help is greatly appreciated in this regard.

Please Note : I don't want to drop and create the existing job in production as I would loose history of the job.

Regards,

Sreekanth.
0
Comment
Question by:n_srikanth4
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39775430
I would go through the steps with the wizard, just don't finish and instead save the SQL Scripts off so that you can optionally edit them and have them ultimately run in Production.

Let me know if you need help with that.
0
 

Author Comment

by:n_srikanth4
ID: 39775464
Hi Savant ,

     I could not go through the wizard , It will just add the step , but not generate any script .  Secondly , the  jobs are already created in Dev server , so i cannot undo the wizard.  So could you please walk through an example sample script please.

Regards,

Srk.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39775503
i would drop the step in development and start again using the wizard, but this time save the SQL scripts, before you finish.

P.S.  The name is Anthony (you will see it as it is just below where it says "Your Comment" and after "by:")
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 38

Accepted Solution

by:
Jim P. earned 1500 total points
ID: 39775517
My suggestion is that you create your own "history" table that you write the results to as the steps run. Basically as the first part of that particular step you insert the step name, time, server, db and other details at the beginning into the history table. Then at the end of the step you have a similar insert statement.

That way you aren't dependent on the job history table.

Then adding steps or replacing the script you don't lose the history. And if you build out the script for more stuff, you can just hand it off to the production team and move on.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39781034
Hi,

If you examine the script of the existing job, that script should have all the clues you need. That is, the job steps are added to the job one at a time. Of course you'll have to add logic that finds how many existing steps there are, and appends them. And don't forget to modify existing last step to go on to the next on success.

HTH
  David
0
 

Author Closing Comment

by:n_srikanth4
ID: 39813554
good
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question