SSMS GUI not working? Try to use T-SQL!

One of the good things, when we have new clients, is that sometimes they have needs that you never heard before.
This does not necessarily mean that they are complex. As a matter of fact, they can be really simple..now the question is..are they doable? 🙂

From my experience, this can happen mainly because one of two reasons, they have some very specific need, or because the way the application is built will make you work with features that you haven’t played yet.

SQL Server Agent Job Schedules – Scenario

The client approached me and asked “Hey, we have an account that is the owner of our jobs, but we would like to use a different account to change the schedule of the job, mainly the start time: is that possible?
As I was not sure about it, I jumped to the documentation.

First things first

I double checked that the login they were mentioning had any permissions on the msdb database. In this case, the login was already part of one of the SQL Server Agent Fixed Database Roles, namely the SQLAgentOperatorRole, which have the following permissions described here.

If we take a look at the 1st row of the grid we can see that a login can change a Job Schedule if they own it.

Fair enough, let’s try it

With this information, I was confident that it would work.
To test it, I have created a login, added it to SQLAgentOperatorRole fixed role on the msdb database and had to change the schedule owner.

NOTE: We are talking about schedule owner and not job owner, this are two different things.

To find who is the owner of the schedule we can run the following query:

SELECT name, SUSER_SNAME(owner_sid) AS ScheduleOwner
  FROM dbo.sysschedules

Then, we need to change the owner to the login we want to use. For this, we should use the sp_update_schedule stored procedure on msdb database using the following code:

EXEC msdb.dbo.sp_update_schedule 
	@name = 'ScheduleName',
	@owner_login_name = 'NewOwnerLoginName'

Now that the login we want to use to change the schedule is the owner of it, the client can connect to the instance using SSMS and this login and edit the schedule, right? Unfortunately no.

Bug on GUI, or missing detail on documentation?

I tested on SSMS and the GUI is disabled

I had SSMS v17.3 which is a little bit out of date, so I upgraded to v17.9.1 which is the current GA (General Availability) version but I got the same behaviour. I have also installed the most recent version which is v18.0 preview 7 (by the time of this post) but, then again the same behaviour.

I decided to open a bug item 37230619 on SQL Server UserVoice called “Edit Job Schedule not working when login is the schedule owner” that you can upvote here if you agree that this should be fixed.

Workaround

Get the schedule id from the list above and you can run the following command (with the login that is the owner of the schedule) in order to change the schedule properties, in this case, the start date, to run at 1am.

USE [msdb]
GO
EXEC msdb.dbo.sp_update_schedule @schedule_id=0000, 
		@active_start_time=10000
GO

I agree that the @active_start_time parameter value is not the most intuitive, but if you look closer it has the format of ‘hh:mm:ss’ but without the ‘:’ characters and it’s a number.
In this example, ’01:00:00′ is translated to 10000.

At least this way it works. The client was happy to have one way to do it.

Bottom line

When the GUI doesn’t work, try to script out the action or find what is running behind the hood and run the command manually. Maybe you get a surprise!

SQL Server Operations Studio and VSCode: The wrong default datetimeoffset format

This post is to answer the question: “You are used to seeing in the format of yyyy-MM-dd right?” that I have raised on my blog post Don’t cutoff yourself with dates in T-SQL – Did you know….

As you could see from that blog post, my screen shots were from VSCode and in this case using mssql extension, but this happens also on SQL Server Operations Studio.

“But why are my datetimeoffset values on VSCode being showed in that format?” (dd-MMM-yyyy)

The short answer is because it relies on your regional settings.

This means that whatever settings you have set for your date when you open the VSCode or SQL Operations Studio this will be used to show the output from your datetimeoffset columns.

I’m being specific when I mean datetimeoffset

The DATETIME and DATETIME2 types already display always in yyyy-MM-dd format like SQL Server Management Studio.
Once again, remember, I’m talking about default output not if you use a CAST, CONVERT or FORMAT function to manipulate the results.

How can we fix this?

When I was looking for this behaviour I did some research and found that a similar problem was raised but regarding DATETIME2. You can see it here – issue #570.
With this in mind, I decided to open an new issue (#1139) on the vscode-mssql extension repository on GitHub and point to the other one already solved.

If you identify yourself with it, please add your to the issue.

Now, we need to wait to see the evolution and, hopefully, a fix will be included on a upcoming release.

Summary

Always try to use an unambiguous date format like “yyyy-MM-dd”.

Remember, copying things that can have double meaning (incorrect format) can lead to unexpected results like I have shown on the other blog post.

And last but not least, if you think is a bug/missing feature please take the time to fill an issue. It will help you an others for sure!

Thanks for reading!

Offline Microsoft Documentation? Download it!

On my last article I shared how we can now Contribute to Microsoft Documentation. Today I bring another quick tip on Microsoft Documentation!

Download Microsoft Documentation

Did you know that we can download PDF files with Microsoft Documentation?

I did not know until my colleague called my attention to it few days ago.

Important note: This tip is not (yet?) available for all Microsoft’s product suite. You should confirm if this tip applies to the product you need.

“Which documentation?”

The one we can find at docs.microsoft.com.

Here is why this can be useful

Nowadays, some of us have access to the internet almost 100% of the time, this help us forget that this may fail. You probably have gone through this, losing the internet access right when you needed to check a document. You know, it can happen.

If it happens, you get stuck because you can’t access a small (or not) piece of text that you could have backed up before but you didn’t, right?

Were you using the online documentation to understand what a specific field that belongs to an System Dynamic Management View (DMV) means? Or, which parameter you need to use to execute a specific system stored procedure?

If you get the pdf, you can continue working offline. Going on a flight? Will you be in a place where you don’t have internet access at all?

I think you get the point.

“I will give it a try, show me how”

The link is located on the bottom left of the page.

DownloadLink

This download will not download just the current page. By using the “Download PDF” link you will get all the content that is present on the tree-view under the “filter” box on the left of the page.

treeview

Script to download all existing PDF files

From my search exists at least 98 pdf documents (~66mb) exist just for the Relational Databases topic. Download them all is not the kind of work I would like to do manually.

PowerShell for the rescue

I wrote a PowerShell script that make thing a little bit easier.

With this script, you can download all files for a specific topic. You can find and download the script Get-MSDocs from my GitHub repository, just change the variables and run it.

Let’s see an example

You search for ‘sys.dm_exec_sessions’ DMV and you find the corresponding page from Microsoft documentation -> sys.dm_exec_sessions

The image below shows where you find the topic (highlighted in yellow) that you need to setup on the $topic variable on the script.

mainTopic

By setting the variable $topic = "relational-databases" this script will download all PDF files for that main topic. I have accomplished that by understanding the sql-docs GitHub repository nomenclature.

Each folder in there is the name of one PDF file plus, the current folder ‘Relational-Database’ in this scenario.

Next, choose the destination by setting it on the $outputFolder variable.

As an example for the SQL docs, you have to choose a folder from the Docs root at GitHub repository.

If you find any difficulty working with it let me know by writing a comment to this blog post.

Let’s say you want to do the same but for Azure – you need to change the URLs too. The script is currently pointing to ‘SQL.sql-content’ and for Azure is ‘Azure.azure-documents’. The way I know this is by clicking on download PDF on one of the pages and read the URL from the PDF.

Wrap up:

I have shown how you can download a copy of the documentation manually but also how to get all existing files for a specific topic.

Also, I explained that this is not available for every Microsoft product. For example, PowerShell docs don’t have the link to download PDF file on the docs.microsoft.com site.

Maybe in the future this will become the standard for every Microsoft’s product documentation.

 

Thanks for reading

Contribute to Microsoft Documentation

Times have changed and Microsoft has changed the way we can contribute for documentation!

We already have access to the source code from some programs. One example is PowerShell, that has an GitHub repository where anyone can contribute!

Now anyone can contribute to the documentation too!

How and where?

If you haven’t seen before, now we have a pencil icon on the top right corner that makes possible to suggest a change.

feature_image

 

When clicking on that pencil we will be redirected, in this case, to the MicrosoftDocs – sql-docs repository on GitHub.

There, we need to fork the repository, make the changes and submit our suggestion by doing a pull request (PR). After that we just need to wait for some feedback from the Microsoft team that will review what we have submitted.

Start contributing

In the past, if you saw any errors on Microsoft documentation you could not help easily. But now we don’t have more excuses! If we want to contribute the process is much easier.

Have you overcome a not so common problem and have precious information to add to the documentation? Do you want to add another code example? Or have you “just” found a typo?

Just go ahead and submit a PR.