Shortcut: Apply cut or copy commands to blank lines when there is no selection

When I’m doing a lot of query editing, I often get a bit mesmerized, particularly if there’s a lot of manual copy and paste or cut and paste going on.

One thing that often drives me crazy is when I use Ctrl-C (ie: copy) when I meant to use Ctrl-V (ie: paste). Invariably, I do this when I have nothing highlighted at all. So not only did I not get the value pasted, I just copied an empty value into the clipboard.

But SQL Server Management Studio (SSMS) has your back on this. (And so does Visual Studio)

In Tools, then Options, then Text Editor, then Transact-SQL, under the General tab, there’s an option for Apply Cut of Copy commands to blank lines when there is no selection.

The default is that it works as expected (like you don’t want it to), but if you uncheck it, you might have just saved yourself some annoyance.

If I’m working with this code:

and I highlight the word DECLARE and hit Ctrl-C, then move to the blank line and hit Ctrl-C instead of Ctrl-V, I’d have just lost my first clipboard item. With this option unchecked, I can just smile, and hit Ctrl-V again, and it will still paste:

SDU Tools: Clear Service Broker Transmission Queue in T-SQL

Service Broker is one of my favorite tools in SQL Server. So many applications need to have a transactional queue, and many people try to build them using tables and other objects. But SQL Server has Service Broker and with it, you get the beauty of using the knowledge of someone who already knows about queues having thought about how they need to work.

But while Service Broker is quite forgiving, it’s common while developing Service Broker applications to make mistakes and end up with messages in queues that will never be delivered because you had some bug in your code. Service Broker doesn’t want to ever just throw your data away.

One of our free SDU Tools for T-SQL developers and DBAs is ClearServiceBrokerTransmission queue. Let’s look at an example of using it.

We’ll create a new database called Development and use it:

Then create a master encryption key, a queue, and a service:

Now let’s send a message that will never be delivered (because the target service doesn’t exist):

We can query to see that it’s still in the transmission queue:

If we scroll to the right, we can see why it’s stuck:

Now we realize that we messed up the service name and it will never be delivered, so we want to flush the queue. We can do that easily:

Note that if you are following along with the code and don’t have SDU_Tools installed in that database, you’ll need to change the EXEC line to point to a database where they are installed.

And finally, we could just clean up:

You can see this tool in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

 

 

 

Opinion: Design your own job

One of the software houses that I’ve done some work for over the years has had a number of unexpected issues with their clients and had to shed quite a lot of their staff. This is always a concerning time and I’m seeing a lot of worried and unhappy people. Either they don’t think  their jobs will last, or they are upset at having been moved to roles that they don’t want.

Many see no option but to try to stick it out, even if they hate what they’re doing.

When I was young, the perceived wisdom was that it was best to get a job with a large company, as they have the stability for long term employment. I saw friends heading into banks, government departments, and Fortune 500 companies.

Image by Jordan Andrews
Image by Jordan Andrews

I’m sure there was a time long ago where this worked but I think the concept of stable employment at large companies is almost illusory nowadays. In so many organizations that I deal with, I see pretty regular churn, and whole teams of good people discarded, almost at a whim.

By comparison, my friends that have created their own jobs have had by far the most stable and  satisfying careers. Many have built something up and are still doing it, even if the specifics have evolved over time. The other argument for larger companies has been that higher income can be achieved, yet many who have created their own jobs have now earned far more than if they’d joined a large company.

One of the beauties of being in these companies only on a part-time contract basis, is not being concerned when these seismic changes occur with organizations.

The way that I see the world evolving, I think it will be more important than ever to be in control of your own destiny. While it can be useful to get a good grounding in a business area from a larger company (to perhaps get a better understanding of the norms and professional standards of your industry), your future is likely to be brighter if you take care of it yourself, rather than outsourcing it to the whims of some company that you don’t control.

You need to be prepared to also take on the responsibility of your own career development ie: get yourself trained on useful areas, keep across new technologies, learn new skills. Be prepared to invest in yourself, not be someone who is whinging because your company isn’t developing your career the way you’d like.

And while you’re at it, find something that you love to do.

Over time, I can see there being far less traditional 9-5 full-time job roles available, particularly at the lower-skilled end of the market. Don’t be one of the “oh woe is me – who will give me a job now?” people.

Design your own job; take the initiative to make it happen. It may take a while but start today; invest in yourself, and take control of your own future.

SQL: And where did the SSRS Private Assemblies folder move to in VS2017?

Recently, I posted about the templates folder for SQL Server Reporting Services designer moving in Visual Studio 2017. You’ll find that blog post here.

I’ve also now found that the Private Assemblies folder has moved too. I do wish these locations would be fixed, shared, and known rather than needing to play hide and seek when updates are made to the SSRS designer tooling.

The Private Assemblies folder was used to hold a copy of any .NET assemblies that are referenced in your SSRS projects. By using .NET assemblies, you can share logic that’s used in reports. It’s worth noting that wherever we can, we try to avoid using .NET assemblies for this, and wish that SSRS had a way of dealing with common code inclusions, but that’s a topic for another day.

Generally we only use code in reports for things like formatting functions, because we think that reports should render the data, not really calculate the data. (That should be done by a procedure or function on the server).

One of the reasons that we avoid using .NET assemblies for shared logic in reports is that when you do that, you buy yourself deployment issues. Where will the assembly (DLL) live? You basically have two choices:

Install it in the GAC (global assembly cache) – we don’t like this one as it pollutes the GAC with application-specific logic.

Install it in both the Report Server’s bin directory, and also in the private assemblies area for the SSRS designer. It has to go in two places as the designer needs to be able to find it during design time, and the report server needs to be able to find it at run time.

What has recently moved though, is where the private assemblies folder is located. Previously it was here:

\Program Files (x86)\Microsoft Visual Studio 14.0
\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

Now it’s either here:

\Program Files (x86)\Microsoft Visual Studio\2017
\SQL\Common7\IDE\CommonExtensions\Microsoft\SSRS\

Or here:

\Program Files (x86)\Microsoft Visual Studio\2017
\Enterprise\Common7\IDE\CommonExtensions\Microsoft\SSRS\

Like last time, I presume the difference is because of how the Visual Studio shell has been installed ie: either by SSDT (the SQL one), or by Visual Studio (the Enterprise one).

 

 

DevOps: Thoughts on Microsoft’s Acquisition of Github

I have many friends who would have checked the calendar when they first heard that Microsoft was buying Github. They would have guessed it was April 1st.

I think it’s another pretty bold strategic move by Satya Nadella.

It’s been interesting to see all the naysayers coming out of the woodwork to beat up on the idea of Microsoft owning Github, as though it was going to be the death of Github. Almost every single time I hear a justification though, it is based on their opinion of Microsoft or something they did often decades ago.

People outside the Microsoft camp seem genuinely unaware of the seismic changes that have happened within Microsoft in recent years. As someone who has worked with them and followed them closely for decades, I can tell you that it is a very, very different company now. If your opinion of them is based on anything more than a few years old, it’s time to re-evaluate your stance.

Microsoft is already a heavy user of Github, and is the largest contributor to open source software on the planet.

But more importantly, their acquisition puts Github into a solid financial position that it did not have before. Github was pretty much at a crossroads, had a few suitors, but in any rational evaluation, Microsoft was the best positioned for this.

From Microsoft’s point of view, I can see how it will beautifully mesh with many ongoing changes within the company, particularly as things like Azure Functions take hold. It also provides more certainty for existing Github enterprise customers, and will introduce a whole new raft of enterprise level customers to Github.

The one big concern that I have is around identity. This is an area that Microsoft hasn’t yet sorted out. There are still too many issues with Microsoft Accounts vs Organizational Accounts and so on. There needs to be a good plan to integrate the Github identity system.

Shortcut: My favorite keyboard shortcuts in SQL Server Management Studio (SSMS)

Visual Studio is a very configurable tool, and particularly in the area of keyboard shortcuts. Because SQL Server Management Studio (SSMS) is based on Visual Studio, it inherits many of these configuration options.
SSMS has a very rich set of keyboard shortcuts. Without trying to cover most of them, I do want to highlight a few that I think are really important to know how to use.

Let’s start with an easy set of commands:

You might need to change the case of some values. If I have this code:

I might decide that our new coding standards say that variables that are treated like constants, must be in all capitals and with words separated by underscores. (This is often called SHOUTY_SNAKE_CASE).

I can just highlight the name (or double-click it), the hit Ctrl-Shift-U to make it upper case.

And Ctrl-Shift-L would make it lower case.

Now, what if I want to rearrange the order of these declaration lines. If I needed to move the @A_CONSTANT_VALUE line below the other line, I often see people highlight the whole line, then cut it, then paste it below.

A great keyboard shortcut for doing that, is to hit Alt-Shift-T. No matter where you are on the line, it just moves the line down by one.

If I wanted to then add a line in between these two lines, what I typically see people do is to put the cursor on the D in the second line (at the beginning of the line), then hit Enter to move it down one, then use the up arrow to go back to the newly emptied line.

What you can do instead is put the cursor anywhere on the first line, and hit Ctrl-Shift-Enter.

I’m sure that I’m somewhat anal, and because of this, I also often spend time cleaning up lines. So if I come across a line like the second one here, the spacing grates on me:

The quickest way to clean up the second line is to highlight the whole line, then hit Ctrl-K followed by Ctrl-Backslash.

Notice that it automagically removed all the messy whitespace. It can do multiple lines at once, but it won’t rearrange what’s on each line.

Finally, if I needed to comment out this code, I’d highlight it and hit Ctrl-K then Ctrl-C.

And Ctrl-K followed by Ctrl-U will uncomment it.

Two more easy shortcuts: Ctrl-Home takes you to the top of the script. Ctrl-End takes you to the bottom of the script.

The final most useful keyboard shortcut is Ctrl-R. It hides or shows the results pane.

You’ll find a detailed list of keyboard shortcuts here:

https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-keyboard-shortcuts

 

SDU Tools: Character to Hexadecimal in T-SQL

I often need to convert values from one data type to another, particularly when working with strings. One function that I’m surprised wasn’t already built in to SQL Server was one that converts a character to its hexadecimal representation.

There is an ASCII function. You can see it here:

This converts a character to its ASCII value (see http://asciitable.com)

We’ve added a function to our free SDU Tools collection for developers and DBAs. It’s called CharToHexadecimal and you can see what it does in the main image above.

You can also see it in action here:

For more information on being an SDU Insider and getting our tools and eBooks, just visit here: http://sdutools.sqldownunder.com.

 

 

SQL: Where did my Reporting Services templates disappear to in VS2017?

I’m always telling students that one of the first things you need to learn to do when working with SQL Server Reporting Services (SSRS) is to create report templates.

Now I really wish that SSRS had a real templating system, but what we have at present is just a folder that contains the items that we can create. For example, in an SSRS project, if I right-click Reports, and click Add, then New Item:

I see a list of templates to choose from:

Now what I really want to see is something like MyCompany_A4_Portrait ie: I should already have company templates set up for paper size and orientation, etc. Usually I’d have a template that already has a logo, a place for parameters to be displayed, a report title, etc.

Templates are just report files (ie: RDL files) but placed in a special folder on your system.

If you’re starting with a blank page to create a report, you’re doing it wrong.

But where do these templates come from? In the Visual Studio 2015 version of the tools, they were here:

C:\Program Files (x86)\Microsoft Visual Studio 14.0\
Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

I found after installing the VS2017 version of the tools though, that they weren’t there any more. Instead, so far I’ve found them in different locations on different machines. One was here:

C:\Program Files (x86)\Microsoft Visual Studio\2017\
Enterprise\Common7\IDE\CommonExtensions\Microsoft\
SSRS\ProjectItems\ReportProject

The other was here:

C:\Program Files (x86)\Microsoft Visual Studio\2017\
SQL\Common7\IDE\CommonExtensions\Microsoft\
SSRS\ProjectItems\ReportProject

At one of those locations, you’ll find these:

Note the difference is that it either says SQL or it says Enterprise in the path. I presume this depends upon which version of the VS shell you have installed, either the version installed by SQL Server Data Tools, or the full version installed by Visual Studio.

I wish this was less confusing, and I wish there was a standard shared folder that templates could live in ie: shared amongst a group of developers in a standard way.

 

SQL: SELECT TOP 100 PERCENT is a code smell for SQL Server

We help a lot of customers upgrade their systems. Part of that job involves looking for code smells before starting the upgrades. If you aren’t familiar with code smells, this Wikipedia article explains it pretty well: https://en.wikipedia.org/wiki/Code_smell.

Specifically, they describe a code smell as any characteristic in the source code of a program that possibly indicates a deeper problem.

One of these is the presence of TOP 100 PERCENT or perhaps written as TOP (100) PERCENT in SELECT queries. Almost always, we see this in the definition of views.

In SQL Server, a view is basically just a SELECT statement that’s been given a name.

Tables don’t have a natural order, and neither do views. If you want to output rows in a specific order, you need to use an ORDER BY clause in the queries that reference the tables or views.

What usually leads to this code smell is that the developers have attempted to create an ordered view. Let’s see an example.

In the WideWorldImporters database, I could write a query to return all the USB food items:

Note that I haven’t included an ORDER BY clause. The data happens to be in StockItemName order but that’s not guaranteed at all.

Now imagine that I want to create a view, but I want that view to return the rows in StockItemID order. I could try to create it like this:

That will return this error:

You are correctly told that the ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions. That’s because those objects don’t have any natural order.

But it’s the final part of the message that leads developers astray: “unless TOP, OFFSET or FOR XML is also specified”.

So they change the query as follows:

And the error goes away.

Unfortunately, in SQL Server 2000, because of a qwerk in how that version was implemented, it also worked as expected.

The problem here is that the TOP statement wasn’t implemented well, back in SQL Server 2000. It used the ORDER BY clause both for determining the TOP rows, and for the output order of the query. It really should have had a separate option on TOP instead, something like an OVER clause:

SELECT TOP(10) OVER (ORDER BY ProductID)
….
ORDER BY CustomerID;

So this led to people thinking they could create ordered views by including a TOP statement.

In SQL Server 2005, this stopped working as expected. The ORDER BY in the view was used for implementing TOP but it wasn’t used for setting the order of the view itself. Customers started complaining that it was now broken. It wasn’t broken at all. It used the TOP statement to work out which rows to return (ie: all of them in this case), and then returned the rows in any order it felt like.

Views don’t have any natural order.

So if we see TOP (100) PERCENT in code, we suspect that the developer has attempted to create an ordered view and we check if that’s important before going any further. Chances are that the query that uses that view (or the client application) need to be modified instead.

ML: Properties of the LaunchPad service – changing the concurrent session limit

In a recent post, I discussed issues I found when testing the Machine Learning Services setup in SQL Server 2017.

After that, my old friend Niels Berglund also posted about issues he found after installing CU7 (cumulative update 7) and how he solved them. Niels’ article is here: http://www.nielsberglund.com/2018/06/01/sql-server-machine-learning-services-and-sql-server-2017-cu-7/

What each of those articles discussed though was detail on how temporary files are used by Machine Learning Services in SQL Server to hold R or Python data for sessions. By default, SQL Server configures itself to hold data for up to 20 concurrent sessions.

You can change that number.

In SQL Server Configuration Manager, find the LaunchPad service:

In the Properties page for the service, select the Advanced tab. In the main image above, you can see the value that you can change to increase the number of concurrent sessions.

Values up to 100 are permitted.

SQL Server also creates passwords for these “external” users. If you have a policy that requires regular password changes, you’ll see that there’s also an option in this window to let you change all of them.

(It’s worth noting that current NIST guidelines say that you shouldn’t force regular password updates anyway – you’ll find more info here: https://auth0.com/blog/dont-pass-on-the-new-nist-password-guidelines/)