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!

Triggers: The hidden logic that will strike back – TSQL Tuesday #106

https://voiceofthedba.com/2018/09/03/t-sql-tuesday-106-trigger-headaches-or-happiness/

This month’s T-SQL Tuesday is brought by Steve Jones (b | t) and he wants to know if triggers causes headaches or happiness to us.

This is the 106nd edition of TSQL2sDay – an Adam Machanic (b | t) brainchild.

 
Triggers are those kind of database objects that sometimes are the best bet but most of the times they will strike back.

“Why?” – You may ask.

From my experience triggers are objects that we “tend” to forget are there, and only when we hit a problem and, sometimes, after digging into the problem we end by saying something like

wait..but why is the value different from what I have used?! Ohh…maybe the table has some triggers.

Yes, it has already been uttered by me before. For me, triggers are a hole of possible business logic written and forgotten…forever.
Let me just clarify the meaning of “forever” here – until you hit a problem or you need to rewrite some logic and the result is not what you expect. Again, because the code is ‘hidden’.

Recent pain – A project to migrate Firebird SQL to T-SQL

When Steve mention the “I think I’ve ended up using triggers in 0.01% of my tables or less.” I remember this project and after the math I can say they used ~78,84% and ~63,32% (were two databases) of triggers on their tables.

When looking at their code it became obvious the type of use they are making, mainly to fill 1-2 columns with current datetime and/or to generate a new ID (because they didn’t use identity columns – supported on v3.0 or higher)

“You never used triggers, it’s what you are saying?”

No, it’s not. I have used them before and not only 0,01% but maybe less than 5%.

Let me share an story

Back in the earlier times when I was a full-time T-SQL developer (I think was on 2008/2009), I worked with an application that use triggers to insert data on some tombstone tables that help to go back and get the data at that time.
Nowadays, we can achieve this with much less code using temporal tables.

One day I hit an issue where the record on the main table wasn’t being inserted, I run the code, a stored procedure, with some parameters and I got back a new ID (we had identity columns) all good. When I ran with other parameters (the ones not working) I didn’t see the new ID, I went back and forward and seeing the gaps generated by the problematic parameter set, throw some debug messages into the stored procedure, also throwing more information on the catch block to understand the problem.

Then, after reading the message carefully I saw that the problem was on the insert on my tombstone table and not on my “visible” logic. Spent about one hour to get it…

Waste of time

Or not, from that day on, one of my first questions when I hear people have some problems like this is to ask “are there any triggers on the tables involved?”

With that I already saved some hours of work.

Wrap up

As you may understand I decided to not use triggers whenever possible, but, sometimes we pick the train already in movement and we may need them.
If you fit on this last scenario, please, please document it very well!

I’m eager to read other’s stories because I bet they also have some good points and on this interesting topic.

Thanks for reading.

I will be speaking @ SQLSaturday #795 Porto

On 29th September we will have SQL Saturday Porto and I’m proud to announce that I will be speaking!

I will be speaking about dbachecks PowerShell module – dbachecks – Checking SQL Server environments with PowerShell.

The event will be held at ISEP, Rua Dr. António Bernardino de Almeida, 431, 4249-015 Porto, Portugal and you can see the full schedule here.

Register, it’s free!

Hope to see you in Porto! If you see me, come and say hi!

Don’t cutoff yourself with dates in T-SQL – Did you know…

Almost all the applications we use have a lot of options. And, sometimes we even stumble across them by accident.
Who’s ever heard something like “Normal user don’t even use 5% of Excel capabilities!”?

Other options, we know they exist but because the default value it’s ok (so far) we tend to forget it. It is just sitting there, waiting for the day we want/need to change it.

The oddity

Few days ago I was talking with a friend that show me some “odd behavior” when working with dates on SQL Server.

Are you mad?! Is this SQL Server bugged?

In case you ask, this is my table definition:

CREATE TABLE DatesFormat
(
    dt DATETIMEOFFSET DEFAULT ('1900-01-01')
);
GO

Let’s take a closer look at the SELECT.

  1. On the 1st column, we can see the complete value of the column
  2. The 2nd column picks the year from the date column
  3. The 3rd one also picks the year from a datetime but declared as string.

But why don’t the 2nd and 3rd columns return the exact same value?!

What is your interpretation?

What do you read when you see some date in a format like “01-Jan-00 00:00:00.000”? Keep in mind that I’m talking about the output directly from the table and without any formatting.
1st of January seems to leave no doubt (just because there is no default date format starting with two digits for the year), but…what about the year part ’00’?
It stands for 1900 and the 3rd column is wrong?
Or it stands for 2000 and the DATEPART function is returning the wrong value?

Both are returning the correct value! Say hello to “Two Digit Year Cutoff” configuration

You can find it on the advanced tab in the Server Proprieties:

Or by running the sp_configure command:

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'two digit year cutoff';  
GO 

Or even using dbatools PowerShell module:

Get-DbaSpConfigure -SqlInstance sql2016 -ConfigName 'TwoDigitYearCutoff'

Output:

That’s right! This option is the one that makes the last column value being translated to 2000 and not 1900.

If we change the configuration to 1999 instead of 2049 (default value) the result of the DATEPART will be 1900 but having it as 2049 will convert the year as 2000 (from the date that is a string).

The test:

-- Check the running value
EXEC sp_configure 'two digit year cutoff';
GO

-- Notice the 3rd column
SELECT dt, DATEPART(YEAR, dt), DATEPART(YEAR, '01-Jan-00 12:00:00 AM +00:00') 
  FROM dbo.DatesFormat
GO

-- Change the configuration
EXEC sp_configure 'two digit year cutoff', 1999;  
GO  
RECONFIGURE ;  
GO 

-- Confirm the running value
EXEC sp_configure 'two digit year cutoff';
GO

-- See the different value on the 3rd column
SELECT dt, DATEPART(YEAR, dt), DATEPART(YEAR, '01-Jan-00 12:00:00 AM +00:00') 
  FROM dbo.DatesFormat

Output:

Remember, this only happens when you use a literal string.

To set a new value using dbatools:

Set-DbaSpConfigure -SqlInstance sql2016 -ConfigName 'TwoDigitYearCutoff' -Value 1999

Output:

What about the returning value?

Yeah, I know, why is the value of the first column returned on that format? You are used to seeing in the format of yyyy-MM-dd right?
I’ll explain this in a next post! Stay tuned.

Summary

Next time you have to work with dates in formats like dd-MMM-yy remember that “Two Digit Year Cutoff” configuration exists and may mislead you.

To complete the question…”Did you know that ‘Two Digit Year Cutoff’ configuration can trick you?” Now you do.

Thanks for reading.

I will be speaking @ Tuga IT 2018

Starting on 19th of July and during 3 days the Tuga IT 2018 Summer Edition will be happening in Lisbon!

This edition will have:

4 full-day workshops
38 breakout sessions (1 hour)
5 deep dive sessions (2 hours)
That is over 72 hours of content delivered by 44 awesome speakers, many of them recognized industry experts.

You can see the full schedule here. This edition will have sessions about: Cloud, Data Platform, Development, Integration, IOT, Office, PowerShell and Security.

I will be speaking on 19th about the dbachecks PowerShell module for SQL Server professionals. You can find more information about the session here: dbachecks – Checking SQL Server Environments With PowerShell.

The event will be held at Microsoft Headquarters in Lisbon.

What are you waiting for? Registration to the main event is free for all attendees, with the exception of the full-day workshops.
Main event registrations
Workshops registrations.

Hope to see you in Lisbon!