SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

The Voice of the DBA

Scary Data Collection

Most of us would feel fairly creeped out by finding out an AirBnb or hotel had security cameras watching us. I’m not a woman, and I’m sure ladies are especially bothered by this, but there was an AirBnB rental where a guest found a camera using a little technology scanning. While cameras are allowed, they have to be disclosed.

While many of us would prefer not to be surveilled, we are on a regular basis. Governments are watching our vehicles, all commercial activity is tracked in multiple ways, our locations are captured and sold to anyone. And it’s not even the carriers, it could be software that we think is innocuous and helpful. I would think most people reading this know that everything you do online is tracked, and often tracked from site to site with Facebook, Google, and other APIs, even if you don’t use those companies’ services. What’s disconcerting to me is how extensive data gathering and tracking has become and most people aren’t aware how comprehensive it has become.

And in a wonderful set of timing. As I was writing this, I got a great article about how Google apparently isn’t perceived as invading privacy to the extent that they are. We likely trust them more than we should.

The capture and misuse of data continues to grow. Whether this is by criminals, governments, or commercial businesses, it’s something we have to deal with. This isn’t necessarily any particular organization or situation that stands out, though the larger organizations likely have an out-sized impact and benefit from this. This is one of the reasons why the GDPR and similar legislation was passed. It’s a first attempt, and arguably weak attempt, to limit the use of data by organizations in ways that might be contrary to the wishes of the human that generated the data.

Personally I like the GDPR, and while it might need alteration over time, it does start to to examine the idea that humans ought to be in control of data about them, just as we are often in (some) control of many of the physical items in the world we own. There are rules and regulations, restrictions, and even legal processes that provide recourse over our possessions. Those ought to be extended, and certainly adapted, to digital data, with the corresponding rights that we currently have and perhaps even new ones.

I think this is going to impact our jobs as data professionals in the future. While we will have more requirements, more hassles from security, and more restrictions, this is also going to ensure that organizations need data professionals for a long time.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Featured Contents

A Quick Start to Running Python Code in SQL Server

EmmittAlbright from SQLServerCentral.com

With the addition of Python to Machine Learning Services in SQL Server 2017, you can now execute your Python code inside of SQL Server.

Parameter Sniffing in SQL Server 2019: Adaptive Memory Grants

Additional Articles from Brent Ozar Unlimited Blog

This week, Brent is demoing SQL Server 2019 features that he’s really excited about, and they all center around a theme we all know and love: parameter sniffing.

Refactoring a Batch or Object with SQL Prompt

Additional Articles from Redgate

Louis Davidson reveals some useful Prompt features for refactoring individual code blocks or modules during development, which will improve code quality, reduce tedium, make testing simpler, or sometimes all three.

Using the SQL Monitor Estate Pages

Additional Articles from Redgate

Kathi Kellenberger introduces the new Estate monitoring pages in SQL Monitor 9, and explains how they will help DBAs monitor and safeguard the security, stability and availability of all servers in their care.

From the SQL Server Central Blogs - What Chocolatey Packages Does a Business Intelligence Developer Need?

Aaron Nelson from SQLServerCentral

I’m getting a new work laptop next week: Hooray!! I’m going to have to install A LOT of software: Opportunity! Problem: I have a lot of meetings next week,…

From the SQL Server Central Blogs - MAXDOP, Parallelism and the Cloud

matthew.mcgiffen 73574 from SQLServerCentral

Parallelism and MAXDOP The pros and cons of parallelism have always been with us in SQL Server and I blogged about this a couple of years ago. This is…

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Get the Datetime
What does this code do in SQL Server 2017? DECLARE @MyDate DATE = '2015-08-27' , @MyTime TIME = '15:33:21.057'; SELECT MyDateTime1 = CAST(@MyDate AS DATETIME) + CAST(@MyTime AS DATETIME);
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Getting the next value

I have written this function in Python:

def getn(n):
    val = 0
    while val < n:
        yield val
        val += 1

If I call this:

x = getn(100)

I do not get a result. How do I get the value from my function?

Answer: next(x)

Explanation: This is a generator function. It is used as an iterator, so either an iterable function is needed, or the next() function will return the value. Ref: Generators - https://wiki.python.org/moin/Generators

Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

Status Update 17 April 2019 - Some progress here, with less dev resources because of a few other internal fires. Forums – quoting posts should look better now, though a fix doesn’t help with code. I’d ask that you stop quoting the code, or at least delete the code when your reply is posted for now. Authors and newsletters linked in […]

Always on - Has anyone has configured always on from on premises to Azure cloud? If yes then have you ever came into any issues with that setup? Were there any pros and cons? Thanks

SQL Server Agent scheduled job taking abnormally long time to run - I am having a quite strange problem- the scheduled job in SQL Server Agent is taking abnormally long time to run. However the same job when I right click on the job and select “Start job at step…”, it finishes at desired time. Usually the job should be finished in about 15 to 17 minutes […]

HA: Reporting workload blocks redo threads due to IO contention - https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/troubleshoot-primary-changes-not-reflected-on-secondary?view=sql-server-2017#BKMK_REDOBLOCK We have HA setup and we are finding that the redo thread falls behind. This tends to happen when there are large reporting workloads run against our secondary synchronous node. Are there any options besides just “run fewer queries against the other node”? If it was blocking we could address […]

SSRS Multi-Select parameter weridness - i’m not an SSRS guru but in the past the behavior of making a parameter multi-select would pass a comma delimited string. ie. selecting red and blue and green would give you ‘red,blue,green’ causing one to have to use some form of string split. However, I am working with a report that is passing the […]

How to sum a number of rows with minute data where column format is datetime - I’m working with a database where a column that includes duration in terms of minutes is stored as datetime. The data looks like this. Note the ‘1899-12-30’ info can be effectively ignored. Duration 1899-12-30 00:56:33.000 1899-12-30 00:26:27.000 1899-12-30 01:04:02.000 1899-12-30 00:13:30.000 1899-12-30 01:10:06.000 1899-12-30 00:23:02.000 1899-12-30 00:00:06.000 I am trying to do something like […]

Missing attachments - I remember there was an attachment to the following post (I’ve even got a post on this thread saying that I looked at it) and I can’t find it on the post anymore. https://www.sqlservercentral.com/forums/topic/insert-to-clustered-index Also, it still appears that attachments to articles are not available. Any idea when this problem will be fixed?

Unexpexcted behaviour STGeomFromText - Hey guys, I’ve got a weird behavior of SQL-Server regarding spatial objects here. The code from below defines a postal code area in Frankfurt, Germany as a linestring. I want to transform this to a polygon, to check if some points are liying within this area. But the resulting polygon is way bigger, than the […]

Viewing database diagrams - Hi all We’ve designed some database diagrams on our DEV server (we have SA access). We want our analysts to be able to see these diagrams but we don’t want them to have db_owner access (even though it’s a DEV server). What I’ve done so far it to create a role within the database […]

On premises migration to Azure Managed Instance – link AAD login to db user - I am migrating an on premises SQL Server to Azure Managed Instance however having problems with changing the existing Windows groups database users to be Azure AD groups. I can manually add them by dropping users and recreating them but worried this will miss out some permissions as they are quite complex. Reading an article […]

Create a calculated member in a tabular model - Hello All, I am trying to add a calculated member in a dimension. In Multidimensional model, I do that like this: CREATE MEMBER [Material].[Department].[AB] ASAGGREGATE({[Material].[Department].&[A], [Material].[Department].&}) Is there a way to do the same in tabular model ?

Changing table data - Hi, I have a row in a table call ‘Time Reported’ with 29602 rows the data for the table looks like the below: 1899-12-30 09:12:32.000 each row has a different time but keeps the 1899-12-30 & .000 at the end. I beleive it is formatted like this because it was pulled from a old Access […]

Changing table data - Hi, I have a table name ‘Time Reported’ and the format of the data is : 1899-12-30 09:12:32.000 There are 29602 rows of data, this was imported from a old Access database i would like to run a SQL query to remove the 1899-12-30 & the .000 and leave the 09:12:32 Can anyone assist with […]

Alternative for row_number() over (partition by Department order by Salary desc) - Hi, I am looking for the alternative code for “row_number() over (partition by <> order by <> desc)” using join conditions. Can any one help me to on this part?

Excel Files Randomly Failing - Hello, I have a for each loop container that obtains the data from a SQL database and outputs it in different Excel files based on the condition in the loop. Every time I run the package it randomly produces x amount of Excel files then fails with the error “Cannot Acquire Connection From Connection Manager” […]

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.