Press "Enter" to skip to content

Day: November 14, 2016

Network Shares And SQL Server R Services

Published 2016-11-14 by Kevin Feasel

John Pertell runs into an issue reading a file on a network share using SQL Server R Services:

What I’m trying to do should be pretty easy. I want to use R code inside a stored procedure to read all the log files created by my weather program and store the results in a database. I also want to read the current monthly file on a regular basis, at least once a day. Once the data is in the database I’ll create some mobile reports with data and charts I can read on my phone. I’ll also be able to use my own data to play with local weather predicting. I thought it would make a pretty cool demo for my R sessions.

However when I run my stored procedure to read the logs I receive an error that there is no such file if I map the share as a drive, or I’m using an invalid parameter if I try to access the share directly.

Read on for code and the specific error message.

Comments closed

Filtered Indexes

Published 2016-11-14 by Kevin Feasel

Kendra Little explains the two types of filtered indexes:

These two filtered indexes are very different – and the SQL Server optimizer can use them very differently!

While classic filtered nonclustered rowstore indexes must reliably “cover” parts of the query to be used to the optimizer, filtered nonclustered columnstore indexes may be combined with other indexes to produce a plan returning a larger range of data.

This sounds a little weird. I’ll show you what I mean using the WideWorldImporters database.

Kendra exposes an interesting difference in the two types of index and a case where filtered indexes simply fail  (though that’s not a situation you want to be in anyhow!).

Comments closed

Installing Zeppelin On Windows 10

Published 2016-11-14 by Kevin Feasel

Paul Hernandez shows how to install Apache Zeppelin on Windows 10:

There are several settings you can adjust. Basically, there are two main files in the ZEPPELIN_DIR\conf :

  • zeppelin-env
  • zeppelin-site.xml

In the first one you can configure some interpreter settings. In the second more aspects related to the Website, like for instance, the Zeppelin server port (I am using the 8080 but most probably yours is already used by another application)

This is a very clear walkthrough.  Jupyter is still easier to install, but Paul’s blog post lowers that Zeppelin installation learning curve.

Comments closed

Polybase With Compression

Published 2016-11-14 by Kevin Feasel

I have a post looking at Polybase support for different compression formats:

This is a very interesting set of results.  First, 7Zip archived files do not work with the default encoding.  I’m not particularly surprised by this result, as 7Zip support is relatively scarce across the board and it’s a niche file format (though a very efficient format).

The next failure case is tar.  Tar is a weird case because it missed the first row in the file but was able to collect the remaining 776 records.  Same goes for .tar.gz.  I unpackaged the .tar file and the constituent SecondBasemen.csv file did in fact have all 777 records, so it’s something weird about the codec.

Stick to BZip2 and GZip if you’re using flat files.

Comments closed

Flow From Power BI

Published 2016-11-14 by Kevin Feasel

Chris Webb uses Microsoft Flow’s REST API to pass messages via Power BI:

So basically, in this case I’ve used Flow to create a web service without writing a single line of code. I can see a lot of potential uses for this and I suspect I’ll be blogging about Flow a lot in the future. A word of warning though: do not try to use this as a way of updating a data source. As I mentioned last time, when you run your query you’ll find Power Query/Power BI calls the web service twice. For example, I created a Flow similar to the one above that used the Insert Row step to take text sent to a Request trigger and add it to a table in an Excel workbook, and of course every time I refreshed my query I got two identical rows in my Excel table.

Read the whole thing.

Comments closed

Building Your 2016 VM

Published 2016-11-14 by Kevin Feasel

James Serra looks at putting together a VM for 2016:

With Windows Server 2016 just been released, now is the perfect time to build an Azure VM with SQL Server 2016 on Windows Server 2016.  In a matter of minutes you can be playing and learning both platforms.  Below I will document the steps I took to build the VM along with the additional software I installed.  This is a fully-loaded VM that I use for demo’s and to build small projects:

(Software updates as of 11/4/2016)

There’s a lot of good software here.  And Java.

Comments closed

Considerations For Backups

Published 2016-11-14 by Kevin Feasel

Kevin Hill goes back to basics on backups:

Backing up SQL Server databases has been documented in thousands of books, Microsoft documentation and blog posts since the product was released in the 90s.

I’m not about to try and tell you HOW to backup your SQL databases. Rather, I want to take you through the basic considerations of deciding what to back up, when, how often, etc. in clear language that non-SQL folks can work through.

Understanding how to take and tune backups is important; this is more fundamental advice.

Comments closed

Making Cortana Work

Published 2016-11-14 by Kevin Feasel

Rob Sewell shows how to integrate Cortana with dbareports via Power BI:

When you start using Cortana to query your data you will find that at times it is very frustrating. My wife was in fits of giggles listening to me trying to record the video below as Cortana refused to understand that I was saying “servers” and repeatedly searched Bing for “service” Whilst you can negate the effect by using the alternate names for the Q and A settings it is still a bit hit and miss at times.

This is an interesting post.

Comments closed