A Fine Slice Of SQL Server
Devin Knight continues his Power BI custom visuals series:
In addition, to seeing where the high and low distribution of values are you will also find the Box and Whisker will provide:
A count of values
Maximum
Minimum
Average
Median
Range of values
A simple box and whisker chart goes a long way.
Comments closedAndrew Pruski digs into how to set up log shipping for SQL Server on Linux:
What I’m going to do is setup two instances of SQL Server running on linux and log ship one database from one to another. So the first thing I did was get two VMs running Ubuntu 16.04.1 LTS which can be download from here.
Once both servers were setup (remember to enable ssh) I then went about getting SQL setup, I’m not going to go through the install in this post as the process is documented fully here. Don’t forget to also install the SQL Tools, full guide is here.
Read on for the guide, but also be sure to read his disclaimer.
Comments closedNeil Gelder gives an introduction to JSON in SQL Server 2016:
A new feature in SQL Server 2016 (also available in Azure SQL database) is the ability to create and query JSON (Javascript object notation) documents, which have now become a common alternative to XML.
Lets look at some examples, I’ll be using tables from the new sample database for SQL Server 2016 WorldWideImporters which you can download from this link
I’m of two minds with JSON support: I think it’s very useful for building output sets for service calls and might be fine for inputs when you can’t use a table-valued parameter for some reason, but if you’re doing a lot of JSON splitting of data in a table, that’s a violation of first normal form.
Comments closedKenneth Fisher shows how to create a Microsoft Connect entry:
I recently wrote a blog about how to tell why your SQL login isn’t working. There were a lot of good comments and several of them suggested that I create a connect entry to make the error easier to understand. There was also a question of how to create a connect entry, and it wasn’t the first time I’d heard that, this week. So I’m going to give a quick demo on how to create a connect entry. I’m not going to create it using that particular login error because, as another person pointed out, this error is actually by design. We don’t want to make it easier for someone trying to hack in right?
There are also feedback forums for Power BI and Azure.
Comments closedThe main issues for these applications were caused by trying to run a development system’s code, tested on AWS instances on a physical, on-premise cluster running on real data. The original developer was never given access to the production cluster or the real data.
Apache Ignite was a huge source of problems, principally because it is such a new project that nobody had any real experience with it and also because it is not a very mature project yet.
I found this article fascinating, particularly because the answer was a lot more than just “throw some more hardware at the problem.”
Comments closedAndy Leonard has a detailed post on setting up SSIS catalog environments and references:
Why This Complexity?
My argument for embracing (or ignoring) the complexity of SSIS Catalog configuration is straightforward: Externalization – the act of storing execution-time values outside of the object to be executed – is one way to achieve portability, a software development best practice.
As an architect I loathe complexity. Whenever I encounter (or opt to develop) complexity in a software project, I question it. I ask others to question it. I don’t always arrive at the right conclusion, but I challenge complexity at every turn.
I’ve helped several organizations implement SSIS Catalog portability using Environments and References. We all agree that the solution is complex, but most also agree that the complexity is worth the flexibility achieved thereby.
This is a fairly lengthy blog post, full of good information.
Comments closedMadhivanan JR shows two methods for extracting the decimal value of a number:
One of the questions asked in a forum. “What are the different methods to extract decimal part from a number?”.
There can be many methods. Two simple methods are as shown below
Click through for the two methods. I knew method #1 but didn’t think about method #2 because I’ve always thought of the modulo with respect to integers, but it makes sense.
Comments closedSteve Jones has to install dbatools without the benefit of Powershell 5.0:
I have a number of Windows Server 2012 R2 VMs. I need to get rid of these and upgrade to WS2016, since installing SQL 2016 is a pain on these VMs. However, for the time being, I’m stuck with them.
I was looking to try some of the dbatools modules. I popped open an elevated command prompt and [error]
This is because that module isn’t in this version of PoSh. I checked, and I could see this is v4, and Install-Module was added in v5.
Click through for the solution.
Comments closedAngela Henry shows how to exclude entire sections of a schema comparison in Visual Studio:
When using VS for database projects I typically use my environment specific Publish xml file to deploy changes to my local database when experimenting with code changes. However, every once in a while I will have to use the New Schema Compare tool from the Tools | SQL Server menu when I have a “one-off” database that I need to synch to my database project.
Quite frequently because these are one-off databases there will be a ton of junk items that are in the database, but not in my database project or lots of objects in my project that aren’t in my database. Either way I want to ignore those changes. In the past, I’ve always manually unchecked each item, tedious when you have more than two items to uncheck.
Click through for more details.
Comments closedNate Johnson extends the nested sets model to include a depth attribute:
Depth is pretty simple to add if you’ve already got a tree full of data. We can use a recursive common table expression, or “rCTE“. While normally these are frown-worthy (remember, recursion is not SQL’s strong suite), we’re only using it one time to populate an existing data-set, so we can keep on smiling.
Hierarchies in SQL are an important but not well understood topic.
1 Comment