The Bit Bucket

In a previous post, I talked about stages. They are internal or external cloud storage locations that you can use the COPY command to copy data into database tables from or use the COPY command to export data from database tables.

Now if you are using external stages, they’re just standard storage accounts in AWS (S3), Azure Storage, or Google (GCS). You can use whatever tools you want to get files from other locations (like your local file system) to/from these accounts.

2019-10-18

Another question that I answered on a forum recently was about how to find the primary key column (or columns) for a table.

Here’s an example of the code required:

SELECT ic.index_column_id AS ColumnID, 
       c.[name] AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
    ON i.object_id = ic.object_id 
    AND i.index_id = ic.index_id 
INNER JOIN sys.columns AS c
    ON i.object_id = c.object_id
    AND ic.column_id = c.column_id
INNER JOIN sys.tables AS t
    ON t.object_id = i.object_id 
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id 
WHERE i.is_primary_key <> 0
    AND t.[name] = N'TestTable' -- table name
    AND s.[name] = N'dbo' -- schema name
ORDER BY ColumnID;

I hope that helps someone.

2019-10-17

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