A Fine Slice Of SQL Server
Bill Fellows points out an oddity of T-SQL variable declarations and scoping:
Crazy enough, the last two are correct. It seems that unlike every other language I’ve worked with, all variables are scoped to the same local scope regardless of where in the script they are defined. Demo the first
Wanna see something even more crazy? Check this version out
I should note that I wanted a submit button.
Comments closedEmmanouil Gkatziouras shows that you can run a Hadoop job written in Scala on Azure’s HDInsight:
Previously, we set up a Scala application in order to execute a simple word count on Hadoop.
What comes next is uploading our application to HDInsight. So, we shall proceed in creating a Hadoop cluster on HDInsight.
Read the whole thing, but the upshot is that Scala apps build jar files just like Java would, so there’s nothing special about running them.
Comments closedDerik Hammer explains what Azure SQL Database Elastic Pools do:
Azure SQL Database Elastic Pools are a mechanism for grouping your Azure SQL Databases together into a shared resource pool. Imagine for a moment that you had a physical server on premise. On that server, you have a single SQL Server instance and a single database. This example is similar to how Azure SQL Database works. You have a fixed amount of resources and you pay for those resources, even when you are not using them.
An Elastic Pool is analogous to that same server and instance, instead you add several databases to the instance. The databases will share the same resource pool which can be cheaper than paying for separate sets of resources, as long as your databases’ peak usage times do not align with each other.
Read on to see how you can potentially save money on databases using an elastic pool instead of spinning up the databases independently.
Comments closedDave Bermingham explains what Azure Managed Disks are and why you might want to use them:
What’s Managed Disks you ask? Well, just on February 8th Corey Sanders announced the GA of Managed Disks. You can read all about Managed Disks here. https://azure.microsoft.com/en-us/services/managed-disks/
The reason why Managed Disks would have helped in this outage is that by leveraging an Availability Set combined with Managed Disks you ensure that each of the instances in your Availability Set are connected to a different “Storage scale unit”. So in this particular case, only one of your cluster nodes would have failed, leaving the remaining nodes to take over the workload.
Prior to Managed Disks being available (anything deployed before 2/8/2016), there was no way to ensure that the storage attached to your servers resided on different Storage scale units. Sure, you could use different storage accounts for each instances, but in reality that did not guarantee that those Storage Accounts provisioned storage on different Storage scale units.
Read on for more details.
Comments closedNow the jobs are not going to run as they are as they have CmdExec steps and this is not supported in SQL on Linux so we have to make some changes to the steps. As I blogged previously, this is really easy using PowerShell
First we need to grab the jobs into a variable. We will use Get-SQLAgentJobHistory from the sqlserver module which you need to download SSMS 2016 or later to get. You can get it from https://sqlps.io/dl As we are targeting a Linux SQL Server we will use SQL authentication which we will provide via Get-Credential and then take a look at the jobs
It’s not “point, click, done,” but Rob shows you certainly can do it.
Comments closed17. The report appears but does not execute because the parameters are not set. Why not?
Well, after inspecting the URL (http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fHigher+Education+Solution%2fReports%2fAnnual+Enrollment+Details&DateSchoolYear=2007&Term=Spring), it passed the values as expected. What is the problem? Remember, the parameters are populated from and SSAS model, so that means we need to send the values formatted as such. This format is:
[TableName].[Attribute].&[Value]
No problem, just build that string as part of the URL. Guess what, that doesn’t work either. What you need to do encode certain characters in the URL. For example, to pass year it needs to look like this [Date].[School Year].&[{{SelectionList.SelectedItem}}].
Click through for a step-by-step guide.
Comments closedEwald Cress explains thread-local storage and its relationship with SQL Server workers:
So far, this is all rather abstract, and we’re just seeing numbers which may or may not be pointers, pointing to heaven knows what. Let me finish off by illuminating one trail to something we can relate to.
Out of the five local storage slots which contain something, the first one here points to 00000000`3b656040. As it turns out, this is an instance of the CCompExecCtxtBasic class, and you’ll just have to take my word for it today. Anyway, we’re hunting it for its meat, rather than for its name. Have some:
Click through for details, including a graphic.
Comments closedBy clicking on the “R transformation” a new windows will show up. This windows is a R editor that you can past your code here. however there are couple of things that you should consider.
1. there is a error message handling but always recommended to run and be sure your code work in R studio first (in our example we already tested it in Part 1).
2. the all data is holding in variable “dataset”.
3. you do not need to write “install.packages” to get packages here, but you should first install required packages into your R editor and here just call “library(package name)”
Leila takes this step-by-step, leading to a Power BI visual with drill-down.
Comments closedKevin Hill makes a great point:
A stored procedure that, out of the box, is disabled and has no explicit rights granted (or denied) is locked down to everyone but those in the sysadmin server role.
If someone exploits your SQL Server via xp_cmdshell, its because you LET them, either by granting permissions or by putting someone in sysadmin that clearly should not have been there.
For this in more detail, check out Sean McCown’s post from 2015.
Comments closedI have a very simple query. It’s running against a table with a nonclustered columnstore index.
SELECT COUNT(*) FROM pt.FirstNameByBirthDate_1966_2015;
GO
The query returns one row, as expected. Here’s my count:
For the record, that is the correct number of rows in the table. Here’s where things get weird. In the actual execution plan, the columnstore index returns zero rows.
Yes, this is really the actual execution plan. I’m not tricking you, I promise.
Click through for the answer.
Comments closed