SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Idempotent DDL Scripts That Always Achieve The Same Result - Making Changes Only Once: Stairway to Exploring Database Metadata Level 6
Advanced Performance Tuning for Microsoft SQL Server
Free eBook: Understanding SQL Server Concurrency
From the SQL Server Central Blogs - SSMS 18.5 Fixes from Redgate
From the SQL Server Central Blogs - A Smattering of Thoughts About Applying Site Reliability Engineering principles

Question of the Day

Reading a Datafile

The Voice of the DBA

BYOD Fingerprint Concerns

Many modern devices, whether modbile phones, tablets, or laptops, contain fingerprint readers. We've had readers for a long time, but they were very expensive and not widely deployed. That changed with mobile phones and now many devices include fingerprint readers as standard equipment. So much so that this see a very common way of authenticating access on lots of devices.

This isn't necessarily a very secure way of protecting data. An ArsTechnica article notes that fake fingerprints were able to access these devices 80% of the time. That's a success rate that might worry a lot of security personnel. Most organizations allow some sort of BYOD device, including many financial, medical, and other organizations that deal with sensitive data. Since control of a device often includes saved credentials and the ability to approve 2FA implementations, this could be an issue.

Certainly nation states might try to take advantage of this to gain control of a device and access data, but I'm sure corporate espionage is in play here as well. I wouldn't be surprised if this also becomes a technique for pranksters and jokers. Imagine you can bypass the fingerprint on a colleague's phone. Maybe you want to change their wallpaper. Maybe you want to send an embarrassing email to friends. Maybe you want to add a backdoor to some code using their GitHub credentials. You could do anything from an innocuous joke to a malicious career threatening action. Imagine you choose to do this while your colleague is at lunch, with their phone on their desk.

No security is perfect. Many of us that work with someone often might be able to guess a PIN or pattern on a device if we see it often enough. We certainly could easily put a keylogger on a wired keyboard at someone's desk. There are plenty of vulnerabilities, and in this age of being highly connected through our devices, there are potential issues with all access. There's no perfect solution, but we should be diligent with physical control of our devices and react swiftly if we think one might be stolen. Remote disable and a good backup might be top of my list.

Steve Jones - SSC Editor

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

Featured Contents

Idempotent DDL Scripts That Always Achieve The Same Result - Making Changes Only Once: Stairway to Exploring Database Metadata Level 6

Phil Factor from SQLServerCentral.com

Idempotence is the property of certain operations in mathematics and computer science, that can be applied multiple times without changing the result beyond the first time they are run. When you are making changes to a database to deploy a new version, you need to be sure that certain changes aren’t made twice, or in the wrong order.

Advanced Performance Tuning for Microsoft SQL Server

Additional Articles from MSSQLTips.com

No matter where you run your SQL Server® instances—on-premises, in a virtual machine, or in an Azure SQL Database Managed instance—the challenge is the same. How do you ensure you get optimum performance? Is the virtual machine impacting your database instance performance? Which queries cause the most blocking or deadlocking? Are you consuming more DTUs in your Azure SQL Database than you budgeted for?

Free eBook: Understanding SQL Server Concurrency

Press Release from Redgate

When you can’t get to your data because another application has it locked, a thorough knowledge of SQL Server concurrency will give you the confidence to decide what to do.

From the SQL Server Central Blogs - SSMS 18.5 Fixes from Redgate

Steve Jones - SSC Editor from The Voice of the DBA

I wrote yesterday about the issues with SSMS 18.5 and some of the add-ins from Redgate. I posted the release of the SQL Prompt fix (here: ). The teams...

From the SQL Server Central Blogs - A Smattering of Thoughts About Applying Site Reliability Engineering principles

Sheldon Hull from Sheldon Hull

What's This about
I figured I'd go ahead and take this article which I've gutted several times and share some thoughts, even if it's not an authority on the topic....

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Reading a Datafile
I have a dataset that is stored in the R data file format as counties.rds. I want to read this into a variable in R. What code should I use?
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)

The Linestring

I run this code in SSMS 18.4.

DECLARE @g geometry;  
SET @g = geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180, 100 100)', 0);  
SELECT @g

If I click the Spatial Results tab, what shape is graphed?

Answer: a triangle

Explanation: This Linestring is a triangle. The linestring is made up of coordinate points that define a series of lines connected to each other. In this case, we start at 100,100. There is a line to 20, 180, which is an angled line up and to the left. The next coordinate is 180, 180, which is a straight horizontal line from 20,180. Finally, the 180, 180 point is connected back to 100,100. Ref:

STGeomFromText - https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/stgeomfromtext-geometry-data-type?view=sql-server-ver15
Well-known text representation of geometry - https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry
Polygonal Chain - https://en.wikipedia.org/wiki/Polygonal_chain

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.

SQL Server 2017 - Administration

Enable RPC over SQL link - Hello, Is it possible to enable RPC over an SQL link? What i am attempting to do is this: select * from openquery("server1",'EXECUTE(''sp_serveroption ''''server2'''', ''''rpc out'''', ''''TRUE'''''') AT "server2";') I am a member of SYSADMINS group on the server, so it is not a permission issue I think. But i get the following error: "Server2 […]

SQL Server 2017 - Development

Issue with case statement - i have a table A where the data looks like ID Name Phone CODE 15674 AUSN +14412984775 HT 15674 MAN +13857747488 JK 67866 AUSN +74666378838 HT 98765 AUSN +72366747848 HT Another table called B. where we join A and B based on ID column Table B data ID 15674 67866 98765 i am trying to […]

generate insert statement with data from table - Hi all, My need is to generate insert statement with data from table (using existing data from that table). For example, I got table sometable1 and it has columns id, name. The result should look somewhat like this: Insert Into sometable1 (id, name) values (1, 'sometext1'); Insert Into sometable1 (id, name) values (2, 'sometext2'); How […]

SQL Server 2016 - Administration

Unable to connect to default SQL instance using a custom TCP port - Server A has a default SQL instance running on TCP port 12345. Server A has the SQL browser service running. Server B (on the same subnet) can connect to Server A using SSMS with/without specifying the port number. If Named Pipes protocol is disabled on Server A, Server B can no longer connect without specifying […]

Always on Availability Groups - Can i configure a 2 Node AG Availability group in the same data(local site) centre for High Availability .

DB mail issue - We received error when testing the DBmail The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 ( ). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: #5.1.0 Address rejected.). how to solve this issue .

erreor in insert value from Get-DbaDbBackupHistory - Good morning all , I need to trace the history of all the backup sql of all my servers for this I use the command dbatools Get-DbaDbBackupHistory attached my powershell script $sqlservers ="DESKTOP-95KKMMH","DESKTOP-95KKMMH\production" $serveur="DESKTOP-95KKMMH" $database="msdb" foreach($sqlserver in $sqlservers) { $history=Get-DbaDbBackupHistory -SqlInstance $sqlserver $history | Select-Object -Property SqlInstance , Database,Type,TotalSize,DeviceType,Start,Duration,End | Write-SqlTableData -DatabaseName msdb -TableName History_backup […]

Administration - SQL Server 2014

Stored Procedure - Usage - Hi, I am running query using sys.dm_exec_procedure_stats to get the stored procedure usage report and little confused that it shows latest time like today but Execution count is NULL so what i should understand? Is it Not using Stored Procedure at all since Last Sql server Restarted? How I should interpreting Last_Execution_Time and execution_count columns […]

fdhost.exe service does not relinquish memory - O/S - Windows NT 6.3 (9600) - Physical Machine 16GB of Physical Memory and 8 Processors SQL Server 2014 Standard Edition (64-bit) Version 12.0.6118.4 - Named Instance - Non Clustered The same service account is used on the following services: SQL Server, SQL Server Agent, SQL Full-text Filter Damon Launcher, SQL Server Integration Services 12.0 […]

SQL 2012 - General

Query Required this scenario - Hai, One company multiple employees working data entry operators.Employees working two different shifts. First shift time 10AM to 6PM .Second shift time 8PM to 6AM . my requirement , date wise use wise data entry count for both the shifts. i am facing problem second shits count. Because date changed in night 12'o clock. […]

SQL Server 2012 - T-SQL

In varchar column add numbers and show strings - Below is the sample data create table temp (userid int , rid varchar(10), value varchar(100)) insert into temp(userid,rid,value) values(1,'D01','3'), (1,'D01','4'), (2,'C01','hey'), (2,'C01','1') expected output: 1,'D01','7' 2,'C01','hey' 2,'C01','1' I tried below code and it is throwing error select distinct userid,rid, case when ISNUMERIC(value) = 1 THEN SUM(cast(value as int)) over (partition by userId,rid order by userid) […]

SQL Server 2019 - Development

Getting the biggest spending customers for last 7 days - Hi all, Can anyone help with the below SQL. I'm trying to get the top 5 customers for the last 7 days. I've tried to do it as shown below but it doesn't like my ORDER BY statement in the sub select statement. Any way I can get around this? Thanks in advance. Paul. select […]

Integration Services

Expression builder problem - All, Can anyone advise the correct way to put this expression together? I've tried a few combinations but each results in an error when I press OK. I don't think it's relevant but it's the "arguments" expression for an execute process task. Thanks

Exporting large amount of data out of AS400 - Good morning from my home office. Hope all is well. I have successfully configured the ODBC connection using iSeries something or other and created the data source in SSIS and all of that is working great, including some transformations. My job seems to fail when doing the load. It is going to a SQL server […]

Article Discussions by Author

Stairway to U-SQL Level 16: The Azure Data Lake Catalog - Comments posted to this topic are about the item Stairway to U-SQL Level 16: The Azure Data Lake Catalog

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.