The Bit Bucket

When working with SQL Server Management Studio, sometimes you need to show someone else what you’re working on, and the fonts that you’re using are just too small for someone looking over your shoulder or looking at a screen that you’ve shared with them.

What I often see someone do then, is to go into Tools and Options and start to change the font and color settings. The pain with this is that you then need to set them back later.

There are several options to allow you to zoom in, without needing to change the settings.

First, like many Windows programs, you can hold down the Control key and use the mouse scroll bar to increase or decrease the font size. Note that as you do that, the percentage of zoom is also displayed in the lower right-hand side of the query window.

You could also, of course, just use that drop-down to set the zoom level too:

When you make this change, it applies to all current query windows, and all other query windows that you open until you change it back.

When you are trying to debug code in SQL Server T-SQL, or trying to monitor where code is up to, it’s common to add PRINT statements into the code.

These are fine when they are ad-hoc statements, but if you include PRINT statements in stored procedure code, and the procedure runs for any length of time, you’ll know that you normally don’t get to see the PRINT output in the Messages tab until the procedure ends.

And by then, it’s really too late.

In our free DBA and developer SDU Tools, we added a tool to make it easy to get messages immediately rather than waiting.

PrintMessage sends a message to the Messages tab in SQL Server Management Studio immediately.

You can see it in use above in the main image and here:

For more information on our free SDU Tools and to join our SDU Insiders to get more information on our tools, eBooks, webinars, etc. please just visit here:

http://sdutools.sqldownunder.com

Just a short note to let you know that I’m heading up to speak at SQL Saturday in Brisbane.

I’ll be delivering a session on SQL Server Management Studio tips and tricks and hope to get a chance to show you a number of ways to use SSMS that you might not have seen.

I really hope to see many of you there. If you are coming, please come and say hi.

If you can’t make it though, you can get a copy of our free SSMS Tips and Tricks ebook here: http://ssmsbook.sqldownunder.com.

If you’ve worked with earlier versions of SQL Server, it’s likely that you’ve configured SQL Server Reporting Services. To do that, the tool you use is Reporting Services Configuration Manager.

Lately, though, I’ve seen a number of people installing SQL Server 2017 Reporting Services and being confused. The same applies to Power BI Report Server installation.

During installation they are provided with an option to configure the report server, but if they go back into Reporting Services Configuration Manager, they can’t find the server. They get the No report servers error shown in the featured image above.

The reason for this is that there was a subtle name change in SQL Server 2017, as you can see here:

Note that there are now too options that could be on your system:

• Reporting Services Configuration Manager
• Report Server Configuration Manager

Nothing to be confused about there 🙂

The first option will still find your older Reporting Services installations and allow you to configure them.

The second option will let you find a SQL Server 2017 Report Server or a Power BI Report Server and configure it.

It’s worth noting that in SQL Server 2017, Reporting Services is no longer multi-instance. You can have one SQL Server 2017 Report Server OR one Power BI Report Server on a system.

One of the most common tasks that “data people” perform is moving data around, and that can include exporting existing database data, and importing data from other places.

While there are many other standards for how data is stored in files, CSV (comma-separated-value) files are still (by far) the most common. Another common variation are TSV (tab-separated-value) files, where tabs are used to separate values instead of commas. This is usually a good idea as commas occur frequently within the data.

Given these file formats are so common, how easy it is to read these files using T-SQL? And the answer is “way more complicated than it should be“.

So, we built one of our SDU Tools to come to the rescue. Our ReadCSV stored procedure takes the following parameters:

@Filepath nvarchar(max) – this is the full path to the CSV or TSV file
@Delimiter nvarchar(1) – delimiter used (default is comma but can be another character like  tab)
@TrimOutput bit – should all output values be trimmed before returned (very handy)
@IsFileUnicode bit – 1 if the file is unicode, 0 for ASCII
@RowsToSkip int – should rows be skipped (eg: 1 for a single header row with column names if it is present)

Here’s is an example of it being called:

The procedure returns 50 columns named Column01, Column02, and so on. It always returns 50 columns and will return NULL for any missing values. Note in this example though, that the column names were in the first row. So we can fix that by skipping the first row as shown in the main image above:

You can see the tool in action here:

For more information on our free SDU Tools and to join our SDU Insiders to get more information on our tools, eBooks, webinars, etc. please just visit here:

http://sdutools.sqldownunder.com

Code quality has always been an important topic ever since coding began.

Code complexity is an important part of this. One of the topics that came up many years ago was a discussion on what length procedures or functions should be, before they became too difficult to follow.

I remember one guy commenting that he thought as soon as all the code didn’t fit on your screen any more, you were much more likely to have bugs in it. At the time, screens weren’t all that big.

It’s an interesting thought but even today when writing T-SQL though, if you’ve ever worked on a 4000-line stored procedure, you’ll know how hard that is. If you must work on a long script, it makes it even harder if it’s also a wide script. Then you end up scrolling around in both directions. For this reason, most formatting tools like SQL Prompt have an option to limit the width of lines.

Breaking code into separate lines however, isn’t always possible. And if you’ve decided that you really don’t want to do that, you can enable word wrap in SQL Server Management Studio (SSMS).

One challenge with this is that it’s hard to work out at a glance, which lines have been wrapped and which haven’t.

The answer to this is to enable a visual glyph for the word wrap.

From the Tools menu, choose Options, then Text Editor, then All Languages, then General. Note the option for showing the glyphs:

Now when the word wrap occurs, you can see it quite clearly:

When you use table types in SQL Server, you need to assign permissions on them, and usually EXECUTE permissions. On one of my mailing lists, I saw a posting about how the user was trying to assign permissions to a table type  (for use as a table-valued parameter), and was getting this error:

Msg 15151, Level 16, State 1, Line 11
Cannot find the object, because it does not exist or you do not have permission.

He was wondering if permissions on table types didn’t work properly in Azure SQL Database, which was where he was executing the command.

The problem with this is that you need to assign the permission using the TYPE moniker, not directly to the object name. Let’s see an example:

If I execute the following code:

I’ll see this error:

What I needed to do instead, is to execute this code:

That will work just fine.

Umachandar Jayachandran (UC) from Microsoft pointed out to us that this is because objects and types are in different namespaces within SQL Server and the default namespace is OBJECT (ie: tables, views, stored procedures), not the namespace that has types.

In previous posts (here and here), I’ve described ways to configure the scroll bar in SQL Server Management Studio (SSMS).

There is another key option that I haven’t discussed previously: map mode.

By default, the scroll bar shows the changes, syntax errors, etc:

If you have a long script though, it can be hard to visualize what’s in the other parts of the script. Map mode can help with this.

Right-click the scroll bar and choose Scroll Bar Options:

Below the display options that we have previously looked at is another set of options:

When you choose to use map mode instead of bar mode (the default), the appearance of the scroll bar changes:

You are presented with a tiny preview of the script beside the colored indicator. Obviously you can’t read that but it can help to visualize the position of the code you are working on within the script.

If you hover over any code section though, it then shows you that code in a preview window: