Bug found in SSMS 18.8

Question

Hi,

I believe I found a little bug in SSMS 18.8 - want to confirm with others and if someone can report that would be great.

I have several servers (Development, Stage, Production). I have access to Development and Stage. I (or my colleagues) created jobs in Development, I scripted them and moved to Stage (scripted using Object Explorer Details).

So far so good. Now I wanted to add one more job category - I added it on Development server and put one job into that category. I want this to be scripted - new job category. So I tried the following steps:

Using that job I moved to my category I put it back into Uncategorized (local) and pressed Script button. Now I tried to put it back into my new category and again pressed the script button - however this time I'm getting the error that job doesn't exist. I tried a few times with the same outcome every time.

Also I was trying to find a script to script all my newly created custom categories (if they don't already exist) and quick googling didn't find such a script. Can someone provide, please, if you have it handy?

Thanks in advance.

Answer 1

I could reproduce the situation with both SSMS 18.7 and 18.8. After I clicked the Script tab to generate the script on the Job Properties window, I could not do anything on the same window except pressing the Cancel button. Otherwise I would get the error message "The Job '_TestJob' does not exist on the server. (Microsoft.SqlServer.Smo)" even if I pressed the OK button. I am not sure if this is on purpose, but I could not find any reason to prevent the users from doing that.

I did not find the way to script the newly created custom categories since there is no column to store the date and time in the system table [msdb].[dbo].[syscategories], but the following script can list the counts of the jobs under the categories:

USE [msdb];
GO

SELECT c.[category_id], c.[name], ISNULL(j.[JobCount], 0) AS job_count
  FROM [dbo].[syscategories] AS c
LEFT JOIN (
    SELECT [category_id], COUNT(*) AS JobCount
      FROM [dbo].[sysjobs] 
    GROUP BY [category_id]
) AS j ON j.[category_id] = c.[category_id]
WHERE c.[category_class] = 1    -- Job
AND c.[category_type] = 1       -- Local
GO

Answer 2

The stored procedure sp_add_category in the msdb database can be used to create the categories. If you run that code for an existing category - it will fail and will not create the category.

I would be careful with trying to script this - there are default categories and you would not want to do anything with those. I would setup a script with custom categories - and when they were added to the script, and applied to each system. Keep that script available and when you need a new category - update the script with the new category so you can apply it to all systems where it is needed.

You would only need to run the add for that new category unless you are setting up a new system, then you would run for all necessary categories.

Answer 3

Hi,

I tested it with SSMS 18.7 and 18.8, but could not reproduce the problem.

In addition to environmental differences , It may be that I did not fully understand your operations, which caused my recurrence to be inconsistent.

In any case( Especially if someone can reproduce the issue), If you have comments or suggestions, or you want to report issues, the best way to contact the SSMS team is at SQL Server user feedback.