Views in SQL Server 2019 with SSMS (2021-12-27)

SQLServerCentral Newsletter for December 27, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Views in SQL Server 2019 with SSMS]( - [Learn about SQL Joins on Multiple Columns]( - [Flyway with MariaDB for Those of a Nervous Disposition]( - [From the SQL Server Central Blogs - “Could not find the modules: 'Az.Accounts' with Version: ''” error message and a story to remember]( - [From the SQL Server Central Blogs - Tech Lunch News]( Question of the Day - [Amazon Redshift]( The Voice of the DBA  Daily Coping Tip Appreciate kindness and thank people who do things for you 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. Looking Back at 2021 Today is my last day of work for 2021. I'm off to a family vacation for the rest of the week to enjoy time together and celebrate the New Year. I wanted to take a look back at the year before I leave and give you a few thoughts on various data-related topics. The big news for those Microsoft data platform people is that SQL Server 2022 is coming. No idea when, and without public CTPs, I think it might be a 2022 H2 release, but Microsoft committed to the naming. There have been a number of talks, at Ignite, the PASS Data Community Summit, and the SQL Server and Azure SQL Conference. I had the chance to talk with Bob Ward and Pedro Lopes, and they are excited and proud of the upcoming release. I don't think it's a massive change, but there are a few nice things, and I look forward to the continued query processor improvements. It's also nice to have a new version coming out after a few years. I'm guessing there are plenty of people working with 2016 or prior versions that might look to upgrade in the next couple of years. The other area that occupies a bunch of my time is the event scene. I am have spent a lot of my time in the last decade going to events and helping promote them. SQL Saturday has been an incredible boon to bringing together data professionals at local events, and I look forward to that continuing. 2020 and 2021 were hard years, with relatively few events, either virtual or physical. Concerns over people getting together have kept many organizers and venues from running anything. Many of us are vaccinated and there are areas trying to return to some sense of normalcy. I don't know where the future will go, but I was lucky enough to go to a few events this year. Dataminds Connect in Belgium, SQL Saturday Orlando 2021, and the SQL Server and Azure SQL Conference were highlights for me. Different rules and restrictions at each, and compliance with those was easy for me. I loved getting to see people. There were few virtual events, and many of the events that did take place saw big declines from the attendance and engagement in 2020. While some user groups and smaller events have thrived, many have not. I hope that we continue to see some virtual events in the future, but I am looking forward to more physical interactions in 2022. One interesting data item from 2021 was the change in how healthcare has gone virtual. I've seen more visits taking place, and more devices coming that help smaller facilities run diagnostics for remote doctors. I've seen some home devices as well, and I love the growth in accessibility, and I worry about the security of data and privacy. The push to do reduce contact with others has led to some innovation for sure. Many of us have worked with data for a long time, and we know that companies have usually embraced the collection and analysis of data related to their businesses. Knowing that, I've been surprised how often I find companies struggling with supply chain issues and not knowing the status of their orders. Perhaps they do and prefer not to let consumers know, but in the past I've often seen estimates of when something will arrive. Even Amazon has had a lot of "out of stock and no idea" posts on products. I find it interesting that companies either don't have the data or won't disclose it. Either way, a lack of transparency wasn't the data change I expected in 2021. I haven't dabbled in Bitcoin of cryptocurrency at all, and I'm not sure what to think of Blockchain. It's an interesting mathematical idea, but not sure I like it as a practical item. It's coming in SQL Server 2022, and it's also becoming more mainstream. The thing that made me think we should understand this better was the [renaming of the Staples Center to the Crypto.com arena](. No matter what you think of the tech, spending the money on advertising means there is plenty of revenue and profit in this area. From a data perspective, worth knowing something about how this works. For most of 2021, it felt like an extended 2020. Vaccination opened some things up, but the world didn't return to normal. Many companies continued to avoid forcing people back into offices, but not all. I have some friends back in the office every day and even more that are required to go every week. I don't know where 2022 will bring us, but I hope it's a flexible blend of in-person and remote work. It might not work for everyone, but for many of us working with data, we can certainly get a lot done remotely. We proved that in 2020 and again in 2021. This was a good year for me, and I hope you can say the same thing. I'm off for the rest of the year, but I wish you a Happy New Year and look forward to seeing more of you in 2022. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Views in SQL Server 2019 with SSMS]( mamta_d from SQLServerCentral This article explores how to create and use views in SQL Server 2019 using SSMS. [External Article]( [Learn about SQL Joins on Multiple Columns]( Additional Articles from MSSQLTips.com Learn about joining SQL Server tables together for a query when there is a need to join on multiple columns. [External Article]( [Flyway with MariaDB for Those of a Nervous Disposition]( Additional Articles from Redgate This article will get you up and running quickly with Flyway migrations on MariaDB or MySQL databases, from PowerShell. [Blog Post]( From the SQL Server Central Blogs - [“Could not find the modules: 'Az.Accounts' with Version: ''” error message and a story to remember]( Rayis Imayev from Data Adventures (2021-Dec-20) Part of the continuous integration and delivery for Azure Data Factory involves pre-deployment and post-deployment steps. Their main role is to stop triggers before deployment and restart them afterward,... [Blog Post]( From the SQL Server Central Blogs - [Tech Lunch News]( Joyful Craftsmen from Joyful Craftsmen Blog 8. prosince jsme se opet sešli u obeda nad novinkami ze sveta dat. Je super, že se stále více úcastní i lidé mimo JC – jsme rádi, že se...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Amazon Redshift What type of database is Amazon Redshift? 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 Gift Mask Secret Santa has a table that contains a list of gifts purchased for individuals. There is a column, called giftprice (numeric 8,2), in here, where the value of the gift is stored. Secret Santa doesn't want anyone to know the prices, and applies this code to the column: ALTER TABLE dbo.SecretSantaGifts ALTER COLUMN giftprice ADD MASKED WITH (FUNCTION='DEFAULT()'); None of the recipients of gifts have the UNMASK permission. If one of them queries a row that contains a value of 99.99, what is returned? Answer: 0.00 Explanation: For numeric columns, the default function returns a 0 of that data type. In this case, 0.00. Ref: Dynamic Data Masking - [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 [failure]( - There is a error in the application logs with table name not allowing nulls into column. However, the sql server error log does not show which proc is causing the error. Looked at monitoring tool did not find anything associated. Is there any other place to look into it. Do yo think the better option […] SQL Server 2017 - Development [Customized Schedule Job through TSQL]( - Hi All, After a long time posting my query. It would be great help if anyone can assist on this. I would to like to create one schedule for sql job which should run after every 1 week from the active start date.  For Example : If active start date of the job is […] Development - SQL Server 2014 [Count based on condition]( - Hi I have below query . In T1.Sample1,T1.Sample 'Y' & 'N' is stored . I want to display Count of T1.Sample1 which have & and similarly of T1.Sample2 SELECT T1.U_VendCode,T1.U_VendName as "Vendor Name",T1.Sample1,T1.Sample2 FROM [tbl1] T0 inner join [tbl2] T1 on T0.Docentry = T1.DocEntry where (T0.U_DATE BETWEEN '2021-03-01' and '2021-03-31' ) group by T1.U_VendCode,T1.U_VendName,,T1.Sampl1,T1.Sample2 Code […] [ Error - Each GROUP BY expression must contain at least one column]( - Hi Error - Each GROUP BY expression must contain at least one column that is not an outer reference. SELECT T1.U_VendCode,T1.U_CabNo,T1.U_VendName as "Vendor Name",Count(*), (Select Count(*) from [tbl2] where T1.U_APDE <> 'N' group by T1.U_VendCode,T1.U_CabNo,T1.U_VendName,T1.U_Custcode,T1.U_Custname,T1.U_APDE,T1.U_ARDE,T1.U_ItemCode), T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",T1.U_ItemCode FROM [tbl1] T0 inner join [tbl2] T1 on T0.Docentry = T1.DocEntry where (T1.U_ItemCode is […] [Inner select count(*) is not returning any data.]( - Hi Inner select count(*) is not returning any data. SELECTT1.U_VendCode,T1.U_CabNo,T1.U_VendName as "Vendor Name",Count(*),(Select Count(*) from [tbl1] where U_APDE = 'Y'),T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",T1.U_ItemCodeFROM [tbl0] T0inner join [tbl1] T1 on T0.Docentry = T1.DocEntrywhere NOT(T1.U_APDE = 'N' AND T1.U_ARDE = 'N') and (T1.U_ItemCode is not null) and (T0.U_DATE BETWEEN '2021-03-01' and '2021-03-31' )and T1.U_CustCode […] SQL 2012 - General [Urgent help needed. MS SQL Server 2012]( - Hi, I've just got a call (6pm on a Friday!) from a customer who has had his line-of-business application provider sell him a solution which required SQL Server 2012 Standard, yet the supplier (joyfully) installed the evaluation version of SQL Server 2012 Enterprise. This has now ticked over and expired. My problem is that (a) […] SQL Server 2012 - T-SQL [how to make comm seprated list with control break. in sql 2012]( - hi, col "R" has report no like "R1" "R2" ETC COL "D" AS Direction like "D" "D1". direction can be more than one in one report. --uniqueness of record is col R + D result needed is all directions of the table IN one SCALAR column with all comma seprated and ";" seprated like FOLLOWING. […] SQL Server 2019 - Administration [Getting error Failed to open connection dialog when trying to open SSMS 18]( - I've got SSMS 18.3 installed on my laptop. When I open it, I'm getting an odd error I've never seen before: Failed to open connection dialog Object reference not set to an instance of an object. (Microsoft.SqlServer.Management.SqlStudio) I cannot get past this, and I don't know how to fix it. How do I fix this […] SQL Server 2019 - Development [SQL Agent Job Use Different Environment Variable]( - Hi, Using SQL2019, we have multiple projects and packages in our SSISDB. They all reference a single shared Environment which holds our Connection strings. I have a package using a project level Connection. This package by default uses Connection1, so configuring the sql agent job is simple and this job runs weekly. I want to […] [SQL Filestream Retrieve File with VB or SQL]( - Hello, I want to use SQL Filestream to store files. I am a fairly novice developer and this is new. I have set up my database to store files and have successfully imported files with SQL and using Excel VB to execute the SQL. I would like to use Excel VB to export the file […] [copy rows in the same table based on conditions]( - I have the table as in data below , now I would like to copy all of the rows in which TEXT1 is 'erledigt' and the DATE2 is within the year 2021 , I would like to copy the rows within the same table and make sure to copy the same number of rows as […] [Passing a column into CONTAINSTABLE/CONTAINS]( - I am running a search, passing a column with keywords into inner joint. SELECT         SentimentCategoriesAncors._pk AncorID, Clip._pk ClipID FROM         SentimentCategoriesAncors INNER JOIN Clip ON Clip.title LIKE '%' + SentimentCategoriesAncors.incude + '%' I need get some speed and put some fuzzy logic in play. So, I indexed Clip.title into Full-Text Search and trying to do something […] [3 calculations on different levels]( - Hello everyone, I am asking something that I am not even sure is even possible therefore I will appreciate any suggestion or advice. I have a SQL code that works just fine. But I have an issue My calculations for Inventory On Hand and On Order are done using LOC column (it is basically a […] General Cloud Computing Questions [Noob questions about Cloud computing - What made it so prevalent?]( - We're seeing everything going to the cloud these days, from data storage to phone systems. I don't fully understand what technologies have made cloud computing so prevalent and easier to implement. Is it due to Internet bandwidth? Better software? Integration Services [Azure Data Studio for managing SSIS packages?]( - Hello all, I was wondering if anyone knows of a way to manage SSIS packages using Azure Data Studio? For example, I'd like the functionality of deploying packages: ...and checking on the results from packages that have run in the past: I've Googled and searched this forum but can't find any information on it. This […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -