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

 SQLServerCentral Newsletter for August 8, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Some T-SQL INSERTs DO Follow the Fill Factor! (SQL Oolie)]( - [Problems Caused by Use of the SQL_VARIANT Datatype]( - [PolyBase in SQL Server 2019 – The End of ETL?]( - [From the SQL Server Central Blogs - Rename Azure VM OS Disk]( - [From the SQL Server Central Blogs - Learning more Kubernetes]( Question of the Day - [Creating External Tables]( [Redgate SQL Change Automation]( 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]( [Redgate Data Masker](   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 - [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 […]   [RSS Feed]([Twitter]( 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. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -