question

NaomiNNN avatar image
1 Vote"
NaomiNNN asked NaomiNNN edited

Bug found in SSMS 18.8

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.

sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

There is another issue with SSMS. I'm accessing SQL Server through VPN connection. If I get disconnected from VPN I'm getting error when trying to execute a query. However, clicking on the Cancel button to try to cancel execution produces an error 'Object Reference not set to an instance'... [Something like that]

0 Votes 0 ·

I was about to write another post but thought I already mentioned it before. Every time I got disconnected from the server when I try to execute the query from the already opened query and then try to cancel it I get: 'Object reference not set to an instance of an object'. It is extremely annoying, because the only way to actually cancel is to close that window, then I would have to re-open it to execute.

0 Votes 0 ·
Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
1 Vote"
GuoxiongYuan-7218 answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JeffreyWilliams-3310 avatar image
1 Vote"
JeffreyWilliams-3310 answered NaomiNNN edited

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks, Jeff. I don't remember how exactly did I resolve the problem. I think I scripted the categories somehow. The reason I posted was to complain one more time about this SSMS bug I am beaten up constantly. Say, I have SSMS opened with some queries. In the morning I go and press Execute button not noticing that I got disconnected from VPN and the server. The query says "executing...", I try to stop it and I get the "Object reference..." error. It is super annoying that I cannot properly cancel the query execution. If I press close (x) at the top, then the query cancels and often that tab still remains opened (which is good),

0 Votes 0 ·

I also work over VPN and don't have that issue - if I get disconnected from the VPN and reconnect, then try to execute a query I may get a disconnected message the first time but then execute again and it works.

I suspect the problem is that you are trying to execute the query while disconnected from the VPN. The fix for that is to make sure your VPN is connected prior to running any queries.

0 Votes 0 ·
NaomiNNN avatar image NaomiNNN JeffreyWilliams-3310 ·

Jeff,

I understand I have to reconnect prior to running the query. My point is - if I didn't notice that I got disconnected and did press the 'Execute' button, then I have no way to Cancel, reconnect and re-try. This is when I get this error - attempting to Cancel when I am disconnected.

0 Votes 0 ·