SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Creating External Tables

The Voice of the DBA

Timing is Everything

We know that the online world is dangerous, with the number and variety of threats constantly increasing. For years, phishing with email has been a favorite tactic of hackers. Most of these emails are poorly written, and IT professionals often spot the fake ones, but there are times when the creativity of nefarious individuals is quite surprising.

There was an attack on utility companies recently, which targeted engineers and purported to have some results for professional examinations. Since there is likely always someone taking an engineering exam as a professional at a utility company, this attack has a chance of succeeding. A Word doc included had a number of macros that installed a trojan for the employee. Hopefully few people allowed macro execution, but I'm sure someone was distracted, busy, and perhaps stressed about their exam results and didn't think. Or clicked too fast.

What is scary here is that the attack is suspected to be the work of a nation-state, which could mean substantial resources went into the attack. There are certainly potential issues if someone gets access to a privileged workstation, which is always a concern. This is why few people should have privileged access, and a privileged account should never be used for email software. A low-access account is the best way to work through emails and attachments.

Many of us have privileged accounts, but imagine that someone gets a trojan on our laptop. When we use sudo or runas, we might find our credentials compromised. Think it can't happen? What if all your IT staff got a note about an MS examination result? Or what if they got a free offer to take a certification test? I'm sure someone would click on it, and it only takes one.

Security is hard, with education and caution being the most important tools at our disposal. Spread the word about stories like this and ensure all your staff, especially junior staff, is especially cautious about how and where they open any potential email attachments, click links, or even allow pictures. Security remains a challenge for all our organizations and we need to continually work at maintaining a vigilant environment.

Steve Jones - SSC Editor

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

Featured Contents

Some T-SQL INSERTs DO Follow the Fill Factor! (SQL Oolie)

Jeff Moden from SQLServerCentral

With origins from the world of “Submarine ‘Dolphin’ Qualification” questions, an “Oolie” is a difficult question to answer, or the knowledge or fact needed to answer such a question, that may or may not pertain to one's duties but tests one's knowledge of a system or process to the limit. Introduction Contrary to what many […]

Problems Caused by Use of the SQL_VARIANT Datatype

Additional Articles from Redgate

Phil Factor illustrates the 'quirks' of the SQL_VARIANT datatype and why it's best to investigate when SQL Prompt alerts you to its use. It is only safe to store data as a SQL_VARIANT, if you explicitly convert it to its true type before you use it.

PolyBase in SQL Server 2019 – The End of ETL?

Additional Articles from SimpleTalk

PolyBase has been around since 2016, but Microsoft is enhancing it in 2019 with the ability to connect to more data sources. You can now use PolyBase to connect to other SQL Server instances, and it’s easy to set up with a wizard. In this article Benjamin Weissman explains what’s new in PolyBase and walks you through setting it up and connecting to an external table.

From the SQL Server Central Blogs - Rename Azure VM OS Disk

Bradley Schacht from Bradley Schacht

In a recent post about creating Azure VMs using PowerShell, I noted a common issue that I run into where the name of the OS disk does not conform...

From the SQL Server Central Blogs - Learning more Kubernetes

Steve Jones - SSC Editor from The Voice of the DBA

I’ve been slowly working my way through the 50 days of Kubernetes (K8s). As you might have guessed if you remember my first post, this has been more than...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Creating External Tables
When creating an external table in SQL Server 2017, I can specify a DATA_SOURCE parameter. What can I use as the value for this parameter?
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)

Cleaning the Data frame

I am tracking some of my flights in this csv file:

Flight,Origin,Destination,FlightTime
UA9,IAH,LHR,"2019-04-30 17:34"
UA58,LHR,ORD,"2019-05-01 09:12"
UA99,LAX,LHR,!
UA25,LHR,IAH, "2019-06-22 07:23"

I want to read this into a dataframe in R, but for some of the rows, a missing time is denoted with an exclamation mark. How do I read in this value as a "NA"?

Answer: x = read.csv2("Flights.csv",header=TRUE,sep=",",na.strings = "!")

Explanation: There is a header in this file, so the header parameter needs to be set to TRUE. The na.strings parameter will denote which items are to be read as NA. The results with this are:

> x
  Flight Origin Destination        FlightTime
1    UA9    IAH         LHR  2019-04-30 17:34
2   UA58    LHR         ORD  2019-05-01 09:12
3   UA99    LAX         LHR              
4   UA25    LHR         IAH  2019-06-22 07:23

Ref: Read.csv2 - https://stat.ethz.ch/R-manual/R-devel/library/utils/html/read.table.html

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

veeam backup permission issue - Hi, From Veeam we are taking SQL Server backup. we are using ABC\abcadmin credentias to take the backup. We got a below warning. Warning: [TDB]Unable to update SQL backupset for instance : Code = 0x80040e09 Code meaning = IDispatch error #3081 Source = Microsoft OLE DB Provider for SQL Server Description = The UPDATE permission was denied […]

SQL Server 2017 - Development

Understanding schemas - I queried sys.tables, sys.all_views, sys.all_objects and . The following appear to be views: 1. tables 2. all_views 3. all_objects In all 3 of these cases what is the ‘sys’ part? Isn’t that a schema? I did a SELECT * on all_objects so I can view all objects. I have a few questions about the results of […]

Problems with creating a foreign key column - Here is the code I am using: ALTER TABLE Costpoint.ProjectCodes ADD CONSTRAINT FK_ProjectCodes_Employee FOREIGN KEY (projectMgrID) REFERENCES Employee(employeeID); I am getting the following error: Msg 547, Level 16, State 0, Line 33 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_ProjectCodes_Employee". The conflict occurred in database "Amargosa Hotel", table "dbo.Employee", column 'employeeID'. I […]

SQL Server 2016 - Administration

Indexes are not replicated - I reconfigured the transactional replication on my database and i found indexes are not replicated to the subscriber in all the tables how to create the indexes on all the tables in subscriber with out effecting the replication

SSISDB is in recovery - Hello, so my SQL agent job SSIS Server Maintenance Job has slowly been getting slower and slower over the past week, then today it ran over 8 plus hours when suddenly the SSISDB transaction log filled up max (not the drive Thank GOD) but now the SQL agent job failed and the SSISDB is "In […]

Script to compare tables between two databases - Hi, I have same set of tables in Database A and Database B. If there any changes occurs to a table in Database A, then I have to make same changes in database B. Is there way to query and find the differences between tables in Database A & Database B?

SQL Server 2016 - Development and T-SQL

SSIS and flat file column headings with special characters in them - Thanks in advance for any help! I have a csv file that has a header row in which the header column titles have some special characters in them. For example, the column for a diagnosis code (we are a healthcare organization) might have the heading "Diagnosis: Primary". For reasons I won't get into here, I […]

Group by and a list. - Suppose I want to do a count, grouped by an article and some columns I do not want to group on but I want some examples of the content. Is there a 'simple' solution for this. (Not using FOR XML) Example result. Counted Article Colors […]

Macro in SSIS, pops up for user name password - Hi Guys, I am running a Macro file in SSIS VB script task. My macro pulls data from a https url and url requires user name and password. My package works fine in visual studio project solution but it pops up to enter user name and password. I need to schedule this package to run […]

Administration - SQL Server 2014

Suspend-ClusterNode and Move-ClusterGroup - Hello everyone , In a SQL Server Fci cluster Does this Powershell command Permit to transfer resources to the second node of my cluster Suspend-ClusterNode xxxxx -Drain -Whatif or I must run the command too Move-ClusterGroup “GROUPNAME” –Node “xxxxx ” thanks

Always on Quorum and Listener - I have three cluster nodes configured for Always on Availability group, two nodes are in same location(node1 primary and node2 secondary with synchronous mode) and the third node(node 3 another secondary replica in DR with Asynchronous) would be in DR location. Also, I have configured for automatic failover and trying to understand and ensure that […]

SQL 2012 - General

How to Query an Excel file with mutiple cells for one single ID - Hi Folks, My first question here. This may be lengthy, sorry in advance:-) I am looking to read an excel file into a SQL Server table. The issue I am having is the data entry in the excel file was done in such a way that a person can have multiple pieces of software listed […]

BEGIN TRANSACTION, ROLLBACK TRANSACTION, COMMIT TRANSACTION - I do a lot of ad hoc update, insert, delete statements and was wondering if I should encapsulate using BEGIN... ROLLBACK... COMMIT to give me an out if something goes wrong like below: BEGIN TRANSACTION UPDATE B SET expired = -1 FROM GCDF_DB..BillingThirdNotice B INNER JOIN GCDF_DB..Certs C ON B.PeopleID = C.PeopleID WHERE C.certificationExpireDate < […]

SQL Server 2008 - General

SQL Server Agent Job - Decision Extract Question and Purpose - Hello Support Forum, I've inherited a 2008 R2 server and after scrubbing through the maintenance Plan and Job setup, I came across a scheduled event with the purpose of conducting a "Decision Extract" for an entire Database (that you specify). I've looked at the Query and can follow it no problem but I'm unaware of […]

Reporting Services

Creating bell curve in SSRS - Hi, I have a report that shows how long we have spent on certain matters (I work for a solicitors). I round the amount of time spent on a matter to the nearest hour so we could have 5 matters where we spent 1 hour, 3 matters where we spent 2 hours, 1 matter […]

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.