Stairway to U-SQL Level 9: Views and C# Expressions (2020-03-04)

 SQLServerCentral Newsletter for March 4, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to U-SQL Level 9: Views and C# Expressions]( - [Using SQL Prompt Code Snippets for Repetitive Tasks]( - [Beginner Guide to In-Memory Optimized Tables in SQL Server]( - [Free eBook: Inside the SQL Server Query Optimizer]( - [From the SQL Server Central Blogs - Schema Filtering in SQL Prompt]( - [From the SQL Server Central Blogs - Information Measurement with SQL Server Part 4.8: The Intersection Family of Distances]( Question of the Day - [Finding the differences]( [Redgate SQL Provision]( The Voice of the DBA  The COVID-19 Impact I didn't think much of the COVID-19 virus when I first heard of it. It's another dangerous virus, but like SARS and MERS, it originated on the other side of the world from me and I didn't expect to see a large impact. These were epidemics and my heart goes out to those affected by them. I thought the current COVID-19 impact would be similar, but that's not correct.  [The MVP Summit was cancelled by Microsoft]( this week, as have quite a few large events around the world. [SQL Konferez]( is still taking place and SQL Bits is still scheduled to go on. I travel to the UK next week, and I'm not concerned, but I will be careful. I think avoiding large scale gatherings is a good idea, and certainly, I hope anyone feeling sick will avoid going out as much as possible, especially to places with a concentration of people. Many of us have events and commitments we hate to miss (exams, weddings, etc.), but if we're ill, this seems like the time to skip or reschedule something.  This epidemic, or pandemic, is going to affect lots of the world. Already travel is curtailed, some countries have banned large events, others are being cancelled. Whether necessary or not, these are the realities we are dealing with. I hope most employers adapt and adjust to this, limiting the chance of infection and exposure.  [Cisco is offering Webex to customers]( and others to help limit contact with those that might be infected, as a way to prevent the spread. At Redgate, we are planning for disruptions and having teams practice working remotely while still getting work done. They are trying to run their stand-ups and other meetings from home. This is easier for some groups than others, but it's a skill most teams need to develop. We already ask people to remain home when ill, but in this case, we're just running our DR test early in the event that we need to ask most employees to work remotely.  I'm not a doctor or scientist, and I don't quite know what to make of all the information being spread online through social media. There are [recommendations from the CDC](, and most of the advice I've seen is similar. Wash your hands, disinfect common surfaces, and cover your mouth if you have any symptoms.  I hope and pray no more people die, but I also know that likely isn't reality. Take care of yourself and your family, and be smart in how to live and work in public. I hope the world gets control of this soon and we start to see a decline in cases, and I hope most of us find ways to help that come true. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to U-SQL]( [Stairway to U-SQL Level 9: Views and C# Expressions]( Mike McQuillan from SQLServerCentral.com Learn how U-SQL views can hide complexity and use C# expressions, providing powerful ways to format and return your data. [Using SQL Prompt Code Snippets for Repetitive Tasks]( Additional Articles from Redgate Louis Davidson discovers the joy of using SQL Prompt code snippets to remove repetition from a variety of tasks, from inserting comment headers, to creating tables, to executing useful metadata queries. [Beginner Guide to In-Memory Optimized Tables in SQL Server]( Additional Articles from SimpleTalk Microsoft added the In-Memory OLTP (aka memory optimized tables) feature to SQL Server 2014. Operations to this type of table do not need locks, therefore eliminating blocking and deadlocking. In this Article, Monica Rathbun demonstrates how to get started using memory optimized tables. [Free eBook: Inside the SQL Server Query Optimizer]( Press Release from SQLServerCentral.com This free eBook from Redgate Software will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. From the SQL Server Central Blogs - [Schema Filtering in SQL Prompt]( Steve Jones - SSC Editor from The Voice of the DBA I love SQL Prompt, and I’m regularly impressed by the enhancements our teams continue to make in the product. One item that I found interesting recently was schema filtering.... From the SQL Server Central Blogs - [Information Measurement with SQL Server Part 4.8: The Intersection Family of Distances]( SQLServerSteve from Multidimensional Mayhem By Steve Bolton …………Each group of distance measures surveyed in this segment of the series was defined by affinities between the underlying equations. For example, the common denominator for...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Finding the differences I have two sets in R, as shown here: > a <- c(3,65,3,5,7,8,4,3) > b <- c(3,65,5,7,3,9,7,1) I want to find those values in a that are not in b. What should I do? 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) Getting BCP for Linux I have a Linux workstation that I am using for some SQL Server development. I installed Azure Data Studio, but that did not come with bcp. How can I get the bcp utility for Linux installed? Answer: Use your Linux package manager to install the mssql-tools package Explanation: Bcp is available on Linux, as a part of the mssql-tools package. Ref: - Install sqlcmd and bcp the SQL Server command-line tools on Linux - - Export \ import data using BCP - SQL Server on Linux - [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 [AG Listeners - multiple instances]( - Hi, I'm looking after a cluster that has 2 instances; one for 2016 and one for 2017. The AG listeners are both configured to use port 1433 and everything is working fine? Customer wants a separate instance of 2017 - how do listener ports relate to SQL ports? In the SQL port world named instances […] SQL Server 2017 - Development [Can't find my option to run my package in my toolbar]( - I need help running this package of my but my run button is no where to be found. SQL Server 2016 - Administration [AlwaysOn Failover Execution Cache]( - Hello I would like to know on the event of a SQL Server AlwaysOn failover, what is the effect of the query performance? How long it takes to generate the execution cache as the existing queries running in the primary node will not go to Stand by node. [Upgrading to SQL 2016]( - Hi All I am upgrading from SQL 2012 Std to SQL 2016 Std. A quick question I wanted to run past you guys..... Can I take the Master & MSDB mdf's and ldf's from the new 2016 instance and replace it with the mdf's and ldf's from the 2012 instance in attempt to bring across […] [Sudden growth in mdf file]( - Please assist I have faced a sharp growth in the size of my primary data file on SQL Server 2016.How can I identify the cause of the issue. SQL 2012 - General [How to do the following trace one a database server and on a table?]( - I have some troubles in doing following trace, thanks for kind help! 1. aside from opening SQL profile or extended event, How to monitor or trace who and when logining SQL server ? 2. How to know/trace who and when excecuting insert?update or delete or select statement on one table? SQL Server 2012 - T-SQL [input character string does not follow style 112, either change the input ch]( - Hi, I'm getting an obscure issue when when I run this SQL statement SELECT TOP 1 S_Date from tbl_name order by RowID I get this error message : Msg 9807, Level 16, State 0, Line 1 The input character string does not follow style 112, either change the input character string or use a different […] [View Dependencies]( - I have SQL Server 2012 and running the following query. THe results do not include all dependencies. I have a stored proc that calls a view, but that does notshwo up. How do I get full dependency list? SELECT ReferencingObjectType = o1.type, ReferencingObjectName = o1.type_desc, ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name, ReferencedObject = SCHEMA_NAME(o2.schema_id)+'.'+ed.referenced_entity_name, ReferencedObjectType = o2.type, ReferencedObjectName […] SQL Server 2008 Performance Tuning [Insert statement hung but select works normally]( - I have a question thrown to me by my developers, according to them, they notice that on a high load SQL server, their insert jobs is taking a long time to complete however all their select statement doesn't have any impact at all as the select completed very quickly. I told them, most select statement […] Reporting Services [SSRS 2012 Folder Settings not showing]( - Hello experts, We have an SSRS 2012 instance. Due to organization policy, I need to connect to the Report Manager using RDS / Work Resources. When I connect using the Work Resources Internet Explorer, I try to click 'Folder Settings' from the Home folder to add an AD account to the permissions. However, when I […] [SSRS 2019 DB account issues]( - Hi, I've been having some issues trying to get a brand new SSRS install deployed.  I have setup a brand new Windows 2019 server install along with SSRS 2019. I have the SSRS DB hosted a a separate Windows 2016/SQL2017 server. I have configured SSRS to use the Virtual Network service account and am […] Powershell [Finding specific file with dynamic date]( - I need to find a file with a specific date in it. The problem is I never know what the date will be except "today". The date is formatted as yyyyMMddhhmm. Since it was created earlier in the day by an automated process, I need to be able to find it by ignoring the hhmm […] Integration Services [SSIS Catalog and Github]( - Hello, the new environment I am working at now wants to use GitHub for version control, and SSIS for ETL, and wondering if SSIS integration catalog will work well with GitHub? or is it only going to work with SSIS by file? thanks in advance SQL Server 2005 General Discussion [Attachments EMail]( - I'm having trouble trying to get my query results loaded into my excel attachment can anyone help.  DECLARE @sub VARCHAR(100) DECLARE @qry VARCHAR(1000) DECLARE @msg VARCHAR(250) DECLARE @query NVARCHAR(1000) DECLARE @query_attachment_filename NVARCHAR(520) SELECT @sub = 'DC Weekly Transfer' SELECT @msg = 'This is Just a test.' SELECT @query_attachment_filename = 'dcweekly.csv' Declare @nOrgID int = […] [my query results are not in my excell attachment can anyone help]( - Hello, I have a query here that is sending a excell attachment to a email the only problem is my results are not being put into that attachment also how do I distinguish this when I create a Agent Job what parts do I put where in my agent job.  DECLARE @sub VARCHAR(100) DECLARE […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -