Category Archives: Tech Notes

Microsoft Released SQL Operations Studio (preview)

Microsoft recently announced at the 2017 SQL PASS event that they would be previewing a new tool for sql server that works across multiple operating systems.

“SQL Operations Studio (preview) is a free, light-weight tool, that runs on Windows, macOS, and Linux, for managing SQL Server, Azure SQL Database, and Azure SQL Data Warehouse; wherever they’re running.”

It’s now available for preview.

Read and Download:
https://docs.microsoft.com/en-us/sql/sql-operations-studio/what-is

More info (video) at TechNet:
https://blogs.technet.microsoft.com/dataplatforminsider/2017/11/15/announcing-sql-operations-studio-for-preview/

Remove Numbers from Text SVF, Nested vs CTE

I need to remove numbers from a string while processing some data  as part of an ETL process in a data warehouse.  I have to do this for many columns and potentially many tables so I decided to put the logic into a Scalar-Valued Function (SVF), understanding there are performance detriments when using SVFs. Generally this will be used on relatively small data sets for cleaning “codes” that will be insert into a slowly changing dimension.  I was curious about how to do this.  My instinct was to do it using nested replace functions.  I did a quick google search to see what options the collective would recommend.  I came across a post on Stack Overflow that mentioned the nested replace functions, and then someone also mentioned using a recursive Common Table Expression (CTE), which I thought was a creative suggestion.

https://stackoverflow.com/questions/13240298/remove-numbers-from-string-sql-server/

I have a soft spot in my heart for recursive CTEs so I thought I’d try both options and see which solution performs better.  Let’s start with building the functions:

Now that we have some functions we need some test data so let’s create a simple table and populate it with simi-random data by leveraging the newid() function.

Continue reading Remove Numbers from Text SVF, Nested vs CTE

Backup Transaction Logs to Nul for All Online Databases

First of all, never use this in a production environment!   This script is to backup your transaction logs to a “nul device” for all online databases which are not using simple recovery model.  In windows it is indeed spelled “nul” with one “L”.  The only reason you would want to do this is if you have a non production environment using full recovery model and this server architecturally mirrors your production environment.  For example, we have a “staging” server that is used for testing our code changes before they go into production.  We require the staging environment to be as close to production as possible and have scheduled scripts that sync them weekly.  In this scenario, we have many databases in the staging server that are using full recovery model but we do not want to backup the t-logs, we would rather just throw them away.

To learn more about NUL devices, here’s a link to the wikipedia page: https://en.wikipedia.org/wiki/Null_device

Resolve SQL Server Fatal error: cannot create ‘R_TempDir’

I was trying to test an install of R in SQL Server 2016 and when running a script I received this error: Fatal error: cannot create ‘R_TempDir’

Following the instructions here, I enabled external scripts, restarted the sql server service, and then tried to run the following test script:

This is when the fatal error occurred.  As the error suggests, R is having some issues creating a temporary directory.  After some internet searching and trial and error I got past the issue.

Enable  8dot3 File Names

R configuration uses the 8dot3 file name convention, also known as “short names”.  To enable this on windows 10, run the following command in CMD (command prompt):

For more options and information look here: https://support.microsoft.com/en-us/kb/121007

Give access to the working directory to R

Locate and open “rlauncher.config” file in a text editor.  This file will be under the “<sqlserver_instance>\binn” directory.  Take a look at the location of WORKING_DIRECTORY.  This should have a “short name” file path.  The path should be something like “<sqlserver_instance>\EXTENS~1”, and “\EXTENS~1” is equivalent to “\ExtensibilityData”.  We need to give access to R to this folder. I did this by changing the permissions to full control to everyone.  You may want to be more restrictive here, but in my case this did not matter.

  • Right click folder > Properties > Security tab > Advanced > Add
  • Select a principal (I entered “Everyone”)
  • Tick “Full control” under basic permissions and click “OK”
  • Tick “Replace all child object and permissions entries with inheritable permissions entries from this object” and click “OK”

Now if you rerun the script above you should get a result of “hello, 1”.

Calculate Weighted Average in Excel using SumProduct() Function

Weighted averaged, also known as weighted arithmetic mean, is similar to an ordinary average, except that instead of each of the data points contributing equally to the final average, each data point is “weighted” and thus contributes more or less depending on the given weight.  The weight would typically be some correlated data point that indicates significance of the value being averaged.

For example, let’s say we were tracking the progress of a project and its various tasks.  Our data set includes the task number, percent completed, and estimated hours to complete the task.  We want to calculate an overall percent completed for the project based on how complete the individual tasks are.  Take a look at the example below.

weightedavg00

=AVERAGE(B2:B6)

If we calculate a straight average of the percent complete column then we get 75% completed overall.  However, this could be deceiving because some tasks will take longer to complete than others, as indicated in the estimated hours column.  Let’s use this estimated hours column as the “weight” in our weighted average.

One way to do this would be to:

  • multiply the item (% Complete) by the weight (Estimated Hours) at the row level, shown in column D below.
  • sum up all of those products in column D, and then
  • divide by the sum of the weight for all records (column C)

Formulas:
weightedavg01_formulas

Results:
weightedavg01

The result of the weighted average is 49%, which is much different than the 75% from a straight average.  This is because there are items which have a high percent completed but a low estimated hours to complete, and also items with low percent completed and higher estimated hours to complete.  By including a weight to factor in the level of effort for each item you get a much more accurate result.

What’s the problem with this approach? If the number of tasks changes then it becomes a fairly manual task to adjust the rows and formulas accordingly.  Also, I don’t like the idea of having a “helper” or “work” column inserted into the data set.  There is a quicker and simpler way to calculate the weighted average than the method I just explained.

Continue reading Calculate Weighted Average in Excel using SumProduct() Function

Collapse and Concatenate Rows

I had a need to concatenate  and comma separate some multi-row data into an array of values with each having an unknown number of elements, in other words, take a many to one parent-child relationship and collapse the many child rows into the one parent record and separate the child record values with a comma.  In the past, my default method to solve this problem was to build a temporary  table  and then use a loop to iterate through a data set and append the elements by updating rows in the temporary table, or use a common table expression with anchor and recursive members.  Recently I stumbled upon the “stuff” and “for xml” functions.  I had seen these functions  before but never took the time to understand their potential use.  These function can be used to solve the problem mentioned.

T-SQL For XML (Path Mode)

Function Description: A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in sub queries. The top-level FOR XML clause can be used only in the SELECT statement. In sub queries, FOR XML can be used in the INSERT, UPDATE, and DELETE statements. It can also be used in assignment statements.

In a FOR XML clause, you specify one of these modes: RAW, AUTO, EXPLICIT, PATH.  We will only use PATH for this exercise.

The PATH mode together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a simpler manner.

The EXPLICIT mode allows more control over the shape of the XML. You can mix attributes and elements at will in deciding the shape of the XML. It requires a specific format for the resulting rowset that is generated because of query execution. This rowset format is then mapped into XML shape. The power of EXPLICIT mode is to mix attributes and elements at will, create wrappers and nested complex properties, create space-separated values (for example, OrderID attribute may have a list of order ID values), and mixed contents.

I will not list the syntax for this function because it can get pretty complex very quickly for all of the options.  Instead, you can see in the example I just use it to concatenate the rows into a comma separated array.   Any other XML is basically ignored by passing  in the argument “Path (”)”.

(More information on For XML)

T-SQL Stuff Function

Function Description: The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Function Syntax: STUFF ( character_expression , start , length ,character_expression )

(More Information on the Stuff function)

Example Problem

List database users and include all roles of which a user is a member, separated by comma.

Solution using Stuff and For XML

  • “For XML” is used to collapse and concatenate the row data into a single array.
  • “Stuff” is used to remove the leading comma.
  • Note that we are using a correlated subquery when we reference “dp.principal_id” in order to limit our roles and role members to our database principals in the main outer query.

Output:

principal_id type_desc create_date modify_date Roles
5 SQL_USER 11/13/2002 6/19/2010 db_owner
7 SQL_USER 11/13/2002 6/19/2010 db_owner,db_datareader,db_datawriter
8 WINDOWS_USER 3/26/2009 3/26/2009 db_datareader
10 WINDOWS_USER 3/16/2008 3/16/2008 db_datareader
11 SQL_USER 7/11/2008 6/19/2010 WebViews
13 SQL_USER 7/25/2014 7/25/2014 role_proxyusers
14 WINDOWS_USER 3/12/2009 3/12/2009 db_datareader
16 SQL_USER 7/25/2014 7/25/2014 role_proxyusers
18 SQL_USER 8/26/2005 6/19/2010 db_owner
19 WINDOWS_USER 6/2/2009 6/2/2009 MSReplPAL_9_1,db_datareader
20 WINDOWS_GROUP 10/3/2005 10/3/2005 db_datareader
21 SQL_USER 7/14/2009 5/2/2016 role_proxyusers,db_datareader,db_denydatawriter

Microsoft SQL Server Developer Edition is now free

https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/

“Exciting news! Starting today, SQL Server 2014 Developer Edition is now a free download for Visual Studio Dev Essentials members (you will be prompted to sign in to Visual Studio Dev Essentials before you can download SQL Server 2014 Developer Edition). We are making this change so that all developers can leverage the capabilities that SQL Server 2014 has to offer for their data solution, and this is another step in making SQL Server more accessible. SQL Server Developer Edition is for development and testing only, and not for production environments or for use with production data.”

“SQL Server 2016 Developer Edition, when released later this year, will also be free. To learn more about the exciting new features in SQL Server 2016, read the datasheet.”

Create Read Only User in Azure DB

I have been struggling with creating a user who has read only access to an azure database and finally figured it out.  The key is connecting to the right database in a new session when executing the t-sql commands.  This is because you can not execute a “use” statement, change the database in the current session via the GUI, and you can not make named references to databases in your t-sql.

server_name.database_name.schema_name.object_name

Validate user has read only permissions by connecting to database using these new credentials and executing something like “CREATE TABLE dbo.test (test datetime);”, which should return an error message indicating permission denied.

More information on managing database access and login security in azure: https://azure.microsoft.com/en-us/documentation/articles/sql-database-manage-logins/

WiFi Range Heat Mapping

I wanted to compare WiFi coverage of two different wireless access points and their various configurations in my house, and also check for dead spots.  One quick and cheap way to do this that I have come across is using a free product called Ekahau HeatMapper.

Ekahau HeatMapper allows you to walk around your home or office and capture information about WiFi signals in various spots, and then the software aggregates this information into a banded topographical map.

Continue reading WiFi Range Heat Mapping