Pattern Recognition via Principal Components Analysis (2019-10-25)

 SQLServerCentral Newsletter for October 25, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Pattern Recognition via Principal Components Analysis]( - [Inventory SQL Server Agent Jobs for all Instances with PowerShell]( - [DevOps falling short?]( - [From the SQL Server Central Blogs - Setting Permissions for a SQL Server backup folder–#SQLNewBlogger]( - [From the SQL Server Central Blogs - Databricks Delta Lake]( Question of the Day - [Ordering by Alias]( The Voice of the DBA  Taking Notes – Paper or Keyboard? Today we have a guest editorial from Andy Warren. This was originally published on 31 Jan, 2012, but is being re-run as Steve is at SQL in the City Summit today. I use a mix of techniques to take notes. If I’m leading or participating actively in a meeting I write notes. If I’m not an active participant and the noise won’t be an issue I’ll type them. I’m fussy about the notepad I use. For years I used a notebook just a bit bigger than half page sized, recently I’ve gone to the half paged sized format bound with discs, using the [ARC system]( from Staples until I decide if I like it. If I do, I’ll either upgrade to a [Levenger Circa]( notebook, or make my own cover. I use a twist style ballpoint pen that I made myself, black ink. Strangely for a geek I haven’t yet made the leap to OneNote or [Evernote](, most of my electronic notes wind up in Outlook. If that sounds illogical and mildly convoluted I’d have to agree! To add to that, mostly I capture tasks or items for follow up and who was at a meeting. My notes tend to be short, written as bullet points, with [chicken scratched]( callouts to things I add as we refine an item. Often my notes only last a day or two and then I discard, only on long projects do I keep them as a running journal, even though I rarely refer back to them (just often enough to merit keeping them!). I’m curious about how you take notes. Do you care about the pen and paper? Do you use Evernote, OneNote, or something else on your computer? What works for you? Andy Warren [Join the debate, and respond to today's editorial on the forums]( [Redgate Database Devops](   Featured Contents [Pattern Recognition via Principal Components Analysis]( nick.dale.burns from SQLServerCentral.com We'll look at using principal components analysis to help visualise your data and detect underlying structure or patterns. [Inventory SQL Server Agent Jobs for all Instances with PowerShell]( Additional Articles from MSSQLTips.com In this tip we look at a PowerShell script you can use to gather information about all SQL Server Agent jobs on all SQL Server instances in your environment for quick reporting and analysis. [DevOps falling short?]( Additional Articles from Other Organizations are looking for help with their DevOps implementation, including Redgate tools, knowing where they want to go with their production and deployment but not sure where to start. Clear Measure have designed a 30-point DevOps Inspection & Report to provide you with the feedback and guidance you need to take your software development and deployment to another level. Request your inspection now. From the SQL Server Central Blogs - [Setting Permissions for a SQL Server backup folder–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. While testing a script recently, I needed to... From the SQL Server Central Blogs - [Databricks Delta Lake]( James Serra from James Serra's Blog Introduced in April 2019, Databricks Delta Lake is, in short, a transactional storage layer that runs on top of cloud storage such as Azure Data Lake Storage (ADLS) Gen2...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Ordering by Alias I have a table with some order data that I need to build a query for. I gave a sample data set that looks like this: INSERT dbo.SalesHeader ( OrderKey, CustomerID, ShipTo, OrderDate, OrderTotal, StatusKey) VALUES ( 1, 1, 'Office', '2017-09-01', 500, 0 ), ( 2, 1, 'Home', '2016-07-05', 500, 0 ), ( 3, 2, 'Office', '2018-11-06', 500, 0 ), ( 4, 3, 'Office', '2019-04-07', 500, 0 ), ( 5, 3, 'Office', '2018-06-08', 500, 0 ), ( 6, 3, 'Office', '2015-02-09', 500, 0 ), ( 7, 4, 'Home', '2014-12-11', 500, 0 ) GO I query this table like this to test things: SELECT OrderKey, CustomerID, OrderDate = CONVERT(VARCHAR(10), sh.OrderDate, 101), sh.OrderTotal FROM dbo.SalesHeader AS sh ORDER BY OrderDate; Which OrderKey is returned first? 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 Real REAL What is the equivalent of the REAL datatype? Answer: float(24) Explanation: The REAL datatype is a Float(24). Ref: REAL and FLOAT - Thanks to Bert Wagner for inspiring this. He has a great video on floating point math and potential issues - [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 [Managing Mirroring failover in a WSFC cluster]( - Hi all, hope you can help. I'm not a DBA so have been googling as I go, so forgive my idiocy: I've got a SQL server standard instance with a number of databases. The setup is as follows: Servers 1 and 2 are in a SQL cluster with shared storage. Server 3 is a standalone […] [SQL profiler]( - Is SQL profile being deprecated and extended events should be used instead?  Also, has anyone heard that there is more of a concern around Statistics rather than fragmentation ? [Extended Events- how to log to an SQL table?]( - I want to enable an Extended events session. However, I don't want it to go to a file on the HDD, I 'd really like to send it all to an SQL table so I can do grouping, analysis, reporting, etc... How can I get that data into an SQL table for longer term storage […] SQL Server 2017 - Development [Most frequently used text in column]( - I would like to update a field (yearclass) for each row with the most common text in payclass for each grower, block, section and year combination. In the case where there are equal amounts of payclass, weight should be used to determine yearclass. Highest sum of weight wins. I have tried many queries of which […] [Insert into OPENDATASOURCE with Microsoft.ACE.OLEDB.16.0 provider - failed]( - Hi all, we export data into Excel file from SQL Server 2017 with a simple command (a part of a stored procedure): The command looks like: INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0', 'Excel8.0;HDR=YES;IMEX=1;Database=\\some_folder\excel1.xls')...Sheet1   SELECT * FROM table1 It works fine. But only if just one export is processing at the same time. If more exports are […] [Removing a double space in the middle of a name]( - Suppose I had a list of names and one of the names was Kathy Harrison. Notice the double space after the first name. In Excel there is a function called TRIM which could remove extraneous spaces. I am trying to find something like that in SQL. As far as I know, in SQL, TRIM is […] SQL Server 2016 - Administration [SQL Server security after deleting AD group]( - I currently have a scenario where an AD group that was defined as a SQL login with particular permissions got deleted from AD but not from SQL. So: domaina\groupb - no longer exists as an AD group but still exists as a SQL login It seems that members of the deleted group still have SQL […] [Very large t-log with backups]( - I have a database that's 125GB, but the t-log is 190GB. I do full backups each day and t-log backups every 15 minutes. I've verified the log backups have been successful for the past week. Generally when I see a large log like this it is because backups are failing. The log isn't growing out […] SQL Server 2016 - Development and T-SQL [Exporting very large Data to an XML file from SQL Server]( - I have a query that creates an xml file but I'm getting errors because the XML is too large. I've already set the Results To Grid Property XML Data to unlimited but I'm still getting the error. Administration - SQL Server 2014 [Always on - huge set of inserts and updates]( - Hi everyone, I need some advice please on how best to do something. We have a database which is 1TB in size, its part of a SQL 2014 Always On AG (synchronise sync). We have to do a very large data change, initial estimates are from testing it will take around 15 hours. Optimisation has […] Development - SQL Server 2014 [NOOB question with Case statement]( - hi here i am again with a noob question again: i'm trying to do this: case when columnA <> 0 then columnB = 1 else columnB end but it return error. i used case statment before but im the same column, is it possible to use in multiple column with the argument based on one […] SQL 2012 - General [service pack Question]( - Hi, I updated our SQL ser5ver from Sp2 to SP 4 today, and it says it was a success. However when I run say: I get Sp 2 So did I get the update or not if not what can I do? Thank you SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') SQL Server 2012 - T-SQL [join with GEOMETRY data type]( - I want to perform something like....  SELECT * FROM a JOIN b ON a.geometryField = b.geometryField OR SELECT * FROM a JOIN b ON a.geometryField. STEquals(b.geometryField ) OR SELECT geometryField FROM a WHERE geometryField IN (SELECT geometryField FROM b )  You get the gist. None of the above work. I know I need […] [Need help query for current row and previous row]( - Hye, I've table and data as following, CREATE TABLE [dbo].[WeightStory]( [Idx] [int] IDENTITY(1,1) NOT NULL, [WeightDte] [datetime] NULL, [WhatWeight] [decimal](18, 3) NULL, CONSTRAINT [PK_WeightStory] PRIMARY KEY CLUSTERED ( [Idx] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[WeightStory] […] Reporting Services [Failed Executions]( - Curious what everyone else is doing... I've always had a daily report showing me report subscriptions that failed. I've had a task to better monitor all report interactions (subscriptions, cache refresh and interactive) sitting in my queue, but I put it off because of other higher priority tasks. Right now I'm in the process of […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -