SQL: No report servers were found: what’s in a name?

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.

SDU Tools: Read CSV File in T-SQL

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

 

 

 

Shortcut: Using visual glyphs in SQL Server Management Studio query editing

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:

Machine Learning: Testing your installation of R and Python in SQL Server 2017

One of the wonderful additions to SQL Server in 2016 was the R language. In SQL Server 2017, Python was also added and the combination of both with SQL Server rebranded to Machine Learning Services.

Why would you want these installed? The most common answer is to enable you to run predictive analytics.

But I’ve found that at many sites, getting R and/or Python installed turned out to be more complicated than it seemed.

Once you have the features added (the in-database options and not the standalone options) for R and Python, you need to enable the execution of external scripts. That’s easy enough:

You need to restart the SQL Server service after doing this.

Now you can try to execute this script to see if the features are working:

If all is good, you’ll see that it worked. Both values would be returned.

If you haven’t changed the default SQL Server configuration though, I don’t think that’s what you’ll see. More likely, you’ll see this:

Msg 39012, Level 16, State 1, Line 4
Unable to communicate with the runtime for ‘R’ script. Please check the requirements of ‘R’ runtime.

STDERR message(s) from external script:
Fatal error: cannot create ‘R_TempDir’

So, why does that happen? It’s SQL Server’s attempt at telling you that R doesn’t like paths with spaces in them, and that’s what the default configuration has. (I have no idea why).

If you open Notepad (or your favorite editor) as an administrator, navigate to this file:

The file rlauncher.config holds the configuration for the R feature.

That path for the working directory isn’t going to work. Now you can change the values in it to 8.3 filenames like they have for RHOME in the first line, or, my preference, point it to a different temp folder.

I then make sure that C:\Temp actually exists, and that there’s an ExtensibilityData folder under that. I then copy in all the folders from the original folder:

These are used for different processes running from the SQL Launch Pad.

Then restart both the SQL Server service and the SQL Launch Pad service and try your script again.

If you still have no luck, chances are high that the security isn’t correct for the folders. Ensure that the SQL Launch Pad service account has full control on the ExtensibilityData folder and all sub-folders. By default, the service account will be MSSQLLaunchPad but you can check it in SQL Server Configuration Manager.

I’d restart both services again just for good luck, and then hopefully you’ll see a response from both the queries.

Then you’re ready to start investigating Machine Learning in SQL Server.

 

 

 

SQL: Assign permissions to the table type not directly to the object name

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.

 

 

Shortcut: Map mode in the SSMS scroll bar

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:

Shortcut: Running SQL Server Management Studio as someone else

You don’t always want to run SQL Server Management Studio (SSMS) as your current login for Windows.

Now if all you want to do is to use a SQL Server login, then that’s easy. When you connect to a server in Object Explorer, or when you start a new Database Engine query, you can just choose SQL authentication instead.

But three other scenarios commonly occur.

If you need to run SSMS as an administrator on a machine with UAC, you can do this:

You right-click the link to SSMS and choose Run as administrator.

Another option is that you need to run as another user ie: not just an administrator. If you are using Windows 10, the way to do this is to hold the Shift key before you right-click the SSMS link. Then you see this instead:

And you can then choose the Run as a different user option to log on as someone else just for this one application.

The third scenario is when you are needing to make a Windows authenticated connection to a server, but from a system that is not part of the domain. In that case, you need to run SSMS from the command line like this:

You will need to modify that command to point to the current location of Ssms.exe for the version that you are running.

You will then be prompted to log on to the domain.

Shortcut: Adding multi-level undo, redo to the SSMS toolbar

Years ago, I had the privilege of presenting “what’s new in SQL Server 2012” sessions at many locations around the world. When you present sessions, you can sometimes learn as much as you teach. What I learned while delivering those sessions is that the product group sees what’s important in a release very differently to what the attendees do.

Each time there’s a new version of SQL Server, there will normally be three or four marketing pillars (groups of functionality), and each will have about eight to ten bullet points.

So, when SQL Server 2012 was released, what got the biggest reaction from the audiences? Was it the new availability groups? Was it the new tabular data model in SSAS? Was it the new project model in SSIS?

It might (or might not) surprise you to hear that the biggest reaction world-wide to the release came when I showed the audience that the undo/redo options in the SSIS designer now actually worked.

Even better, they were multi-level undo and redo.

SQL Server Management Studio can also use the same option. By default, it only has a single-level undo and redo. It can go back quite a distance in undo but it does so one step at a time. Adding a multi-level undo and redo can really improve your editing experience. I have no idea why it’s not there on the toolbar by default. So let’s fix that !

First let’s see the toolbar as it started.

Right-click a blank area in the tool bar then choose Customize.

Then choose the Commands tab, the SQL Editor for the Toolbar, and click Add Command:

From the Categories, choose Edit. When the commands appear, scroll down to the MultiLevel Redo and click OK.

Do the same to add MultiLevel Undo, then move them down to where you want them using the Move Down button. I like to have them just after the comment / uncomment buttons.

Once they are in place, note that unlike the normal undo (or Control Z), you can click the button, see a list of your previous actions, and choose how many to apply.

 

Once you undo some, the same applies to Redo.

 

 

SDU Tools: Alphabetic Only and Alphanumeric Only in T-SQL

Sometimes you need to remove all characters from a string, except for a certain type of character. This often happens when processing incoming data from an external source, before you use it within your systems.

In our free DBA and developer SDU Tools, we added some functions to help with this.

AlphabeticOnly limits the output string to just alphabetic characters, based on the English language.

AlphanumericOnly limits the output string to just alphabetic characters (again based on the English language) or the digits from 0 to 9.

You can see both in action in the main image above and here:

For more information on joining SDU Insiders to get our free SDU Tools and other resources, please just visit here:

http://sdutools.sqldownunder.com

Shortcut: Using script projects and solutions in SQL Server Management Studio

I’m puzzled that so few people use script projects and solutions when working with SQL Server Management Studio (SSMS).

They are easy to use. Let’s see an example:

Instead of just starting to create scripts, from the File menu, click New, then Project. You are greeted with the new Project dialog which also allows you to create a solution.

I’ve selected SQL Server Scripts as the project template. Note there is also one for Analysis Services scripts. I’ve named the project, picked a location, and chosen to create a new solution. I might choose to create a solution with a different name if it will contain multiple projects. In this case, I’m not doing that.

To get to the point faster, here’s one that I created earlier:

There are three sections of interest:

Connections – keeps details of all connections used by the project. I typically only have one for any project but I can imagine scenarios where I would have more. A big advantage of this is that if I need to change the connection, I do it once, and all scripts here will use it when I open them.

Queries – as the name says. A nice addition in recent versions is that it automagically keeps them shown in alphabetical order. It didn’t used to do that so we had to remove them all and put them back in.

Miscellaneous – you’ll find if you right-click this that there’s nothing of interest. So how does something get there I hear you ask? Well it’s when you right-click the project and ask to add an existing item.

If you pick any file apart from those with a .sql extension (such as a text file), they’ll be placed into the Miscellaneous folder.

These projects are a great free built-in way to manage your scripts.