There was a question on Stack Overflow recently about SQL Server being "stuck" in single user mode.
The poster had a SQL Server 2016 instance, running on Windows Server 2016. He said he tried to do this:
But it always told him that the database is in use.
The core concept to understand here is that when a database is in single user mode, you can't assume that you're the single user. In this case, he clearly wasn't.
I've seen this happen a lot when people are using SQL Server Management Studio (SSMS) and Object Explorer is connected to the database. It could very well be the single user, not you.
Best advice is to try to find anything that's connected to the database, and close it. Then try again.
If there's no luck, restart the SQL Server service, and connect with a tool like SQLCMD that isn't going to go opening any other connections. Then you should be able to execute the command to put the database back in multi-user mode.
Sometimes we have a date and we need to extract components of the date. For example, we might have a date and wonder what's the year or we have a date and wonder what the month is or the day.
While there are several ways to extract the year, the month, and/or the day from a date in SQL Server, there are functions designed to do precisely that.
The YEAR function takes a date and extracts the year. The MONTH function takes a date and extracts a month (as a month number from 1 to 12). And the DAY function takes a date and returns the day (again as a day number like 28, not the name of the day like Tuesday).
Most developers and analysts today are fairly aware of the impacts of technical debt. As technical debt grows, it takes longer and longer to get real customer or end-user work done. Worse, more and more time is spent triaging and squashing bugs. And one interesting aspect of technical debt is old tooling.
I've written before about modern not being a synonym for better, but there comes a point where you need to modernise your tooling, even if it seems to be doing the job.
For me, the most tell-tale sign is when the author of the tools has apparently moved on from being interested in the tool. No matter how good a tool was, it needs to keep moving forward as requirements and operating environments evolve.
I deal a lot with Microsoft applications, and I often see these signs:
People from the development team seem to have moved on to new jobs
No-one is posting blog posts about what's happening
The tool stops turning up on conference sessions, Channel 9 videos, Azure Fridays, etc.
The flow of new features slows to a trickle or stops
If you ask the company, the tool is still supported and not going anywhere, but given the interest has moved on, you probably should too. I can only imagine that there's a desire to avoid upsetting anyone who's made a commitment to using the tool.
Many people refer to this type of tool as abandonware.
There are times though, when sudden spurts of life appear. A classic example is the work that was done on SQL Server Reporting Services in the 2016 edition. (I'll write more about SSRS another day)
What I really wanted to mention today though is another nearly-hidden downside of using old tools, as a form of technical debt. And that's your ability to find staff. You do not want to underestimate the impact of this.
Today, I was reading about a large company in Europe with untold millions of lines of classic ASP code, embedded with VB. They can't work out how to move forward now.
I feel for them. I've done work with customers where their entire code base was in VB.NET and ASP.NET WebForms. At least that was a step up from classic ASP. They had many hundreds of developers. Even if you're a stalwart who loved the tooling, you need to stop and as yourself where you are going to find staff who'll share that love.
Developers who desperately need an income (and thus a job), and are living from pay to pay, might work for you. But developers who have any options at all, aren't likely to do that.
You'll find it harder and harder to locate good people who want to work for you. You'll end up paying them much, much more than would otherwise be appropriate, and you'll end up lowering your standards for whom you're prepared to employ.
From the point of view of a prospective staff member, do you really want to have your resume showing you starting with that code today?
And if you really are willing to do any coding for the pay, why not learn COBOL and cash in? It won't take that long to learn, and there's still plenty of work around for those wanting to write it.
Finding out the current time is a really common requirement. But you also have to always consider whose concept of time you're considering. Is the the time at the server, or the time where you are? (You might not be in the same place, or more importantly, the same timezone).
The function SYSDATETIME() returns the current system date and time right now at the server. The data type that's returned is a datetime2. It's the higher precision datetime data type that was introduced in SQL Server 2008.
In much older code, you'll see GETDATE() used. That function was a lower precision one that returned the older datetime data type. Unless there's a real need for the older data type, I'd suggest using SYSDATETIME() now.
In the query shown above, there's also a SYSDATETIMEOFFSET() that gives also gives you the current date and time at the server, but it returns a datetimeoffset data type i.e. it has a timezone offset as well. The timezoneoffset is based on the current setting at the server.
Once this code was executed, it returned the following output:
The server time as 9th January 2019 at 10:25 AM. The only difference between the two return values is that the second one includes a timezone offset of +11:00 or eleven hours. (That was daylight saving time in Melbourne which is otherwise 10 hours ahead of UTC).
UTC (universal time) is basically what we called Greenwich Mean Time (GMT) when I was a child. If you really want that time, rather than the local time at the server, there's a function for that too. It's SYSUTCDATETIME(). You can see the effect of it in the output here, where the 11 hours has been removed in the second value:
One thing that fascinates me in Mandarin is the number of times you see a word repeated in a sentence. This can commonly occur with adjectives.
The simplest example would be a single character adjective that is repeated.
她的脸红红的。(Tā de liǎnhóng hóng de.)
This literally means "her face red red" or more likely "she blushed". But the word for red 红(Hóng) is repeated, in this case for emphasis.
Instead of saying:
你的眼睛真大的。(Nǐ de yǎnjīng zhēn dà de.) which says "your eyes are really big",
you get a similar effect by doubling the word for big:
你的眼睛大大的。(Nǐ de yǎnjīng dàdà de.)
Now where the fun begins is that many adjectives involve two characters, and instead of A becoming AA, the effect is that AB becomes AABB. (Not ABAB)
高高兴兴 (Gāo gāoxìng xìng) where 高兴 (Gāo xìng) means happy.
漂漂亮亮 (Piào piàoliang liàng) where 漂亮 (Piào liàng) means beautiful.
I'll write more soon on the best methods for learning Mandarin. If you want to get a taste for it in the meantime though, my current favorite site is iTalki, and my favorite teacher by far is Amy He. If you decide to try it, click here and it's cheaper for both you and me.
As part of our free SDU Tools for developers and DBAs, we have many scripting functions. SQL Server allows you to create your own database roles, and generally, you should do this instead of using the fixed database roles like db_owner, or db_datareader and db_datawriter, as it lets you allocate just the required permissions. To allow scripting these out, we've added a tool called ScriptUserDefinedDatabaseRoles.
It's a stored procedure (as it has to change databases) and takes a single parameter: @DatabaseName (sysname) as the name of the database whose roles you want to script.
You can use our tools as a set or as a great example of how to write functions like these.
Find out more
You can see it in action in the main image above, and in the video here:
Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:
Once again, I'm seeing lots of customers reporting screen repainting issues in SQL Server Management Studio (SSMS). It mostly seems to affect version 18 but I've also seen it in version 17. And it's most prevalent on Windows 10.
The typical issue is that you click on another open tab, and the contents of the tab doesn't repaint. You are still seeing the previous tab. If you click into the tab, you start to see bits from both tabs.
It's super annoying, and I've seen people lose quite a lot of work, because they didn't realise what was happening.
One possible fix
The one thing that seems to fix this in many situations is to change hardware acceleration. Previously, you'd do this by changing the screen driver settings, but where you need to do it for SSMS, is in the Tools>Options>General tab.
You need to disable the "Automatically adjust visual experience" option, and then you can disable the "Use hardware graphics acceleration if available" option.
For most clients, I've seen this fix the problem. I hope it helps you.
Now, one of the challenges when you go to write dates is that there's no standard separate format in T-SQL for how to write a date. Instead, we need to write it as a string. So it's very important to come up with a format that will work properly all the time the example.
In the example I have here, the order date is 20190128. If you use that format (8 digits for a date), it'll always work OK. SQL Server will take the first four as the year, then 2 for the month, 2 for the day and it does that, no matter what your settings are for your session or for your machine.
I've also shown an example of how to include the time.
Now a lot of people are confused with this, they would expect that the bottom format shown with dashes would be safe, or similar for slashes. It's not. That format is safe on the date data type and on datetime2, but it's not safe on the datetime or smalldatetime data types. For example, if you set your system to British English, that would be parsed as year-day-month, not year-month-day.
I think in general. You're better off to just avoid any confusion just write dates as 8 digits. You could also write them as the full ISO 8601 T format. But it has to be the complete format, including the T which is part of that standard generally that's a way more work than what we're suggesting here.
Ever since SQL Server 2012, another safe way to create a date is to use the DATEFROMPARTS function. It takes a year, a month, and a day and returns a date.