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

 

 

 

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).

 

 

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/)

 

 

 

Shortcut: Using line numbers and Go To in SQL Server Management Studio (SSMS)

If you ever have long scripts in SQL Server Management Studio and need to refer to a particular line, it can be helpful to have line numbers shown. This is even more useful if you ever need to write a set of instructions for someone on how to modify a query, or if you are trying to describe how a query works.

Instead of having a query like this:

In Tools, then Options, then Text Editor, then Transact-SQL, on the General tab, there is an option for Line Numbers:

Once that’s enabled, the query windows look like this:

The line numbers go in a separate margin. Note that all lines are numbered, unrelated to whether or not they contain an individual SQL statement.

If you have a long script and you need to quickly, reposition the cursor, note that you can also go directly to a line number. On the Edit menu, you can see that Ctrl-G is the shortcut for Go To.

Hitting Ctrl-G pops up a dialog and asks me for a line number:

Then takes me straight there. Note that in the dialog, the maximum line number is shown.

SDU Tools: SQL Server Version for Compatibility Level

While SQL Server includes an amazing array of built in functions and procedures, one that’s always puzzled me is the lack of a simple function to convert a database compatibility level to a SQL Server version ie: how do you know that 110 was SQL Server 2012?

And so, you don’t need to wait any longer.

One of the tools in our free SDU Tools collection for database developers and DBAs is SQLServerVersionForCompatibilityLevel.

It’s a simple function that takes the compatibility level (an integer), and returns the name of the SQL Server version (a string) as you can see in the main image.

You can see it in action here:

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