The Bit Bucket

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

For many years, I was blogging at sqlblog.com and I was a big fan of what Adam Machanic and Peter DeBetta had done there. Eventually though,  community server was on its last legs, and WordPress seemed the obvious platform for a blog. Fellow MVP Adam Machanic made it really easy for me to migrate to a WordPress site with a tool that he had created.

I headed off to BlueHost with high hopes, but those hopes just haven’t been fulfilled. I’ve had a number of times that things just stop; it’s hard to get to the bottom of what’s causing it; the support is really glacial at times (ever had a chat with someone who is having a conversation with 10 other people at the same time?); and it turned out that what’s broken was something that apparently I was responsible for but didn’t even know existed.

2018-05-15

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

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.

2018-05-10

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.

2018-05-09

The other day, I joined a Skype for Business call from the Microsoft Regional Director program that I’m part of.

I was using my iPhone and I chose to use the web option to connect. I’d say it must have flipped me across to using Skype for Business anyway. (It is installed on my phone).

I thought there would be a large number of people in the meeting, and that we’d be muted the whole time, so when it asked if it was OK for the app to use the microphone, I said “no”. Clearly I should have just left myself muted instead of disabling microphone access.

2018-05-08

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.

2018-05-07

In early versions of SQL Server, the only way to try to track dependencies between tables, procedures, functions, etc. was to use the sp_depends stored procedure. And everyone thought it lied. The real problem was that it didn’t understand partial dependencies and deferred resolution of objects. For example, it got confused if you created a procedure that mentioned a table, then later created the table.

SQL Server 2012 introduced far superior dependency views, and SQL Server Management Studio (SSMS) now shows dependencies using those views under the covers.

2018-05-04