SQL Server Archives - Tomas Lind

Microsoft SQL Server

Dynamic Data Masking (DDM) – Mask query results

Post author:Tomas Lind
Post published:2017/12/30
Post category:Security
Post comments:1 Comment

Enforcement of the new GDPR will begin in May 2018. One of the cornerstones in GDPR is that an organization must be able to protect sensitive personal information. This can be achieved in many ways and at many different levels, from infrastructure (e.g. monitoring traffic) to protecting individual database records. To protect individual data records, some different techniques are available. Examples are encryption and masking. Masking can be done in a permanent way (e.g. actually replacing a name with random characters), or it can be done dynamically (not replacing the original data, but to mask information at query time). SQL Server 2016 ships with a capability to do this dynamic data masking, DDM, and in this article I’ll cover the basics.

(more…)

Microsoft SQL Server

The T-SQL Merge statement

Post author:Tomas Lind
Post published:2017/07/18
Post category:Excel T-SQL
Post comments:5 Comments

You have a table A with new data, and a table B with old data. How do you make sure table B is refreshed with data from table A? You want to insert new records, update existing records and to delete removed records.

The T-SQL merge statement can do all that for you.

(more…)

windows

From SQL Server to MSMQ using PowerShell

Post author:Tomas Lind
Post published:2017/07/14
Post category:MSMQ PowerShell SQL Agent
Post comments:3 Comments

How do you get records from a SQL Server table into a MSMQ queue as separate XML messages?

A commonly suggested way to communicate with MSMQ from within SQL Server is to create a CLR assembly. See for instance this article that has a great coverage of how to use CLR in this scenario. Note however that you have to import the System.Messaging.dll as unsafe for this to work. And you have to enable CLR obviously.

If this is not an option, another way to do it is to use PowerShell. Which is what I will cover in this demonstration.

(more…)

Microsoft SQL Server

Identity columns that are about to overflow

Post author:Tomas Lind
Post published:2016/12/08
Post category:T-SQL Tips & Tricks
Post comments:0 Comments

Identity columns are widely used as primary key columns in SQL Server tables. They can be one of the data types TINYINT, SMALLINT, INT, BIGINT, NUMERIC or DECIMAL. Each of these data types has an upper limit and when that upper limit is reached, an error is raised and further inserts into the table are impossible.

(more…)

Microsoft SQL Server

Temporal Tables Introduction

Post author:Tomas Lind
Post published:2016/12/06
Post category:Temporal Tables
Post comments:0 Comments

Temporal tables was introduced in SQL Server 2016, and is a technique that simplifies keeping track of changed records in a table. The handling of changes of records in a data warehouse dimension table is a good example of when temporal tables can be useful. In this blog post, I’ll cover the basics of temporal tables.

(more…)

Microsoft SQL Server

SQL Agent job logging with tokens

Post author:Tomas Lind
Post published:2016/11/04
Post category:SQL Agent
Post comments:5 Comments

A useful feature in the SQL Agent is the possibility to use tokens. Tokens are a number of system variables (e.g. $(ESCAPE_SQUOTE(DATE) that contains the current date) that gets expanded/replaced with a value when the job is run. An obvious use of tokens is to create a new log file with today’s date and time in the file name each time an SQL Agent job is run.

(more…)

Toggl Time entries to SQL Server

Post author:Tomas Lind
Post published:2016/06/14
Post category:PowerShell Tips & Tricks
Post comments:1 Comment

Toggl is a very simple to use cloud based time tracking application. Time entries on projects and clients can be submitted through the web page or through mobile and desktop applications. Time spent by the team can be viewed in the different reports that can be accessed via their web page. There is a free version that handles much of the time tracking needs, and there are more advanced paid versions as well.

If you want to integrate the information stored at Toggl with your own enviromnent, you can use their API. One way is to download time entries to your own SQL Server database, which is the method I’ll present here.

(more…)

Split strings to columns

Post author:Tomas Lind
Post published:2016/05/25
Post category:T-SQL Tips & Tricks
Post comments:0 Comments

A short demo on how to split strings with delimiters into columns.

(more…)

SSISDB.catalog.create_execution

Post author:Tomas Lind
Post published:2015/12/07
Post category:SSIS
Post comments:3 Comments

The SSISDB Catalog was introduced in SQL Server 2014. With it came a lot of procedures to manage deployed SSIS projects. The procedures cover running, configuring, managing and troubleshooting SSIS packages.

In this post I will cover a procedure that can be used to run SSIS packages. The procedure is SSISDB.catalog.create_execution.

(more…)

Microsoft SQL Server

SQL Server recovery time

Post author:Tomas Lind
Post published:2015/10/27
Post category:Error log Failover Cluster System Stored Procedures
Post comments:0 Comments

SQL Server Recovery time is the time it takes for SQL Server to rollback or roll-forward transaction not yet persisted to the database after a service restart. Transactions that are uncommitted are rolled back, and transactions that are committed but not yet persisted are rolled forward.

Depending on the volume of transactions that needs to be handled during recovery, recovery may take a long time to perform. This affects the time it takes for a SQL Server restart. Another example when recovery times become a factor is when a failover is done in a SQL Server cluster.

So how can we know how long the recovery time will be?

(more…)