tag:blogger.com,1999:blog-2613706866370202422.post7302373164119521542..comments2023-10-08T00:41:54.628+13:00Comments on SQL Concepts: SSDT SQL Agent Job Deployment TemplateAnonymoushttp://www.blogger.com/profile/14226489036653742776noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-2613706866370202422.post-31433489504063190692016-09-30T06:48:13.827+13:002016-09-30T06:48:13.827+13:00This comment has been removed by the author.Kennethhttps://www.blogger.com/profile/13090737858021333148noreply@blogger.comtag:blogger.com,1999:blog-2613706866370202422.post-67330114242767526002016-03-15T19:14:46.214+13:002016-03-15T19:14:46.214+13:00I don't understand what the Job Categories fil...I don't understand what the Job Categories file is for or what to put in it. Is it needed?Anonymoushttps://www.blogger.com/profile/13357811417499994505noreply@blogger.comtag:blogger.com,1999:blog-2613706866370202422.post-69631435555342453802016-02-15T22:57:43.789+13:002016-02-15T22:57:43.789+13:00To further clarify; I create separate files for ea...To further clarify; I create separate files for each post deploy. I then reference each of these with the :r SQLCMD option. Only the "main" post deployment file needs to be set as a post deploy - the others can just be scripts/txt files!Anonymoushttps://www.blogger.com/profile/14226489036653742776noreply@blogger.comtag:blogger.com,1999:blog-2613706866370202422.post-16093773852085582262016-02-15T22:56:13.344+13:002016-02-15T22:56:13.344+13:00I always end my post deploys with a GO. That will...I always end my post deploys with a GO. That will solve that particular problem. <br /><br />I did this in the early days of SSDT because the merging of the files used to get messed up without them. I havent seen that behaviour for a while but I have formed the habit now.Anonymoushttps://www.blogger.com/profile/14226489036653742776noreply@blogger.comtag:blogger.com,1999:blog-2613706866370202422.post-90105690397978166382016-02-10T18:29:09.334+13:002016-02-10T18:29:09.334+13:00Great solution, only issue I ran into is if you tr...Great solution, only issue I ran into is if you try to create more than one of these template scripts and add them into post deployment it fails. Since post deployment is trying to include all files into one large master file the declaration of @JobId is throwing errors.Anonymoushttps://www.blogger.com/profile/11964043607149596733noreply@blogger.comtag:blogger.com,1999:blog-2613706866370202422.post-87316108835482649352013-06-15T12:23:24.481+12:002013-06-15T12:23:24.481+12:00This has certainly solved a crucial problem in our...This has certainly solved a crucial problem in our day to day DB development! Your template is now part of our must-do process, so thankyou.<br /><br />Only one alteration I'd suggest and thought I'd put here for others - although this checks for the presence of the job, it always assumes the Schedule doesn't exist. Found this results in multiple Job Schedules being created on the server rather than deleting and recreating. Below is what we've done to replace ln 66 to 81:<br /><br />/* Check if Schedule exists, otherwise create. */<br />IF EXISTS (SELECT 1 FROM msdb.dbo.sysschedules WHERE name = N'My Schedule') <br />BEGIN<br /> PRINT N'Job Schedule already exists.';<br />END<br />ELSE <br />BEGIN<br /> EXEC msdb.dbo.sp_add_schedule @schedule_name=N'My Schedule', <br /> @enabled=1, <br /> @freq_type=4, <br /> @freq_interval=1, <br /> @freq_subday_type=2, <br /> @freq_subday_interval=60, <br /> @freq_relative_interval=0, <br /> @freq_recurrence_factor=0, <br /> @active_start_date=20120319, <br /> @active_end_date=99991231, <br /> @active_start_time=0, <br /> @active_end_time=235959<br /><br /> PRINT N'Job Schedule created successfully.';<br />END<br /><br />/* Add the job to the schedule */<br />EXEC msdb.dbo.sp_attach_schedule @job_id=@jobId, @schedule_name=N'My Schedule'<br /><br />Hope it helps!Hanznoreply@blogger.comtag:blogger.com,1999:blog-2613706866370202422.post-27272463573256194932013-03-12T07:07:20.008+13:002013-03-12T07:07:20.008+13:00Thanks for sharing your knowledge. Very nice and n...Thanks for sharing your knowledge. Very nice and neat blog! I like the way to use the cmd variables in scripts.<br />Linda.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2613706866370202422.post-5113480856014540722013-02-27T13:00:13.641+13:002013-02-27T13:00:13.641+13:00Hi Joey,
Glad you find it useful.
Regards
KentHi Joey,<br /><br />Glad you find it useful.<br /><br />Regards<br />KentAnonymoushttps://www.blogger.com/profile/14226489036653742776noreply@blogger.comtag:blogger.com,1999:blog-2613706866370202422.post-59265037006471283252013-02-27T12:20:23.214+13:002013-02-27T12:20:23.214+13:00Thank you for this, I like the pattern as it is si...Thank you for this, I like the pattern as it is simple and neat, I will be attempting to integrate this into my own projectsAnonymoushttps://www.blogger.com/profile/18378386217936230399noreply@blogger.com