The Bit Bucket

I mentioned last week that I’ve been needing to write queries against the SQL Server Reporting Services catalog. I often need to list the items that are contained in the SSRS catalog.

So, in our free SDU Tools for developers and DBAs, we added a procedure that does just that. It’s called RSListContentItems.

It takes two optional parameters:

@RSDatabaseName sysname - the name of your SSRS database

@IsOrderedByUserName bit - should the output be ordered by user name?

2019-10-16

When I first saw Grammarly appear, I thought “what a great idea”.

I signed up for an account and started using it, and really liked what it did for my writing. It seemed to work well, although it got seriously messed up sometimes. It really didn’t like it when I had a bunch of code on the screen. Overall though, I liked it.

But then one of my security-focused friends asked me:

2019-10-15

Today, I’m continuing the discussion on the variety of data types supported by SQL Server. I’ll round out the discussion by looking at the operators that are used with the numeric data types. Here are the operators:

Some of these are fairly obvious but even then, they can have hidden complexity.

We use the plus sign + to add numbers, and we use the minus sign (or dash) - to subtract numbers. No surprise there.

2019-10-14

One thing that I quite like about Snowflake is the way it cleanly works with a wide variety of file formats.

Obviously this changes over time but at the time of writing, you could COPY from the following source file formats:

• CSV
• JSON
• AVRO
• ORC
• PARQUET
• XML

There are also quite a number of options for configuring how these are used. Apart from the obvious options like record and field delimiters, skipping rows, etc, one of the most important of these options is compression. You can currently choose these options for compression:

2019-10-11

There was an interesting question in the forums the other day. The poster wanted to be able to put entries in a table to determine who could access data in the other tables in the database.

There are two basic ways to do this. If you want an error thrown, you’d be best just using GRANT/DENY/REVOKE as permissions and not using your own table to control it. However, if you want no error, but just no data, then the Row Level Security (RLS) added in SQL Server 2016 could do the job. Let’s take a look:

2019-10-10

This post relates to another question I got on Stack Overflow recently.

The poster was saying that he was having no luck inserting data from teh Gujarati language, even though he was using nvarchar as a data type.

The most common problem that I see when people aren’t getting the outcome they want when inserting into an nvarchar column is that they aren’t putting N in front of their string values. (N is National Characters Set). Imagine a table like this:

2019-10-10

Lately, I’ve been needing to write queries against the SQL Server Reporting Services catalog. And if you’ve ever tried that, you’ll find that items in the catalog have a type, but there’s no table or view that turns that type (a number) into a name.

So, in our free SDU Tools for developers and DBAs, we added a simple view that does just that. It’s called RSCatalogTypes.

Find out more

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

2019-10-09

Knocking Access was a popular sport over the last decade or more. Many data professionals saw Access as a real problem. Lots of silos of unmanaged data grew up across organizations and things could get out of hand pretty quickly. I saw all the expected problems that come from a lack of centralized management of data.

Some issues were quite nasty. I remember doing work for a company that did aircraft maintenance and had depots all over the country. Every depot had a copy of an Access database, but every single one had then modified it in different, and in many cases, conflicting ways. Then they decided to centralize the data, and oh what a pain.

2019-10-08

Today, I’m continuing the discussion on the variety of data types supported by SQL Server. Last time I mentioned exact decimal numbers, but there are also inexact (or approximate) decimal numbers. Here are the available types:

These two data types float and real are approximate data types.

You probably realize that in decimal, there are values we can’t store exactly like 1/3. No matter how many 3’s we write when we write 0.33333333, we are still never going to have the exact value.

2019-10-07

Snowflake has the normal options in its SQL language for using INSERT statements to add data to a table. But it also has bulk options for rapidly adding a lot of data to a table via the COPY command.  The same command can be used for bulk export as well.

A curious aspect of this though, is that because it’s a cloud-only database system, you can’t just use COPY to get data to/from a local file system. COPY works to/from what’s called a stage.

2019-10-04