NoSqlOnSql (2020-11-02)

 SQLServerCentral Newsletter for November 2, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [NoSqlOnSql]( - [Managing Power BI Workspaces with PowerShell]( - [SQL IntelliSense and Autocomplete in SSMS]( - [From the SQL Server Central Blogs - Using Write-Debug]( - [From the SQL Server Central Blogs - T-SQL Tuesday Retrospective #002: A Puzzling Situation]( Question of the Day - [Reseed Locks]( The Voice of the DBA  Daily Coping Tip Start the week by writing down your top priorities and plans 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. The Separation of Tools I've got a question for you today. Is it better to have SSMS and ADS available as separate downloads? If you installed SQL Server prior to 2017 (the year, not the version), you had the option to add Management Studio (SSMS) to your installation. In fact, that was the only way to get SSMS back then. There was no separate tools download, which was a pain for many customers. Since then, there have been multiple releases of SSMS separately, starting with 16.x and moving through v17 and now v18. In fact, it's been roughly every quarter we've gotten new releases of the tooling. In addition, we've gotten Azure Data Studio (ADS), which is based on Visual Studio Code, but built with connections to the data platform as the primary goal. There are now separate downloads for [SSMS]( and [ADS]( though the latest SSMS download notes that starting with v18.7, ADS is automatically installed alongside the tool. That generated a feedback item, [which requests that this bundling be removed](. While I don't find this to be an issue, I do understand that this can be a problem for many organizations. I think that Microsoft moving to provide a more customized and flexible tooling experience makes sense. Give us the options to configure our systems as we desired. Let us add new features that are appropriate for us, or easily remove them. I would include the requirement that ADS be installed with SSMS, as noted above. That being said, the ability to add in various extensions might be of concern to many organizations. Flexibility includes the ability of both users and organizations to control the allowed and prohibited list of features. I will admit a fondness for SSMS that ADS has not replicated, but I do find myself using it more and more as new tooling items are available only in ADS and not in SSMS. Perhaps as I use notebooks more, I'll find myself more enamored with ADS. After all, I loved isql/Windows for years, even when other tools became the standard. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [NoSqlOnSql]( jsegarra from SQLServerCentral Introduction NoSqlOnSql is a strict syntactical superset of SQL; it adds NoSQL document-oriented capabilities to the SQL language. For this, it uses uses JSON-like documents, integrating them into the normal flow of SQL statements. NoSqlOnSql is based on augmenting a SQL database to support NoSQL features without any kernel changes. In this sense it is […] [Managing Power BI Workspaces with PowerShell]( Additional Articles from MSSQLTips.com In this article we look at how to manage Power BI workspaces using PowerShell cmdlets. [SQL IntelliSense and Autocomplete in SSMS]( Additional Articles from Redgate Phil Factor reviews the major features of SSMS IntelliSense and autocomplete and then explains how SQL Prompt fills in the gaps, and how to use to use the two in tandem to 'get the best of both worlds'. From the SQL Server Central Blogs - [Using Write-Debug]( Steve Jones - SSC Editor from The Voice of the DBA I wrote a post about PoSh output recently, noting that in general we ought to use Write-Output or Write-Verbose for messaging. In there, I mentioned Write-Debug as well as... From the SQL Server Central Blogs - [T-SQL Tuesday Retrospective #002: A Puzzling Situation]( Randolph West from Born SQL with Randolph West For the second T-SQL Tuesday ever — again, hosted by Adam Machanic — we were asked one of three options, and I elected to go with the first one:...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Reseed Locks I need to reseed a table with this code: DBCC CHECKIDENT('Status', RESEED, 100) I am concerned about blocking and locking in this database. What locks will this code take on my table? 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 Best Query I have a table full of various candies. CREATE TABLE Candy ( CandyName VARCHAR(100) , CountryName VARCHAR(50) ) GO INSERT dbo.Candy (CandyName, CountryName) VALUES ('Meiji Kinoko no Yama', 'Japan'), ('Shokolad Para Pitzputzim', 'Israel'), ('Guayabitas', 'Costa Rica'), ('Caramello koalas', 'Australia'), ('Marabou Mjolkchoklad', 'Sweden'), ('Ptasie Mleczko', 'Poland'), ('Coffee Crisp', 'Canada'), ('Saft Goldbären', 'Germany'), ('Nestle Damak', 'Turkey'), ('Indy Dedos', 'Mexico'), ('Fazer''s Marianne', 'Finland'), ('Pascal Pineapple Lumps', 'New Zealand'), ('Percy Pig gummies', 'United Kingdom'), ('Salmiakki', 'Denmark'), ('Dorina Riža', 'Croatia'), ('Strawberry Kiss Yupi', 'Indonesia'), ('Regina', 'Portugal'), ('Mozart Kugeln', 'Austria'), ('ION', 'Greece'), ('Kinder Bueno', 'Italy') GO I have another table for pranks. CREATE TABLE Prank (PrankName VARCHAR(200)) GO INSERT dbo.Prank (PrankName) VALUES ('Give out a burger that looks like a cupcake'), ('Gift wrap a rock'), ('Shoot silly string'), ('Play recording of dog attacking the door'), ('Drop balloons from upper window'), ('Give out resume paper and pens'), ('Drop slime into their hand'), ('Turn out all lights') GO I run this code to decide whether to give out a trick or treat to each person that comes to my door. How many people can I serve on Halloween night? SELECT CASE WHEN RAND(CHECKSUM(NEWID())) > .5 THEN c.CandyName ELSE p.PrankName END FROM dbo.Candy AS c CROSS JOIN dbo.Prank AS p; Answer: 160 Explanation: There are 20 rows of candy and 8 pranks. A cross join gives me 160 rows. Happy Halloween!!! Ref: FROM - [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 [Index question]( - We have some indexes we have changed over the last few years to address performance problems. Currently the system is in MySql. But we are planning to do the migration to SQL Server. Not any issues in MySQL. I am thinking of making the changes in SQL Server. Let me know what you think? [Process to failback after an AG failover using asynchronous mode]( - I'm testing the failover process in my AG that is using Asynchronous availability mode. In the scenario where I manually failover from Server A (primary) to B, what is the process to fail back to A? It seems like the only way is to rebuild the entire synchronization from B to A, failover to A, […] [SSIS 2017 stopped running, not status is Pending Execution]( - We have 3 sql agent jobs that are running SSIS packages. Until this past Saturday these have been running wonderfully. However, now they stop with a Pending Execution status. Even if I just run the SSIS alone it gives the same status. I have restarted the services. Restarted the server. Made sure SSIS and SQL […] SQL Server 2017 - Development [Stored Proc with OPENJSON: How to determine insert or update?]( - This is my 1st attempt at openjson & I'm trying to setup a stored proc that passes in multiple records and either inserts or updates the record in the table. I can setup the basic insert or update query using my openjson, however I don't know how to determine if the current record needs to […] [Question about Data Warehouse]( - Hello. I just rejoined SQL Server Central. I'm here because I'm a solo reports developer for a small business. I use SSRS, Tableau and QlikSense to develop reports and dashboards. I also write complex stored procedures to produce the datasets that I need. These Forums are the only places where I can ask questions or […] SQL Server 2016 - Administration [Login to SQL Server cross domain AD Group user]( - Our MS SQL Server is on another trusted domain (Trusted Domain T). We created a Universal Group within Domain A with users from different forest domains and added the AD group onto the SQL Server. When the user from Child Domain B tries to connect, they get the error: "Login failed for user". If I […] SQL Server 2016 - Development and T-SQL [table variable]( - deleted code resolved issue  [product version result]( - SELECT SERVERPROPERTY ('ProductVersion') Output: 13.0.4001.0 What does each of those numbers mean.I need to find the most recent hotfixes applied on a server. Is 4001 the most recent hotfix applied 13 signifies sql 2016 First '0' is it sp1 ?? And what the last '0' signify ? SELECT substring(SERVERPROPERTY ('ProductVersion'),6,4)..this results in an error . […] [Help Writing a Udf that get Number of "Years in a row"]( - Hello. I have a table (say orderlines) with orderrows,  There is a column that holds the "Year" of the orderline. I like to write a userdefine function that calculate how many years in a row that item has been sold from today and back.  For examle : If I in the table have: […] [Trying to get a arithmetic operation from an Variable]( - Hi, I am not sure if this can be done, but I seem to remember there is some format thing that can be done with quates. Thant would pull out the value so that I can use it in something like this: Declare @dd Varchar(2) = '<' SELECT *FROM SalesWhere [sale_id] + @dd + 2 […] Administration - SQL Server 2014 [Cannot remove growth restriction from log files]( - I have an instance where I have sysadmins. There a number of databases created by users and also msdb where I am unable to set log file growth to unlimited. It seems that when I change the setting all is OK but on checking back it has reverted to limited. I have altered the setting […] SQL 2012 - General ['xp_sqlagent_notify' error on job run]( - Recently, our corporate office forced us to downgrade some non-prod servers from enterprise edition to dev (don't ask, long story). We did so, but now our QA team is having some problems running jobs on the servers. More specifically, a job set up by a vendor to backup & restore a database from server A […] SQL Server 2019 - Development [Getting the top unique value by group]( - Alright, so I've been banging my head against the wall for a bit on this one, trying to avoid my developers going RBAR. We have two tables, and are trying to populate a third table by selecting the first available processing number for each ID. The two tables are joined like this: TABLE1.ID Table2.Processingnumber 1234 […] Integration Services [SSIS projects connection manager driver]( - Hi I am starting using the project deployment mode. I am using SQL server 2019 And I see when I add a connection in connection manager for a package, I cannot see the sql server native client driver any more. What connection manager type do you recommend for SQL server connections?  Thanks Business Intelligence [cannot find MSOLEDBSQL driver for SSIS 2019 connection manager]( - In the list of connection manager driver, I cannot find MSOLEDBSQL, There is a microsoft OLEDB for SQL server, but that is SQLOLEDB, as the online doc says both SQLOLEDB and SQLNCLI native client will be deprecated. For new development use MSOLEDBSQL, but I cannot that driver in the list when I create new connection […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -