SDU Tools: List user access to Reporting Services content

I often need to document the access that various users have to content stored in SQL Server Reporting Services (SSRS). Surprisingly, I didn't find any built-in way to do that.

We had started to add some Reporting Services related options to our free SDU Tools for developers and DBAs, and, just for this, we added a new procedure RSListUserAccessToContent.

It takes two parameters:

@IsOrderedByUserName bit -> Is the output ordered by user (default yes else by item)
@RSDatabaseName sysname -> Reporting Services DB name (default is ReportServer)

The output has the following columns: UserName, RoleName, PathName, FolderName, ItemName, ItemType.

You can see the procedure in action in the image above, and in the video here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

 

Opinion: Whatever happened to "ly" ?

In recent years, there's an odd trend that I've been noticing. Adverbs seem to be getting replaced by adjectives, and at an increasingly fast rate. I see signs that say things like this:

Drive Safe

Now when I was at school, we'd have been given a hard time for writing that. We'd have been told in no uncertain terms that it should have been:

Drive Safely

I was trying to work out if it was more of a US-based thing. I see it far more often in US-based writing, yet it's also happening in the UK, Australia, and others as well.

Puzzled by this, I was starting to wonder if it was just me, and, more importantly:

Whatever happened to "ly" ?

Turns out that I'm not alone in wondering this. This Quora discussion asked Why have so many people stopped using adverbs and instead use adjectives, such as "quick" instead of "quickly"?

The article argues that it's part of a very long-term trend, and that you notice it more as you age, and as you visit other places and come across other dialects.

More concerning(ly), this article asks: Is it poor style to use adverbs ending in "ly" in formal writing? Some commenting on the article make a more curious claim: Some grammarians consider "ly" ending adverbs as bad style in formal writing.

Now I don't see specific evidence to support that, and one person commented that it was primarily advice for sci-fi writers.

A recent article in the Guardian asks:  Where have all the adverbs gone? And how did they go?

The author says: Meanwhile, in everyday parlance in America, people are quite happy to do things "real quick". I hope that doesn't catch on here. There's plenty of time to bother saying "really quickly". 

At least it appears that I'm not the only one that's wondering where "ly" went. I'd love to hear your thoughts on it.

T-SQL 101: #19 Querying literals, expressions, and functions in T-SQL

Apart from data just in a table SQL server can select other things like the ones shown here:

If I say SELECT 2, it just returns the value 2.

If I say SELECT 'Hello', it just returns Hello.

Both of those are examples of what's called a literal value, which is an exact value that doesn't change.

SELECT 4 + 5 is an example of an expression. This is where we can work something out to get the value that needs to be returned. No surprise, that will return 9 just as you'd.

Finally, we can also select from functions. SYSDATETIME()  is a function that returns the current date and time at the server. We don't have to care about how that works internally, we can just SELECT it, and it'll tell us the current date and time.

So we're able to use expressions and values and both can be returned. Functions, though, are programmable objects, which can be used either as values or even as tables in a FROM clause but we'll see more about that in a later post.

Not all SQL engines do this

It's worth noting that not all SQL database engines allow us to have a SELECT statement without a FROM clause. For example, in Oracle, you can't just say SELECT 4 + 5. Instead, they have a dummy table called dual, which is a single column, single row table with a dummy value. Then you can say SELECT 4 + 5 FROM dual; instead.

I'm glad SQL Server doesn't require this.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

Learning Mandarin: Him, her, it, they, and them

In my previous Learning Mandarin post, I discussed personal pronouns and adjectives. Additional useful related words are:

Him, her, it, they, and them

Similar to the way that we have male, female, and other genders for these pronouns, Mandarin has the same concept but what's interesting is that the spoken words are the same for all of them. The written characters are different for each:

(Tā) is the word for "he".

(Tā) is the word for "she".

Notice that the right-hand side of both characters is the same but the left-hand part differs. In the male version, the left-hand is a squashed form of the character (Rén) which means person. You might recognize it from the Chinese currency 人民币 (Rénmínbì) which is literally "the people's currency".

For the female version, the left-hand part (known as a radical) is a squashed form of the character (Nǚ) which means "women", hence its use in "she".

For inanimate objects, yet another different written character is used:

(Tā) is the word for "it".

In English, we also change the words depending upon whether they are subjects or objects in a sentence i.e. He did it. I gave it to him.

Mandarin is easier with this because it uses the same words:

他做了。(Tā zuòle) is "He did it".

把这个给他。(Bǎ zhège gěi tā) is "Give it to him"

And finally, similar to last time, you make these plural by adding (Men) to the end, so

他们 (Tāmen) is the equivalent of "they" when it's the subject of a sentence, and the equivalent of "them" when it's the object.

Learning Mandarin

I'll write more soon on the best methods for learning. If you want to get a taste for it in the meantime though, my current favorite site is iTalki, and my favorite teacher by far is Amy He. If you decide to try it, click here and it's cheaper for both you and me.

SQL: Storing the names of objects in SQL Server tables and variables

When I'm writing SQL Server scripts, I often need to store the name of SQL Server objects (like databases, schemas, tables, procedures, etc.) in variables.

That can also happen when I'm creating tables. I might need to store a SQL Server object name (like a login name or a user name) in a column of a table.

So which data type should be used? varchar(100), varchar(200), nvarchar(max), etc. etc. ??

The answer is: sysname

sysname is a built-in data type that's currently stored as nvarchar(128).

While you could use nvarchar(128), that wouldn't be a great idea. If the SQL Server team ever need to change the size of object names, you'll have a problem if you'd used nvarchar(128). If, as recommended, you'd used the sysname data type, you'll be fine.

Fixing Locking and Blocking Issues in SQL Server – Part 6 – Avoiding Deadlocks

This is part 6 in a series of posts:

  • Part 1 covered being sure there is a locking and blocking issue
  • Part 2 covered the impact of RCSI
  • Part 3 looked at the impacts of indexing on locking and blocking
  • Part 4 looked at what deadlocks really are and how SQL Server handles them
  • Part 5 looked at how applications should handle deadlocks

Today, though, I want to look at how to try to avoid deadlocks in the first place.

You Can't

Let's start with the bad news. You can't totally avoid deadlocks without doing something like serializing all access to the database.

As I mentioned in Part 5, you need to expect and handle deadlocks when they occur.

Now we have the bad news out of the way, let's look at things that can help to minimize them. Even though we make our applications capable of handling them, I want to see as few of them happening as possible.

Fix the query performance

Part one of this series talked a bit about this. I often see significant blocking issues (and associated deadlocks) completely disappear when the queries run quickly.

The longer that a query runs for, the longer it's likely to hold locks. We want to minimize that time. Indexing is your friend here.

Minimize the locking (volume and duration)

The fewer things that are blocked, and the shorter time that they're blocked, the less chance there are for deadlocks in the first place. There are four key aspects of this:

First, (no surprise), indexing is a key issue here. If you don't give SQL Server suitable indexes to let it find just the data that you need to lock, you shouldn't be surprised when it ends up locking entire partitions or tables.

Second, the design of your tables is also an issue. A properly normalized database schema is easier to work with in terms of minimizing locks.

Third, you need to use the lowest isolation level that works for your queries. Part two of this series talked about using RCSI. Often that will really help. Your biggest pain point though is going to come from applications that insist on using Serializable as an isolation level. Unfortunately, many Microsoft applications default to using this (I'm looking at you Biztalk's SQL Adapter), as do many other things in Windows like Component Services. Whenever you have a choice and it's appropriate, reduce the isolation level to the lowest you can work with.

You might be wondering about the use of NOLOCK here. Clearly it might help if you can deal with the risk. Basically, you're giving up consistency for reduced locking and performance. If you're working only with archive data that isn't changing, it might be an ok option.

If you're working with data that's being actively changed, it's a significant risk. It's hard to explain duplicated or missing rows on reports, and even harder to explain data on your report that doesn't exist in the database because it was rolled back.

I also see people trying to put NOLOCK on INSERT, UPDATE, DELETE. Here's a hint: that's not going to work.

Fourth, you need to hold locks for the shortest time that you possibly can. I'm not talking about holding locks across user interactions, that would be just plain dumb. You need to get as much work done as quickly as possible. If you want high concurrency, holding transactions across multiple round trips to the database server isn't clever, yet I see it all the time.

Don't create a proc to insert an order header, then another proc to insert an order detail line, and then call them one by one within a transaction started by the client application. Instead, pass the whole order (or even multiple orders where it makes sense) directly to a procedure on the SQL Server that does the whole job at once. Table-valued parameters are your friend here.

Locks are typically happening within transactions so you need to keep the transaction durations very short. And that means that you need to manage them properly. Start them only when needed and commit them or roll them back as soon as possible.

The transaction handling in JDBC is a great example of how not to handle transactions.

It turns on chained mode (which means that SQL Server starts a transaction automagically when a data modification occurs), and then just periodically runs a command that says "if there is a transaction, commit it". Please don't do this. At least not if you're looking for high performance and/or concurrency.

Serialize the workload

One useful, (but possibly contentious) method of reducing deadlocks, is to try to ensure that you lock objects in the database in the same order within all your logic.

I remember a site that I went to in the 1980s where they had a rule that tables were always used in alphabetical order. That sounded like a bad idea at the time, and it still sounds like a bad idea.

However, you can make a big difference to deadlocks by using tables in the same logical order every time. For example, if you acquire an order number from a sequence, then using it in an order header, then write the order detail lines, write an audit entry, etc. and keep to that same type of order, you'll have a lot less deadlocks to deal with, as it will naturally serialize the work to be done.

When all else fails

Even with all the above, you still will likely have some deadlocks. Apart from all the handling options, you might consider exerting some control over who loses when a deadlock occurs. Perhaps you have a background task that you don't mind dying and restarting. In that case, you can set its deadlock priority low so it offers itself as a victim, rather than just having SQL Server work out who's written the least to the transaction log.

In the next post, I'll look at retry logic at the server end.

 

SDU Tools: LoginTypes and UserTypes in SQL Server

I write a lot of utility code for SQL Server. Many of the system tables include values for LoginTypeID and UserTypeID but I've never found a view in SQL Server that returns a description of each of those values.

To make it easy, in our free SDU Tools for developers and DBAs, we added two views (LoginTypes and UserTypes) to help.

You can see the views in action in the image above, and in the video here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

 

Book: The Little Book of Luck

One of the things that I love about digital books and audio books is how quickly I can go from a friend talking about one, to actually having it. This book is one of those. I can't actually remember who recommended this one but I recall looking it up immediately and purchasing it. It's The Little Book of Luck by Richard Wiseman.

Wiseman is a professor for public understanding of psychology in the UK. He states his interests as "unusual areas including deception, luck, humour, and the paranormal".

I don't share his interest in the paranormal. While it can mean things that are just quite out of the ordinary, the common usage of paranormal now tends to be things that aren't able to easily explained (at least right now), and that seem to break things like the laws of nature. I don't see any concrete evidence for any of those.

However, this book tackled "luck".

I've spent quite a bit of time in Asian communities over the last decade and "luck" is something that all these communities seem to have a profound belief in, and seek out. Try selling a house number 24 to a Chinese person compared to selling them a house number 8. Lots of "luck" with that.

Clearly, I don't believe that "luck" is a thing. And while Wiseman doesn't come out and say that directly, he does give plenty of hints that what appears to be luck is often just a positive view of something that's happened.

I like the way that the book talks about ways to view the world, and basically, building an optimistic outlook all the time.

It's only a short book and while you could read the whole thing in half an hour if you rushed through it, it's worth spending some time contemplating his ideas.

Bottom line?

I didn't expect to but I did enjoy reading this small book,  and it's a pretty easy read. I was really expecting more but it might be just the thing to brighten your day.

Greg's rating: 6 out of 10

Note: as an Amazon Associate I earn (a pittance) from qualifying purchases but whether or not I recommend a book is unrelated to this. One day it might just help cover some of my site costs. (But given the rate, that's not really likely anyway).

T-SQL 101: #18 – Removing duplicate rows by using DISTINCT

If I query rows from a SQL Server table, I don't always want all the rows returned. In particular, I might not want any duplicates. I can remove them by using DISTINCT.

Here's a simple example. I want a list of the sizes that products can come in, so I execute this query:

Note that although I get a list of sizes, I get a row returned for every row in the table. If I add DISTINCT to the query, look at the effect that it has:

The removal of duplicates applies to all the columns listed after DISTINCT, so if I selected Size and Color, I'd get all the combinations of Size and Color that have been used.

The Perils of DISTINCT

I often run into situations where DISTINCT is overused. What happens is that developers don't get their join operations working properly, end up with duplicates, and "fix" the query by adding DISTINCT to the SELECT clause at the top. Try to avoid this by making sure your joins are done properly first. Don't rely on DISTINCT to fix join problems.

DISTINCT vs GROUP BY

In a later post, we'll talk about GROUP BY but it's worth noting that writing:

SELECT DISTINCT Size
FROM dbo.Products
ORDER BY Size;

is really the same as writing:

SELECT Size
FROM dbo.Products
GROUP BY Size
ORDER BY Size;

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

Learning Mandarin: Personal Pronouns and Adjectives

In English, we have personal pronouns and adjectives like:

I, we, you, your, our, my

So what are the equivalents in Mandarin?

(Wǒ) means "I". It's pronounced pretty much like the English "war". However, in English, a possessive form is my. In Mandarin, we use the same word as for I, but add on an indication that it's possessive.

我的 (Wǒ de) means "my". The de is pronounced a bit like "da" and

我的朋友 (Wǒ de péngyǒu) means "my friend".

To make it plural, there is another qualifier that we can add.

我们 (Wǒmen) is "we".

And just like before, we add to make it possessive.  So

我们的 (Wǒmen de) means "our" and

我们的朋友 (Wǒmen de péngyǒu) means "our friend"

The word for you is (Nǐ), the plural word is the same in English but it's

你们 (Nǐmen) in Mandarin. And no surprise that

你们的 (Nǐmen de) means "your".

Two Other Oddities

There are two other oddities that I want to mention today.

In English, we just have the word "you". Mandarin has two forms of this. We just saw

(Nǐ)

but there is another, more polite form, that's

(Nín)

It's often used to honor customers, people older than yourself, etc.

The other interesting one is the word for "we". In English, that might or might not include the person you are speaking to. It might include yourself and someone else, or yourself and the person you're speaking to, or perhaps also someone else.

We saw

我们 (Wǒmen)

before as the word for "we". However, if the person you're speaking to is also included, the correct term is

咱们 (Zánmen)

Learning Mandarin

I'll write more soon on the best methods for learning. If you want to get a taste for it in the meantime though, my current favorite site is iTalki, and my favorite teacher by far is Amy He. If you decide to try it, click here and it's cheaper for both you and me.