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
In an earlier post, I described how I didn’t particularly like all the colors that are shown in the scroll bar now in SQL Server Management Studio (SSMS):
In that post, I described how to turn them all off, or at least how to kill off some of them. But, of course they are there for a reason. Instead of turning them all off, you might decide to make sense of what they are there for.
2018-05-03
If you aren’t aware of the SQL Server data type called sql_variant, don’t feel bad; you’re not alone. Many people who’ve worked with SQL Server for a very long time haven’t used it.
sql_variant is a very special data type. It’s the data type that you use when you need to store other data but you’re not sure what type you will need to store. Once it is stored though, it has the appropriate data type.
2018-05-02
