Sql-Server

I wrote recently about menu items being missing from SSIS and other BI projects in VS2019, but another thing that’s gone MIA is the Integration Services Connector for Power Query.

This is the connector that allows you to use Power Query (and the M language) to input data into SSIS.

The SSIS designer for VS2019 recently went GA (general availability). It’s great that it’s released, but if you install it, you’ll find it removes the Power Query option for SSIS.

2020-01-01

If you’ve recently installed Visual Studio 2019 (VS2019), and then installed the SQL Server Reporting Services (SSRS), the SQL Server Analysis Services (SSAS), and/or SQL Server Integration Services (SSIS) extensions, when you open a project you might be wondering where your menus went.

How it was

For example, if you had a report open in the VS2017 report designer, this menu was present:

And if you had an AS model open in the VS2017 tabular designer, these menus were present:

2020-01-01

As part of our free SDU Tools for developers and DBAs, we have many scripting functions. SQL Server allows you to create your own database roles, and generally, you should do this instead of using the fixed database roles like db_owner, or db_datareader and db_datawriter, as it lets you allocate just the required permissions. To allow scripting out the permissions assigned to these roles, we’ve added a tool called ScriptUserDefinedDatabaseRolePermissions.

2020-01-01

It’s useful to be able to find one string within another string. T-SQL has two basic functions for that: CHARINDEX and PATINDEX.

CHARINDEX is used to see if one string appears within another string. Take a look at this code:

In this first example, I asked SQL Server to look for the string lot in the string Hellothere. You can see that it found it at position 4.

2019-12-30

I’ve had a lot of great feedback on my series of blog posts about Snowflake, and explaining the concepts for an existing SQL Server audience.

Just out for Christmas is a new free eBook for our SDU Insiders. I’m preparing a three part series on Snowflake for SQL Server users:

Part 1: Core Concepts (available now)
Part 2: SQL Language (coming soon)
Part 3: Administration (sometime after that :-))

You can get part one now! And did I mention free? (for SDU Insiders)

2019-12-27

One issue that comes up time and again in the forums is when an INSERT statement fails with a violation of a UNIQUE or PRIMARY KEY constraint but when the user checks the existing table, the value that’s being complained about isn’t already in the table.

The Symptom

For example, a table might have primary key values of 2, 12, and 14.

When an INSERT is performed, there is an error telling you that you can’t insert a duplicate key value of say 15.

2019-12-26

REPLICATE is an easy function to work with. It takes a string and a number of times you want it repeated. Here’s an example:

In this case, I’ve asked it to replicate (i.e. duplicate many times) an A character. I’ve asked for 5 of them. I put an X at each end so you could see the effect more clearly.

There are many things this can be used for, but for example, if I wanted to draw a line with 100 dashes, I could just ask for REPLICATE(’-’, 100).

2019-12-23

As with most products today, Snowflake has substantial handling of encryption to protect client data.

All editions claim to provide “Always-on enterprise grade encryption in transit and at rest”.

In Transit

First, for connections, HTTPS and TLS 1.2 are used throughout the system.

If the customer is using external stages (holding locations in cloud-storage accounts), the data can be encrypted while stored in the stage, and then travel encrypted into Snowflake. To do that, you create an “encrypted stage” where you tell Snowflake the master encryption key (i.e. the client side key) when creating the stage:

2019-12-20

As part of our free SDU Tools for developers and DBAs, we have many scripting functions. SQL Server allows you to create your own server roles, and generally, you should do this instead of using the fixed server roles, as it lets you allocate just the required permissions. To allow scripting the permissions that have been assigned to the roles, we’ve added a tool called ScriptUserDefinedServerRolePermissions.

It’s a function and doesn’t require any parameters.

2019-12-18

It’s that time of year where people give each other presents. Why not give yourself a present and learn something new while you’ve got a chance?

Our latest course release is: SQL Server Integration Services for Developers and DBAs.

You’ll find it, and our other courses here now:

https://training.sqldownunder.com/