The Bit Bucket

Opinion: If you don't like answering questions, leave the forums

Over the years, I have long periods where I avoid Q&A forums. Lately, I’ve been spending a bit of time back in some forums. And once again, I’ve seen the sorts of behavior that make me think about leaving again.

Here’s a simple message: If you don’t like answering questions in Q&A forums, then don’t. Leave. You might think it’s all about you and your attempts to gain reputation in the forums. It’s not.

2019-10-29

T-SQL 101: 41 Using string literals in SQL Server

When we write string literal values, we need to surround them in single quotes. There are options in some SQL products we could make that double quotes. In general though, don’t do that. Just use single quotes.

If you have multi byte characters, they need to be preceded by the N prefix. I’ve previously mentioned that it stands for National Character Set. If I don’t use the N, when I execute the second query, I’d see this:

2019-10-28

Snowflake for SQL Server Users - Part 12 - Parallelism when loading data from files into tables

When you are loading data into Snowflake, it’s really important to achieve the maximum parallelism that you can. You want as many files loading in parallel as you have. I mentioned in earlier posts that the number of servers that you have, and the size of each of those servers, will determine the number of processor threads that are available to you from your virtual warehouses.

It would be pointless to have 32 processor threads waiting to load your data, and you provide Snowflake with one large file to load.

2019-10-25

SQL: Accessing SQL Server Express localdb from another computer

I saw a discussion on a forum the other day about whether or not you could access the localdb version of SQL Server Express from another computer. Lots of people were saying it’s only a local tool and you can’t access it from elsewhere.

But that’s not true.

You actually can connect to a localdb instance from other machines. It’s a perfectly valid configuration. (Although, if you’re really running it like a service, you’d be better off with SQL Server Express Edition instead).

2019-10-24

SDU Tools: List user access to Reporting Services

Continuing my recent theme of running queries against the SQL Server Reporting Services catalog, I often need to list which users have access to the service.

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

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-23

Opinion: Non-responsive contact forms are worse than none at all

Marketing folk have a few common truisms. One is that it costs way less to keep a customer than it is to find a new one. I have no doubt that’s true. It’s important to keep existing customers. And it costs a lot to get new ones.

For many people now, your website will be the first point of contact. By the time that someone visits your website though, a lot of things often have already had to go right. So it’s really important to keep them once they get there. Why waste all that?

2019-10-22

T-SQL 101: 40 String data types in SQL Server

You’ll notice when we discuss strings that there are many data types compared to what we had with numbers:

char was the original data character data type. By fixed length, I mean that if it’s defined as 10 characters long, it always returns 10 characters, even if there are only 5 characters stored in the string.

Prior to SQL Server 2019, char could only be used with ANSI characters (i.e. single byte characters). From 2019 onwards, char can also be used to store multi-byte characters by using UTF-8 encoding. I’ll write more about that another day.

2019-10-21

Snowflake for SQL Server Users - Part 11 - GET, PUT and SnowSQL for working with local files

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

SQL: How to find primary key column names in SQL Server

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

SDU Tools: List content items in the SQL Server Reporting Services catalog

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