Sql-Server

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.

2018-05-24

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.

2018-05-23

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.

2018-05-22

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.

2018-05-21

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.

2018-05-18

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.

2018-05-17

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.

2018-05-16

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.

2018-05-14

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.

2018-05-12

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:

2018-05-11