Iterating Over Calculated Tables using Power Query (2020-04-07)

 SQLServerCentral Newsletter for April 7, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Iterating Over Calculated Tables using Power Query]( - [The Staging Phase of Deployment]( - [Database DevOps is gaining popularity]( - [From the SQL Server Central Blogs - Azure Analysis Services – Processing Hot & Cold Data using Tabular model partitions]( - [From the SQL Server Central Blogs - Extracting data from Common Data Services (Microsoft Dynamics 365) using Azure Data Factory]( Question of the Day - [The ordering of the data]( The Voice of the DBA  Daily Coping Tip Rediscover your favourite music that really lifts your spirits. I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Encryption Libraries One of the ways we work to implement better security is through the use of encryption. I remember the early days of [PGP](, VPNs adding end to end encryption, [IPSec]( becoming available, and more. I was excited to see column level encryption come in SQL Server 2005, but it was a bit complex to implement and had issues. The evolution over time with TDE and Always Encrypted were welcome additions, but we can do better. Handling encryption in the database can provide some protection, but really, we'd like to see an end to end solution, that ensures data is protected on the wire as well as in the db. Microsoft does a lot of research, and [released a Simple Encrypted Arithmetic Library (SEAL) as open source code](. This is a C++ library that developers can use to protect their communications. Last year they also [released a .NET wrapper]( to make it easier for a wide variety of developers to use. There is a lot more to deal with when handling encrypted at scale. Indexing and search operations are difficult and complex, and can require lots of resources. I think that the [Always Encrypted in SQL Server 2019 with Secure Enclaves]( is a good evolution, but it's complex to set up, tooling is poor, and maybe more disconcerting, there are [potential vulnerabilities with secure enclaves]( on Intel chips. Certainly a combination of developers being able to use something like SEAL and a linkage with a secure enclave on the server for set-based operations might be something we can look forward to in the future. I hope so, as security continues to be a challenge for many of us. I'd also like to see more software that is built to ensure users can't access huge amounts of data, and perhaps easier ways to control the export of large amounts of data. We do need this capability, but we also want very few people, or no people and only a process, with rights to do so. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Iterating Over Calculated Tables using Power Query]( Gouri Shankar from SQLServerCentral Problem Many of us are already aware of the loopin... [The Staging Phase of Deployment]( Additional Articles from SQLServerCentral Despite some humorous examples of deployments gone wrong, failures are not funny. William Brewer explains why staging is so important and how it can help avoid the types of disasters he recalls in this article. [Database DevOps is gaining popularity]( Additional Articles from SQLServerCentral Fourty-six percent of the State of Database DevOps survey respondents advised they are performing some form of deployment automation. This figure has risen year on year as the conversation of DevOps in Database development continues, and the benefits become more apparent. If you are interested in more insight, you can download a copy of the 2020 State of Database DevOps report here. Download now From the SQL Server Central Blogs - [Azure Analysis Services – Processing Hot & Cold Data using Tabular model partitions]( Joyful Craftsmen from Joyful Craftsmen Blog Introduction Recently have been working on a “Proof of Concept” task where I wanted to test the capabilities of Azure Analysis Services Tabular model when loading data from different... From the SQL Server Central Blogs - [Extracting data from Common Data Services (Microsoft Dynamics 365) using Azure Data Factory]( Rayis Imayev from Data Adventures (2020-Mar-30) Little did I know that I can use Azure Data Factory (ADF) to extract data from the Common Data Services (CDS). This data platform enables you to securely store...   Question of the Day Today's question (by Steve Jones - SSC Editor):  The ordering of the data I have this code: DECLARE @Test TABLE (testString VARCHAR(1) COLLATE SQL_Latin1_General_CP1_CS_AS); INSERT INTO @Test (testString) VALUES ('a') , ('b') , ('c') , ('A') , ('B') , ('C'); Then I run this in the same batch. SELECT testString FROM @Test WHERE testString LIKE '[a-B]'; What is returned (each letter in the answer would be on a separate row)? 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) Linux Support Which of these Linux platforms is not supported for SQL Server 2019? Answer: CENTOS Explanation: CentOS is the distribution based on Red Hat. This not supported, however. Ref: - Installation Guidance for Linux - - CentOS - [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 [SSIS Deployment Issue]( - Hello: Problem: Cannot deploy SSIS packages to AZURE VM/SQL Server Instance (or execute them via SSDT/VS) from a local laptop using a SQL Login. FYI: Project Deployment Model is used. Background:  We moved our development SQL Server instance to an AZURE VM, and would like to avoid giving every developer RDP permissions access to the server. […] [query optimization]( -   SQL Server 2017 - Development [count by week by start/end date]( - I'm trying to show a count by week but I am unsure of how to find dates between years? How do I show the Year, Week, and count for the example below? This ID_NUM should show 1 for every week for the entire year. I've shown what I've tried so far below. Thanks.  I'd […] [update by quantity]( - I'm writing an update statement that uses a quantity column to determine how many rows get updated. I can accomplish this using RBAR, but performance is unusable and I can't figure out how to do this as a set operation. I have a sales table that contains a row for each individual item. In the […] SQL Server 2016 - Administration [Convert SQL HA (2 node) to Standalone]( - Hi, We have some of the SQL Server AlwaysOn environment (NonPROD) where we do not require alwayson any longer. Instead a standalone system will be sufficient. Without provisioning a new environment (standalone), how we can use the existing HA to a standalone and remove one of the other nodes? Does Microsoft has any clearly defined […] [Shrinking _log file for live database]( - I have a live database in SQL2016 (in Production, in use) that I need to shrink its l_log file which has grown to over 250GB. Can I use this type of command block safely?: ALTER DATABASE ExampleDB SET RECOVERY SIMPLE DBCC SHRINKFILE('ExampleDB_log', 0, TRUNCATEONLY) ALTER DATABASE ExampleDB SET RECOVERY FULL [Logical file name mismatch]( - Hi All I've picked up that the logical name for my transaction log is different between sys.database_files and sys.master_files. I know how to fix this up. My question is, how did it get that way? How can I recreate this issue? SQL Server 2016   Thanks   [SSRS report font settings]( - Dear team, Hope all are safe and doing well, We have a Windows Server 2016 STD server with SSRS installed. Question is where can I download Arial Narrow & Myriad Pro To use in SSRS reports? Is this available on Windows server 2016? User is having some issues with fonts while exporting reports to PDF on our […] SQL Server 2016 - Development and T-SQL [Stored Proc slow after switching to Replication?]( - Hi First, let me apologize in advance for my ignorance on this... We are in the process of switching our reporting DB from backup and restore to transactional replication. I am testing stored procedures and noticing some SP's run much slower in transactional replication. I've done some googling and have tried the three below, but […] Development - SQL Server 2014 [Trouble with Finding Dependencies between Tables using Keys]( - In the past I could rely on the View Dependencies feature in SQL Server Mgt Studio to show me all objects that an Object depends on or that the object is dependent on. I don't know when this changed but it no longer works reliably. The View Dependencies works for some parent/Child tables but not […] [How to find Last Record with multiple results]( - In Access, there is a LAST function, which simply allows you to do a group by, then select last, and you get the last record for each of your results. This does not exist in SQL. I have a query I am running that will show me a full list of all Part ID's in […] SQL Server 2012 - T-SQL [Finding a range of date within a range of date]( - I have a project that requires to display a list of patients within a specific period of dates but having problems building my statement the following is the sample data set. CaseNo    DateAdmitted  DateDischarge 1                12/01/2019       12/02/2019 2               12/01/2019        12/03/2019 3               12/02/2019       NULL                  - not yet discharge Scenario 1: getting patients from 12/02/2019 to 12/03/2019 […] SQL Server 2019 - Administration [Unexcpted erorrs restoring large tables]( - Hi Please excuse me if this is a basic question, I have been given a .sql file which has a script to create and insert data into a table. The script around 100,000 records in it. Most of the content is in the form 'Insert into table ....' 'insert into table ...' GO Insert into […] SQL Server 2019 - Development [Arithmetic overflow error converting expression to data type nvarchar.]( - Is there a way to find the field this error is being generated by? I am trying to bring data from an AS400 to SQL. I am attempting to convert 3 fields to date formats. I have a staging table that converts them to nvarchar(10) then when inserting into the final table I do IDATE(field) […] SQL Server Newbies [Force job to fail based on condition of IF]( - I am using SQL Server 2005 and I need to force a job to fail based on a condition of an IF statement. This is an overview what my code is doing: IF (SELECT '1') = 1 BEGIN PRINT 'Yes' END ELSE BEGIN PRINT 'No' END The above isn't my exact code but this gives […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -