February 3, 2021 at 3:07 am
Thanks for the script. I had a quick play today and the it failed on the first job I tried it on 🙁
It doesn't handle single quotation marks well in the body of the text so I had to go through and work out which ones should be double in the output it creates for sp_add_jobstep.
Your script also does a join on sysoperators so returns NULL for jobs that don't have email notification set up. That means that the command to create the job becomes NULL too as it is then concatenating strings with NULL.
February 3, 2021 at 4:07 am
This was removed by the editor as SPAM
February 3, 2021 at 7:02 am
Good article and thank you for taking the time to write and publish it.
As a bit of a sidebar, though, I just go to "Source Control" and grab a copy of the deleted job. This is especially important if you have a backup system that auto-magically deletes backup files from disks after the "archive bit" has been reset (indicating that they have been backed up to tape) after a certain number of days. I've had my fair share of people that have intentionally (or otherwise) deleted weekly, monthly, quarterly, semi-annual, and annual jobs only to find out weeks or months later that they need the job back. By then, the on-disk backups are long gone and making a trip to the tape warehouse simply takes too bloody long.
I agree 100% with that, having a copy of the jobs would be much easier and safer. Can you share how does your jobs deployment look like?
Do you do it automatically or just copy/paste the jobs?
February 4, 2021 at 12:27 pm
Thanks for the script. I had a quick play today and the it failed on the first job I tried it on 🙁
It doesn't handle single quotation marks well in the body of the text so I had to go through and work out which ones should be double in the output it creates for sp_add_jobstep.
Good catch, I will have a look.
Your script also does a join on sysoperators so returns NULL for jobs that don't have email notification set up. That means that the command to create the job becomes NULL too as it is then concatenating strings with NULL.
That has been updated and waiting for the publication.
February 4, 2021 at 4:18 pm
Jeff Moden wrote:Good article and thank you for taking the time to write and publish it.
As a bit of a sidebar, though, I just go to "Source Control" and grab a copy of the deleted job. This is especially important if you have a backup system that auto-magically deletes backup files from disks after the "archive bit" has been reset (indicating that they have been backed up to tape) after a certain number of days. I've had my fair share of people that have intentionally (or otherwise) deleted weekly, monthly, quarterly, semi-annual, and annual jobs only to find out weeks or months later that they need the job back. By then, the on-disk backups are long gone and making a trip to the tape warehouse simply takes too bloody long.
I agree 100% with that, having a copy of the jobs would be much easier and safer. Can you share how does your jobs deployment look like?
Do you do it automatically or just copy/paste the jobs?
I generate the thoroughly tested/signed off jobs from the Object Explorer Detail window to another window. I save that to an SVN folder for the purpose of holding all the things that need to be deployed during an installation. That code can be executed using any number of methods including the oldest of them all... a batch file that calls SQLCmd and uses the jobs.sql file I created as a part of the deployment package as input. You could also use an over-arching installation script that you'd open in SSMS and press the {f5} button on or, like I said, any other of a dozen other methods. Choose your flavor of the day. 😀
Of course, each job is also saved in SVN individually with versioning and any special purposes noted.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply